Recipe 3.13 Print Only Records Matching a Form's Filter
3.13.1 Problem
You have a form that you
use to view and edit your collection of record and CD albums. On the
form, you've placed a command button that you use to
print the records contained in the form's recordset.
This works fine, but you'd like to enhance the
functionality of the form so that when you filter records on the form
and then print the report, only the filtered records will print. Is
there any way to do this in Access?
3.13.2 Solution
Access includes properties (Filter and
FilterOn) of forms and reports that you can use to manipulate form
and report filters programmatically. This solution shows you how to
use these properties to print on a report only those records filtered
by a form.Load 03-13.MDB and open the frmAlbums form. When
you press the Print Records button, you should see the preview of a
report, rptAlbums, which includes all 65 records from qryAlbums.
Close the report and go back to frmAlbums, which should still be
open. Now create a filter of the form's records
using one of the Filter toolbar buttons or the Records
Filter command. For example, you might create a filter by using the
Filter by Form facility (see Figure 3-30).
Figure 3-30. Filter by Form is used to filter records on frmAlbums
When you finish creating the filter, apply it. You should see a
filtered subset of the records (Figure 3-31).
Figure 3-31. The records have been filtered, resulting in three records
Now press the Print Records button. You should see a preview of the
same report, rptAlbums, this time filtered to match the records you
filtered using frmAlbums. If you print the filtered report, you
should see a report similar to the one shown in Figure 3-32.
Figure 3-32. The report includes only those records from the filtered form
To create your own report
that synchronizes its records with those of a
form's, follow these steps:
- Create a new form or edit an existing one. The sample form,
frmAlbums, is an unbound main form with an embedded subform bound to
the qryAlbums query, but you can use any style of form you like. - Create a new report or edit an existing one that's
based on the same record source as the form (or, if you are using an
embedded subform, that's based on the same record
source as the subform) from Step 1. Save the report and give it a
name. The sample report is named rptAlbums. - Switch back to the form. Add to the
form a command button with an event procedure that uses the
DoCmd.OpenReport method to open the report from Step 2 in preview
view. (For more information on creating event procedures, see the
Preface.) The code for the cmdPrint button on frmAlbums is shown
here:Private Sub cmdPrint_Click( )
Change "rptAlbums" to the name of
DoCmd.OpenReport "rptAlbums", View:=acPreview
End Sub
the report created in Step 2. Save the form and close it. - Switch back to the report and create an event procedure attached to
the report's Open event. Add code similar to that
shown here for rptAlbums:Private Sub Report_Open(Cancel As Integer)
Dim frmFilter As Form
Const acbcFilterFrm = "frmAlbums"
Const acbcFilterSubFrmCtl = "subAlbums"
' Is the the report's filtering form open?
If SysCmd(acSysCmdGetObjectState, acForm, acbcFilterFrm) <> 0 Then
Set frmFilter = Forms(acbcFilterFrm)
' Is the form currently filtered?
If frmFilter.FilterOn Then
' Set the report's filter to the subform's filter.
Me.Filter = frmFilter(acbcFilterSubFrmCtl).Form.Filter
' If the filter form didn't include a subform, use this
' (simpler) syntax instead:
' Me.Filter = frmFilter.Filter
Me.FilterOn = True
Me.Caption = Me.Caption & " (filtered)"
End If
End If
End Sub - Change the value of the acbcFilterFrm constant to
the name of the form and the acbcFilterSubFrmCtl
constant to the name of the subform control created in Step 1. If
your form doesn't include an
embedded subform, either delete
"(acbcFilterSubFrmCtl).Form" from
the 11th line of code or completely delete this line of code and the
two comment lines that follow and uncomment (remove the leading
single quote from) the following line of code:' Me.Filter = frmFilter.Filter
You should also delete the following line of code if you
aren't using a subform (although leaving it in
won't hurt):Const acbcFilterSubFrmCtl = "subAlbums"
- If you wish to display the filter value on the report whenever the
report is based on a filtered subset of records, add a text box
control to the page footer (or any other section you prefer) and name
this control txtFilter. Next, add the following code to an event
procedure attached to the section's Format event:Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer)
' If this report is filtered, make the txtFilter control visible
' and set its value to the Filter property of the report.
If Me.FilterOn Then
Me.txtFilter = Me.Filter
Me.txtFilter.Visible = True
Else
Me.txtFilter.Visible = False
End If
End Sub - Save the report and close it. You can test the report by opening the
filtering form, choosing various filters, and then pressing the Print
Records button on the form.
3.13.3 Discussion
This solution works by setting the
report's Filter property to the value of the
form's Filter property. The form's
and report's Filter properties contain the last
filter created for the object. Because the last filter hangs around
even after you've turned it off (by using the
Records Remove Filter/Sort command or the equivalent
toolbar button), the code in Step 4 first checks the status of the
FilterOn property. This property is set to True
when a filter is active and False when there is no
filter or when the existing filter isn't currently
active.At the beginning of the
report's Open event procedure, the code checks to
see if the form associated with this report is open, using the
following code:
If SysCmd(acSysCmdGetObjectState, acForm, acbcFilterFrm) <> 0 Then
SysCmd is a function that handles a number
of different chores including the following:
- Displaying a progress meter or text in the status bar.
- Returning status information about Access (such as the Access
directory, whether the runtime or retail product is running, and so
on). - Returning the state of a database object to indicate whether it is
open, is a new object, or has been changed but not saved.
You indicate to Access which flavor of SysCmd
you want by passing it an enumerated value as the first parameter.
(See the online help topic for the SysCmd function for more
information on the possible parameter values.) The code in the Open
event procedure passes SysCmd the
acSysCmdGetObjectState constant, which tells
SysCmd that you would like information on the
open status of the frmAlbums form. SysCmd
obliges by returning one of the values listed in Table 3-11 (the value 3 is skipped so that any
combination of values added together will result in a unique number).
In this case, you care only if the SysCmd return
value is nonzero.
SysCmd return value | Access constant | Meaning |
---|---|---|
0 | None | The object either doesn't exist or is closed. |
1 | acObjStateOpen | The object is open, but not new or dirty. |
2 | acObjStateDirty | The object is in an unsaved state. |
4 | acObjStateNew | The object is new and in an unsaved state. |
Set frmFilter = Forms(acbcFilterFrm)
' Is the form currently filtered?
If frmFilter.FilterOn Then
' Set the report's filter to the subform's filter.
Me.Filter = frmFilter(acbcFilterSubFrmCtl).Form.Filter
' If the filter form didn't include a subform, use this
' (simpler) syntax instead:
' Me.Filter = frmFilter.Filter
Me.FilterOn = True
Me.Caption = Me.Caption & " (filtered)"
End If
If the form is currently filtered (i.e., if frmFilter.FilterOn is set
to True, which in VBA is the same as just saying
frmFilterOn), the report's filter is set to the
form's filter. Because the subform control on the
form is actually being filtered, we set the report's
filter equal to the subform's filter.Notice that we used
"frmFilter(acbcFilterSubFrmCtl).Form.Filter"
rather than
"frmFilter(acbcFilterSubFrmCtl).Filter".
This odd-looking syntax tells Access that you want the Filter
property of the subform that the subform control contains, not the
Filter property of the subform control itself (which
doesn't have such a property).If no subform is used on the form, you can simplify the statement to
this:
Me.Filter = frmFilter.Filter
Next, the code sets the
report's FilterOn property to
True, which causes the report to be filtered using
the previously set Filter property. Finally, the code changes the
caption of the report so that
"(filtered)" appears in the
titlebar when you preview the report. This last statement is
optionalit provides a nice added touch.The optional code in Step
5which we added to the page footer's Format
event in the sample reportdocuments the filter by displaying
it in a text box on the report. The syntax of the filter is the same
as that of a SQL Where clause (without the
WHERE keyword).You may also wish to set the
report's OrderBy property to the
form's OrderBy property. If you do this, you must
also check the status of the OrderByOn property, which is analogous
to the FilterOn property. The syntax of the OrderBy property is
similar to that of the SQL Order
By clause (without the ORDER
BY keyword).