Passing a parameter in a Report's Open Event to a parameter query (Access 2007)
- by JPM
Hi there,
I would like to know if there is a way to set the parameters in an Access 2007 query using VBA. I am new to using VBA in Access, and I have been tasked with adding a little piece of functionality to an existing app.
The issue I am having is that the same report can be called in two different places in the application. The first being on a command button on a data entry form, the other from a switchboard button. The report itself is based on a parameter query that has requires the user to enter a Supplier ID.
The user would like to not have to enter the Supplier ID on the data entry form (since the form displays the Supplier ID already), but from the switchboard, they would like to be prompted to enter a Supplier ID.
Where I am stuck is how to call the report's query (in the report's open event) and pass the SupplierID from the form as the parameter. I have been trying for a while, and I can't get anything to work correctly. Here is my code so far, but I am obviously stumped.
Private Sub Report_Open(Cancel As Integer)
Dim intSupplierCode As Integer
'Check to see if the data entry form is open
If CurrentProject.AllForms("frmExample").IsLoaded = True Then
'Retrieve the SupplierID from the data entry form
intSupplierCode = Forms![frmExample]![SupplierID]
'Call the parameter query passing the SupplierID????
DoCmd.OpenQuery "qryParams"
Else
'Execute the parameter query as normal
DoCmd.OpenQuery "qryParams"?????
End If
End Sub
I've tried Me.SupplierID = intSupplierCode, and although it compiles, it bombs when I run it. And here is my SQL code for the parameter query:
PARAMETERS [Enter Supplier] Long;
SELECT Suppliers.SupplierID, Suppliers.CompanyName, Suppliers.ContactName, Suppliers.ContactTitle
FROM Suppliers
WHERE (((Suppliers.SupplierID)=[Enter Supplier]));
I know there are ways around this problem (and probably an easy way as well) but like I said, my lack of experience using Access and VBA makes things difficult. If any of you could help, that would be great!
