Passing a parameter to an open report event in a parameter request (Access 2007)

Question

I would like to know if there is a way to set parameters in an Access 2007 request using VBA. I'm new to using VBA in Access, and I was tasked with adding a bit of functionality to an existing application.

The problem I am facing is that the same report can be called up in two different places in the application. The first is on the command button in the data entry form, the other is on the switch button. The report itself is based on a request for a parameter that requires the user to enter a vendor identifier.

The user does not want to enter the vendor identifier in the data entry form (since the form already displays the vendor identifier), but they will be prompted from the switch to enter the vendor identifier.

Where I am stuck - how to call a report request (in the report event that opens) and pass RequestID from the form as a parameter. I tried for a while, and I can't get something to work correctly. Here is my code so far, but I'm clearly confused.

Private Sub Report_Open (Undo as a whole)

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 tried Me.SupplierID = intSupplierCode, and although it compiles, it bombs when I run it. And here is my SQL code to request the parameter:

OPTIONS [Enter Supplier] Long; SELECT Suppliers.SupplierID, Suppliers.CompanyName, Suppliers.ContactName, Suppliers.ContactTitle Suppliers WHERE (((Suppliers.SupplierID) = [Enter Supplier]));

I know that there are ways around this problem (and probably an easy way), but, as I said, my lack of experience using Access and VBA complicates the situation. If any of you could help, that would be great!

Answer 1

The proposed offer is 100% DELETE a parameter from the request. This not only solves your problem, but also means that you can use the request for code, other forms and not ruin the whole design, because one silly form is not open (therefore, it is VERY reason for your question).

So, remove the parameters from the request. It also means that your report will no longer need some form that is already open. And again, if some dumb form doesn't open, why doesn't your report work?

So delete the parameter. Now, in your form that opens the report, it can pass the filter, and use what is called the where clause more. This "where" clause was developed in MS-access to solve the problem of knowing in advance which parameters and filters you need. This happens at runtime, and so MANY DIFFERENT forms can invoke and open this report.

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

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

So, in the above example, the parameter is created on the fly. The big advantage is that tomorrow you can have a different form, open the same report and possibly filter by region.

In the case of passing the NO where clause and the user simply opening the form, no filters will be used, and there will be no queries, and all entries will be displayed. This is probably your best approach.

However, if for some strange reason you still think it is necessary, you need to have a hint for a report when one silly form simply does not open, then put the following code in the open form event.

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

However, I would really try to avoid hard-coding some dumb form name in the reporting event that opens. This not only means that your hardcodes depend on some kind of silly form, which is now attached to the report, but if you later copy this report or even copy the original form (or even rename any of these objects), then you have to go to app and hunt for and now find places where you as a developer made dependencies. This approach can significantly increase the cost of maintaining the application and, therefore, should be recommended.

So, the suggestion here is to reset the parameter request. Just create a form or some kind of operating system to run reports. These forms should ask the user for the information you want to filter. Or, as in your case, the linked form and current record provide this information. The beauty of this system is no longer reflected in the report.

Any form, or even any code on the road, can freely pass a pramaeter, and it will not be limited by the ProviderID, but it can be any type of filter or parameter that you want.

Keep in mind that perhaps the user may not want to open this form and may not want an invitation. With your design and question, the user will be forced to enter the parameter value even when starting the report without any open forms and not wanting to be asked to allow them to view all the records in this report.

Answer 2

As I stated in a recent post , I try to never set any parameters or create control links in the sources of reports or forms. Instead, I install them at runtime. The easiest way is to pass the WhereCondition property to DoCmd.OpenForm / DoCmd.OpenReport:

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

It is assumed that you run it from a form that has the corresponding ProviderID already present in its record source (i.e. you are in a record with this provider identifier).

More complex is the use of the OnOpen report event to set the report source. This is what I outlined in the above post . But this example makes it difficult to choose a form of choice, while you can instead propose different choices depending on the context. There are two ways to handle this:

• if A2003 and later, pass OpenArg (the last parameter DoCmd.OpenReport) to tell the OnOpen event what to do in order to collect information about what to filter.

• use an external structure, such as a stand-alone class, to store the criteria that the OnOpen event will read and act accordingly.

I suspect WhereCondition in DoCmd.OpenReport is the easiest solution, but if you want to drill down on the other two, just ask.