MS Access 2003: Filter report results using the OpenReport VBA command
This MSAccess tutorial explains how to filter report results using the OpenReport VBA command in Access 2003 (with screenshots and step-by-step instructions).
Question: In Microsoft Access 2003/XP/2000/97, I have a report and I would like to be able to filter the report results without hardcoding parameters in the Query Builder. How can I do this?
Answer: You can use the OpenReport command in VBA to open a report and filter the results in many different ways.
We've created an Access sample that you can download.
In this example, we've created 3 buttons:
- All Suppliers button
- Current Supplier only button
- SupplierID > 5 and CompanyName starts with S button
Each of these buttons will apply a different filter to the rptSuppliers report allowing you to filter your report results in whatever way you see fit.
The VBA code behind the "All Suppliers" button is:
Private Sub cmdAll_Click() 'Display all suppliers in rptSuppliers report DoCmd.OpenReport "rptSuppliers", acViewPreview End Sub
The VBA code behind the "Current Supplier only" button is:
Private Sub cmdCurrent_Click() 'Filter report to display only Supplier currently showing on frmExample ' (by SupplierID field) DoCmd.OpenReport "rptSuppliers", acViewPreview, , "SupplierID = " & SupplierID End Sub
The VBA code behind the "SupplierID > 5 and CompanyName starts with S" button is:
Private Sub cmdComplex_Click() 'Filter report to display only Suppliers whose SupplierID > 5 and ' CompanyName starts with S DoCmd.OpenReport "rptSuppliers", acViewPreview, , "SupplierID > 5 and CompanyName like 'S*'" End Sub
Advertisements