You are currently offline, waiting for your internet to reconnect

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

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.

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 samplefile, 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 sampledatabase Northwind. The form enables you to choose which fields and valuesto 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 sizeEnd 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) = ""   NextEnd 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 IfEnd 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.NameEnd 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.
dynamic popup acccon
Properties

Article ID: 208529 - Last Review: 10/11/2006 19:46:57 - Revision: 2.2

  • Microsoft Access 2000 Standard Edition
  • kbdta kbhowto kbofficeprog kbprogramming KB208529
Feedback