Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2004

    Unanswered: Access 2002 Help

    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
    Option Explicit

    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."
    Exit Sub
    End If
    ' Build criteria string for Office field
    If IsNull(Me.cboOffice.Value) Then
    strOffice = "Like '*'"
    strOffice = "='" & Me.cboOffice.Value & "'"
    End If
    ' Build criteria string for Department field
    If IsNull(Me.cboDepartment.Value) Then
    strDepartment = "Like '*'"
    strDepartment = "='" & Me.cboDepartment.Value & "'"
    End If
    ' Combine criteria strings into a WHERE clause for the filter
    strFilter = "[Office] " & strOffice & " AND [Department] " & strDepartment
    ' Apply the filter and switch it on
    With Reports![rptStaff]
    .Filter = strFilter
    .FilterOn = True
    End With
    End Sub

    Private Sub cmdRemoveFilter_Click()
    On Error Resume Next
    ' Switch the filter off
    Reports![rptStaff].FilterOn = False
    End Sub


  2. #2
    Join Date
    Nov 2003
    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.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts