ACC2000: How to Filter a Report from a Pop-Up Form

This article shows you how to create a report that you can filter dynamically in Print Preview by selecting filter criteria from a pop-up form.

NOTE: This article explains a technique demonstrated in the sample file, RptSmp00.mdb. For information about how to obtain this sample file, please see the following article in the Microsoft Knowledge Base: Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.

The technique involves creating a pop-up form and a report in the sample database Northwind. The form enables you to choose which fields and values to use for filtering a report in Print Preview.

Creating the Report

CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

1. Open the sample database Northwind.mdb.
2. Start the Report Wizard and create a report based on the Customers table.
3. In the Which fields do you want on your report box, select the following fields, and then click Next:

      CompanyName
      ContactName
      City
      Region
      Country
   					

4. In the Do you want to add any grouping levels? box, click Next.
5. In the What sort order do you want for your records? box, click Next.
6. In the How would you like to layout your report? box, click Tabular, and then click Next.
7. In the What style would you like? box, click Next.
8. In the What title do you want? box, enter rptCustomers. Click Modify the report's design, and then click Finish.
9. In Design view, display the property sheet of the report, and set the FilterOn property to Yes.
10. Save and close the report.

Creating the Pop-up Form

1. Create a new form not based on any table or query in Design view with the following form properties:

      Form: Test1
      -------------------------
      Caption: TestForm
      ControlSource: Test Table
      ScrollBars: Neither
      RecordSelectors: No
      NavigationButtons: No
      PopUp: Yes
      BorderStyle: Thin
      MinMaxButtons: None
      Width: 2.5"
   					

2. Set the OnOpen property of the form to the following event procedure:

   Private Sub Form_Open(Cancel As Integer)
      DoCmd.OpenReport "rptCustomers", A_PREVIEW 'Open Customers report.
      DoCmd.Maximize  'Maximize the report window.
   End Sub
   					

3. Set the OnClose property of the form to the following event procedure:

   Private Sub Form_Close()
      DoCmd.Close acReport, "rptCustomers" 'Close the Customers report.
      DoCmd.Restore  'Restore the window size
   End Sub
   					

4. Add the following five combo boxes to the detail section. Place them on the form vertically, one below the other:

      Combo box
      -------------------------------------------------------------
      Name: Filter1
      ControlSource: Selection Field
      Tag: CompanyName
      RowSource: Select Distinct [CompanyName] from Customers Order 
                 By [CompanyName];
      Width: 1.5"
   
      Combo box
      -------------------------------------------------------------
      Name: Filter2
      Tag: ContactName
      RowSource: Select Distinct [ContactName] from Customers Order 
                 By [ContactName];
      Width: 1.5"
   
      Combo box
      ---------------------------------------------------------------
      Name: Filter3
      Tag: City
      RowSource: Select Distinct [City] from Customers Order By [City];
      Width: 1.5"
   
      Combo box
      -----------------------------------------------------------
      Name: Filter4
      Tag: Region
      RowSource: SELECT DISTINCT Customers.Region FROM Customers 
                 WHERE(((Customers.Region) Is Not Null)) ORDER BY 
                 Customers.Region;
      Width: 1.5"
   
      Combo box
      -----------------------------------------------------------------------
      Name: Filter5
      Tag: Country
      RowSource: Select Distinct [Country] from Customers Order By [Country];
      Width: 1.5"
   					

5. Add the following command button to the form, which will enable you to reset the values in the combo boxes and check boxes of the form:

      Combo box
      --------------------------
      Name: Clear
      Caption: Clear
      OnClick: [Event procedure]
   					

6. Set the OnClick property of the command button to the following event procedure:

   Private Sub Clear_Click()
      Dim  intCounter as Integer
      For intCounter = 1 To 5
          Me("Filter" & intCounter) = ""
      Next
   End Sub
   					

7. Add a second command button to the form as follows:

      Command Button
      --------------------------
      Name: Set Filter
      Caption: Set Filter
      OnClick: [Event procedure]
   					

8. Set the OnClick property of the second command button to the following event procedure:

   Private Sub Set_Filter_Click()
   Dim strSQL as String, intCounter as Integer
   
   ' Build SQL String.
     For intCounter = 1 To 5
         If Me("Filter" & intCounter) <> "" Then
   
            strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " _
                & " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & " _
                And    "
         End If
     Next
   
     If strSQL <> "" Then
        ' Strip Last " And ".
        strSQL = Left(strSQL, (Len(strSQL) - 5))
   
        ' Set the Filter property.
        Reports![rptCustomers].Filter = strSQL
        Reports![rptCustomers].FilterOn = True
      End If
   End Sub
   					

9. Add a third command button to the form as follows:

      Command button
      -------------------------
      Name:Close
      Caption: Close
      OnClick: [Event procedure]
   					

10. Set the OnClick property of the third command button to the following event procedure:

    Private Sub Close_Click()
       DoCmd.Close acForm, Me.Form.Name
    End Sub
    					

11. Close and save the form as frmFilter.

Filtering the Report

1. Open the frmFilter pop-up form in Form view. Note that the rptCustomers report opens in Print Preview behind the form and displays all records in the Customers table.
2. In the Region combo box, select "BC," and then click the Set Filter button. You should see only the records that contain "BC" in the Region field.
3. Click the Clear button to reset the Filter property of the report.

NOTE: To experiment with various combinations of data, you can select an item from one or more combo boxes and click the Set Filter button. When you are ready to select different items, first click the Clear button to reset the Filter property of the report before making new choices in the combo boxes.

For more information about the Filter property, click Microsoft Access Help on the Help menu, type filter property in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

For more information about Filter By Form, click Microsoft Access Help on the Help menu, type filter by form in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

For more information about Filter By Selection, click Microsoft Access Help on the Help menu, type filter records by selecting values in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.