Access: Printing a Filtered Report

By Helen Feddema
Access versions: 2007-2013
Level: Intermediate

The main menu of the sample database for Access Archon #212 (A New Style Main Menu) has selectors for filtering a report by CustomerID or OrderNo. But sometimes you might want to print a filtered report right from the current record of a form. That is the topic of this article.

The Form

The Customers and Orders form is a standard main form/subform form, with a record selector in the form header, and two buttons for printing a filtered report in the form footer:

Figure A. The Customers and Orders form

Method 1: Using the Filter Property of a Report

The simplest way to print a filtered report is to save a value (usually the key field of the underlying table) to a string, which is used to set the wherecondition argument of the OpenReport method. Here is the code for this method (note that the ID value is wrapped in Chr(39) because it is a string):

Private Sub cmdPrintFilteredReportMethod1_Click()

On Error Resume Next

DoCmd.RunCommand acCmdSaveRecord

On Error GoTo ErrorHandler

Dim strID As String

Dim strFilter As String

strID = Nz(Me![CustomerID].Value)

‘Check for ID

If strID <> “” Then

strFilter = “[CustomerID] = ” & Chr(39) & strID & Chr(39)

Debug.Print “Filter string: ” & strFilter

DoCmd.OpenReport reportname:=”rptCustomersAndOrders”, _

view:=acViewPreview, _

wherecondition:=strFilter

End If

ErrorHandlerExit:

Exit Sub

ErrorHandler:

MsgBox “Error No: ” & Err.Number _

& ” in ” & Me.ActiveControl.Name & ” procedure; ” _

& “Description: ” & Err.Description

Resume ErrorHandlerExit

End Sub

For a more typical AutoNumber ID, the filter string would be something like this:

strFilter = “[CustomerID] = ” & lngID

Method 2: Filtering by a Database Property

The second method saves the ID from the current record to a custom database property, which is then used to filter the record source query of the report opened from this code. See Access Archon #152 (Storing Data in Custom Database Properties) for more details on using database properties.

Private Sub cmdPrintFilteredReportMethod2_Click()

On Error Resume Next

DoCmd.RunCommand acCmdSaveRecord

On Error GoTo ErrorHandler

Dim strID As String

‘Check for ID

strID = Nz(Me![CustomerID].Value)

If strID <> “” Then

‘Save ID to custom database property used to filter report

strPropertyName = “CustomerID”

lngDataType = dbText

Call SetProperty(strPropertyName, lngDataType, strID)

DoCmd.OpenReport reportname:=”rptSelectedCustomerAndOrders”, _

view:=acViewPreview

End If

ErrorHandlerExit:

Exit Sub

ErrorHandler:

MsgBox “Error No: ” & Err.Number _

& ” in ” & Me.ActiveControl.Name & ” procedure; ” _

& “Description: ” & Err.Description

Resume ErrorHandlerExit

End Sub

The filtered report looks the same for both methods:

Figure B. A filtered report opened from a form button

References

The code in the sample database does not need any special references.

Supporting Files

The zip file containing this article, in Word 97-2003 format, plus the supporting file(s), may be downloaded from the Access Archon page of my Web site, as accarch227.zip, which is the last entry in the table of Access Archon columns for Access Watch.