ACC: How to Filter a Report from a Pop-Up Form
ID: Q147143
|
The information in this article applies to:
-
Microsoft Access versions 7.0, 97
SUMMARY
Moderate: Requires basic macro, coding, and interoperability skills.
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:
Q145777 ACC95: Microsoft Access Sample Reports Available in Download Center
Q175072 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.
- In the "What style would you like?" box, click Next.
- In the "What title do you want?" box, enter rptCustomers.
Click "Modify the report's design," and then
click Finish.
- In Design view, display the report's property sheet and set the
FilterOn property to Yes.
- Save and close the report.
Creating the Pop-up Form
- Create a new form not based on any table or query in Design view with
the following form properties:
Form: frmFilter
---------------------
ScrollBars: Neither
RecordSelectors: No
NavigationButtons: No
PopUp: Yes
BorderStyle: Thin
MinMaxButtons: None
Width: 2.5"
- 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
- 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
- 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.
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"
- 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:
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
- Add a second command button to your form:
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
- Add a third command button to your form:
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
- Close and save the form as frmFilter.
Filtering the Report
- 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.
- 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.
- 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.
REFERENCES
For more information about the Filter property, search the Help Index for
"Filter Property," or ask the Microsoft Access 97 Office Assistant.
For more information about filter by form or filter by selection, search
the Help Index for "Filter By Form" or "Filter By Selection," or ask the
Microsoft Access 97 Office Assistant.
Additional query words:
dynamic popup
Keywords : FmrHowto RptSort
Version : 7.0 97
Platform : WINDOWS
Issue type : kbhowto
|