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

Article translations Article translations
Article ID: 208529 - View products that this article applies to.
This article was previously published under Q208529
This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).

Moderate: Requires basic macro, coding, and interoperability skills.

Expand all | Collapse all

On This Page

SUMMARY

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:
231851 Microsoft Access 2000 Sample Reports Available in Download Center
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.

MORE INFORMATION

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.

REFERENCES

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.

Properties

Article ID: 208529 - Last Review: October 11, 2006 - Revision: 2.2
APPLIES TO
  • Microsoft Access 2000 Standard Edition
Keywords: 
kbdta kbhowto kbofficeprog kbprogramming KB208529

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com