Passing a parameter in a Report's Open Event to a parameter query (Access 2007)

Question

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!

Answer 1

As I outlined in a recent post, I tend never to hardwire any parameters or form control references into the recordsources of reports or forms. Instead, I set them at runtime. The simplest way is by passing the WhereCondition property in the DoCmd.OpenForm/DoCmd.OpenReport:

DoCmd.OpenReport "MyReport", , , "[SupplierID]=" & Me!SupplierID

That assumes you're running it from a form that has the relevant SupplierID already present in its recordsource (i.e., you're on a record with that SupplierID).

More complicated is to use the OnOpen event of the report to set the reports's recordsource. That's what I outlined in the cited post above. But that example hardwires the choice to a selection form, whereas you might want to instead offer different sets of choices depending on context. There are two ways to handle that:

1. if A2003 and later, pass an OpenArg (the last parameter of the DoCmd.OpenReport) to tell the OnOpen event what to do to collect the information on what to filter to.

2. use an outside structure like a standalone class module to store criteria that the OnOpen event will read and act upon accordingly.

I suspect that the WhereCondition in the DoCmd.OpenReport is your easiest solution, but if you want details on the other two, just ask.

Answer 2

The suggestion being made here is to 100% REMOVE the parameter from the query. This not only solves your problem, but then means you can use the query for code, other forms and not have your whole design fall apart because one stupid form is not open (hence the VERY reason for your question).

So, remove the parameters from the query. This also means that your report will now not need some form that already opened. And again, if some silly form is not opened, why should your report fail to work?

So, remove the parameter. Now, in your form that opens the report, it can pass the filter, and more in point use what is a called a "where" clause. This "where" clause is designed in MS-access to solve the problem of having to know ahead of time what kind of parameters and filters you need. It occurs at runtime, and thus MANY DIFFERENT forms can call and open that report.

Now in the form that calls and opens the form, you go:

Docmd.OpenReport "rptSuppliers",acViewPreview, , _
                "SupplierCode = " & me.SupplierCode

So, in the above, the parameter is created on the fly. The great advantage is tomorrow you can have another form open the same report and perhaps filter by region.

In the case of NO where clause being passed and a user simply opening the form, then no filters will be used and no prompts will occur and all records will show. This is probably your best approach.

However if for some strange reason you still deem it REALLY necessary to have some report prompt when one silly form just happens to not be opened, then place the following code in the forms on-open event.

If CurrentProject.AllForms("form1").IsLoaded = False Then
   Me.Filter = "SupplierID = " & InputBox("Enter Supplier ID")
   Me.FilterOn = True
End

However, I would really make efforts to avoid hard coding some silly form name in the reports open event. Not only does this mean your hard coding dependencies of some silly form that is now attached to a report, but if you later on copy that report, or even copy the original form (or even rename any of these objects), then you have to go into the application and hunt about and now find the places you as a developer introduced dependences. This approach can substantially increase the maintenance costs of an application and thus should be advoied.

So, the suggestion here is to dump the parameter query. Simply provide a form or some prompt system to launch the reports. Those forms should prompt the user for the information you wish to filter. Or as in your case the bound form and it current record provides that information. The beauty of this system is now there is no depdancy from the report.

Any form, or even any code down the road is free to pass a pramaeter, and it will not be limited to SupplierID, but can be any type of filter or parameter you wish.

Keep in mind that perhaps the user might not want that form to be open and perhaps they don't want the prompt. With your design and question the user will be forced to enter a parameter value even when launching the report without any forms open and not desiring to be prompted to allow them to view all reocrds in that report.