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:

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.

Table 3-11. The SysCmd object state return values

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.

The next stretch of code does all the work:

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 optional—it provides a nice added touch.

The optional code in Step 5—which we added to the page footer's Format event in the sample report—documents 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).