Microsoft KB Archive/208529

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

Article ID: 208529

Article Last Modified on 10/11/2006

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



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 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.

 Open the sample database Northwind.mdb. Start the Report Wizard and create a report based on the Customers table.  In the Which fields do you want on your report box, select the following fields, and then click Next:   CompanyName ContactName City Region Country  In the Do you want to add any grouping levels? box, click Next. In the What sort order do you want for your records? box, click Next. In the How would you like to layout your report? box, click Tabular, and then click Next.</li> In the What style would you like? box, click Next.</li> In the What title do you want? box, enter rptCustomers. Click Modify the report's design, and then click Finish.</li> In Design view, display the property sheet of the report, and set the FilterOn property to Yes.</li> Save and close the report.</li></ol>

Creating the Pop-up Form
<ol>  Create a new form not based on any table or query in Design view with the following form properties: <pre class="fixed_text">  Form: Test1 -  Caption: TestForm ControlSource: Test Table ScrollBars: Neither RecordSelectors: No  NavigationButtons: No   PopUp: Yes BorderStyle: Thin MinMaxButtons: None Width: 2.5"                   </li>  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                    </li>  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                    </li>  Add the following five combo boxes to the detail section. Place them on the form vertically, one below the other: <pre class="fixed_text">   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"                   </li>  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: <pre class="fixed_text">   Combo box   --   Name: Clear   Caption: Clear   OnClick: [Event procedure]                    </li>  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                    </li>  Add a second command button to the form as follows: <pre class="fixed_text">   Command Button   --   Name: Set Filter   Caption: Set Filter   OnClick: [Event procedure]                    </li>  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 </li>  Add a third command button to the form as follows: <pre class="fixed_text">  Command button -  Name:Close Caption: Close OnClick: [Event procedure] </li>  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 </li> Close and save the form as frmFilter.</li></ol>

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.

<div class="references_section">