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

• Microsoft Access 2000

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.
Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals 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 needs. If you have limited programming experience, you may want to contact a Microsoft Certified Solution Provider or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Solution Providers, please see the following page on the World Wide Web:

http://www.microsoft.com/mcsp/

http://www.microsoft.com/support/supportnet/overview/overview.asp

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

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.

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.

Additional query words: dynamic popup acccon

Keywords : kbdta FmrHowto RptSort
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbhowto