Access Cookbook - Ken Getz [75]
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.
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.
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