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:

http://img.office-watch.com/ow/Access%20-%20Printing%20a%20Filtered%20Report%201.png image from Access: Printing a Filtered Report at Office-Watch.com

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):

VBA Code

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.

VBA Code

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:

http://img.office-watch.com/ow/Access%20-%20Printing%20a%20Filtered%20Report%202.png image from Access: Printing a Filtered Report at Office-Watch.com

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.











Document Name

Document Type

Place in

Printing a Filtered Report (AA 226).accdb

Access 2007/2010 database (can also be used in higher versions of Access)

Wherever you want