The following is copied from Greens web site and I have 2 questions concerning the code below:
How can the code be set for more than 1 report?
How can the form and report be opened at the same time?
The purpose of this database is to demonstrate how an Access report can be filtered and sorted at run-time using VBA and SQL. It contains three tables: (tblStaff, tblOffices and tblDepartments) five forms (frmReportFilter1 - 5) and a report (rptStaff). The tblStaff table contains some sample data in the form of a list of the staff of a fictional, multinational company. The other two tables contain lists of the company Offices and Departments and provide the Row Source for combo boxes and list boxes.
TO USE THE DIALOG BOXES you should first open the report in Print Preview view, then open one of the forms. Make your choice from the controls on the dialog and click the Apply Filter button. You will see the report change to reflect your choices. A Remove Filter button is provided to restore the original recordset.
Option Compare Database
Private Sub cmdApplyFilter_Click()
Dim strOffice As String
Dim strDepartment As String
Dim strFilter As String
' Check that the report is open
If SysCmd(acSysCmdGetObjectState, acReport, "rptStaff") <> acObjStateOpen Then
MsgBox "You must open the report first."
' Build criteria string for Office field
If IsNull(Me.cboOffice.Value) Then
strOffice = "Like '*'"
strOffice = "='" & Me.cboOffice.Value & "'"
' Build criteria string for Department field
If IsNull(Me.cboDepartment.Value) Then
strDepartment = "Like '*'"
strDepartment = "='" & Me.cboDepartment.Value & "'"
' Combine criteria strings into a WHERE clause for the filter
strFilter = "[Office] " & strOffice & " AND [Department] " & strDepartment
' Apply the filter and switch it on
.Filter = strFilter
.FilterOn = True
Private Sub cmdRemoveFilter_Click()
On Error Resume Next
' Switch the filter off
Reports![rptStaff].FilterOn = False
Once the report is opened and the OnOpen event has been triggered on the report, you cannot change the reports data. To apply a filter to the report use the DoCmd.OpenReport using the Where property to pass the filter to the report.
See the Help file on the full syntax for the OpenReport property and you will see that you can easily use the same command for multiple reports - just replace the report name and the Where property.
You can have as many forms and reports open as your system can handle. However the same report can only be opened once.