How to Pass a Variable to Access DoCmd.OpenReport Using OpenArgs
Question details
The user needs to pass a variable via the OpenArgs parameter when opening a Microsoft Access report and utilize that variable to filter the report data.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Opening a report in Microsoft Access using VBA DoCmd.OpenReport and attempting to dynamically filter the output using a passed variable.
- Observed behavior
- Passing a value through the OpenArgs argument does not automatically filter the report; the report opens but ignores the intended filter unless handled by specific code.
Ensure you have basic familiarity with Microsoft Access VBA and have opened your Access database in Design View to modify the report's code module.
Apply Filter Using OpenArgs in the Report's Open Event
Write VBA code in the report's On Open event to read the passed OpenArgs variable and actively apply it as a filter.
Passing a variable via OpenArgs does not automatically filter the record source by default. You must explicitly catch this variable in the report's VBA module and set the filter properties manually.
In your form's VBA code, pass the variable using the OpenArgs parameter: DoCmd.OpenReport "YourReportName", , , , , "YourVariableValue".
Navigate to the target report, right-click, and select Design View. Open the Property Sheet, go to the Event tab, and click the ellipses (...) next to 'On Open' to launch the VBA editor.
Insert the following VBA code to read Me.OpenArgs: Dim strOpenArgs As String strOpenArgs = Nz(Me.OpenArgs, "") If Len(strOpenArgs) > 0 Then Me.Filter = "[YourFieldName] = '" & strOpenArgs & "'" Me.FilterOn = True End If
Save the VBA module and the report. Trigger the DoCmd.OpenReport action again to see the filtered results.
Use a Saved Query Referencing Form Controls
Set a query as the report's record source and reference form controls directly in the query criteria. This is highly recommended when exporting filtered reports to PDF.
Need a Reliable Office Suite? Try WPS Office
While WPS Office does not feature a database tool like MS Access, it is an outstanding free alternative for Word, Excel, and PowerPoint files. Enjoy high format compatibility, a lightweight installer, and a familiar interface for your daily document tasks.
- 1. Download the Installer: Visit the official WPS Office website and click the free download button.
- 2. Install WPS Office: Run the setup file and follow the on-screen prompts for a fast installation.
- 3. Work with Office Files: Open, edit, and save your existing Word, Excel, and PowerPoint documents seamlessly.
Frequently Asked Questions
Why is my Access report ignoring the OpenArgs parameter?
By default, Microsoft Access does not automatically apply OpenArgs as a data filter. You must explicitly write VBA code in the report's On Open event to read Me.OpenArgs and assign it to the Me.Filter property while setting Me.FilterOn = True.
Can I use OpenArgs to pass multiple variables to a report?
Yes, you can pass multiple values by concatenating them into a single string separated by a specific delimiter (like a pipe | or comma). In the report's Open event, you can use the Split() function in VBA to extract and assign the individual values to your filter string.
Is it better to use OpenArgs or a query reference to filter an Access report?
It depends on your workflow. OpenArgs is excellent for flexible, on-the-fly filtering driven entirely by VBA. However, if you are exporting the report to a PDF using DoCmd.OutputTo, using a query with criteria referencing an open form control (e.g., =Forms!FormName!ControlName) is often more reliable.
