MS Access report VBA command in Access 2003/XP/2000/97

Filter report results using the Open Report VBA command in Access 2003/XP/2000/97

---

In Access 2003/XP/2000/97, I have a report and I would like to be able to filter the report results without hard coding parameters in the Query Builder.

You can use the Open Report command in VBA to open a report and filter the results in many different ways.

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