12.7 Create a PowerPoint Presentation from Access Data

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 08-13.MDB and open the frmAlbums form. When you press the Print Records button, you should see the preview of a report, rptAlbums, that 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 new 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:

1. 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.

2. 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.

3. 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(  )     DoCmd.OpenReport "rptAlbums", View:=acPreview End Sub

   Change "rptAlbums" to the name of the report created in Step 2. Save the form and close it.

4. 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

5. 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"

6. 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

7. 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 potpourri function, which allows you to do the following:

• Display a progress meter or text in the status bar.

• Return status information about Access (such as the Access directory, whether the runtime or retail product is running, and so on).

• Return 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 a constant as the first parameter. (You can find each of the SysCmd constants in the online help.) 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 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).