Microsoft KB Archive/147143

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

Article ID: 147143

Article Last Modified on 1/19/2007

-

APPLIES TO


 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q147143



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



SUMMARY
This article describes how to filter a report dynamically in Print Preview by selecting filter criteria from a pop-up form.

NOTE: This article explains a technique demonstrated in the sample files, RptSampl.exe (for Microsoft Access for Windows 95 version 7.0) and RptSmp97.exe (for Microsoft Access 97). For information about how to obtain these sample files, please see the following articles in the Microsoft Knowledge Base:

145777 ACC95: Microsoft Access Sample Reports Available in Download Center

175072 ACC97: Microsoft Access 97 Sample Reports Available in Download Center



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
 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 report's property sheet 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: frmFilter -     ScrollBars: Neither RecordSelectors: No     NavigationButtons: No      PopUp: Yes BorderStyle: Thin MinMaxButtons: None Width: 2.5"                       </li>  Set the form's OnOpen property 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 form's OnClose property 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 in the detail section. Place them on the form vertically, one below each other:

NOTE: In the SQL expressions below, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this expression.

<pre class="fixed_text">     Combo box: Name: Filter1 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 your form, which will enable you to reset the values in the form's combo boxes and check boxes:

<pre class="fixed_text">     Command button: Name: Clear Caption: Clear OnClick: [Event procedure]

Set the OnClick [Event procedure] as follows:

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 your form:

<pre class="fixed_text">     Command Button: Name: Set Filter Caption: Set Filter OnClick: [Event procedure]

Set the OnClick [Event procedure] as follows:

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 your form:

<pre class="fixed_text">     Command Button: Name: Close Caption: Close OnClick: [Event procedure]

Set the OnClick [Event procedure] as follows:

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 Clear to reset the report's Filter property.

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 report's Filter property before making new choices in the combo boxes.

<div class="references_section">