Run a report in MS-ACCESS using a subform’s filtered criteria – VBA

I created a quick reporting system in MS-ACCESS that allows the user to search various criteria with the results returning in a subform. Those results could then be filtered in various ways which had to be reflected in the final report.

This VBA script is what I used to capture the filtered results and include them in the report’s source record. The event is triggered by clicking a ‘Run Report’ button on the search form.

Private Sub btnReport_Click()
On Error GoTo Err_btnReport_Click
Dim stDocName As String

stDocName = "tblRecord1"
 'check for subform filter
 If Me!FinDisc_AdHocsubreport.Form.Filter = "" Then   MsgBox "Apply a filter to the form first."
 Else
'include filter in report criteria
 DoCmd.OpenReport stDocName, acViewPreview, , Me!FinDisc_AdHocsubreport.Form.Filter  
 End If

Exit_btnReport_Click:
 Exit Sub

Err_btnReport_Click:
 MsgBox Err.Description
 Resume Exit_btnReport_Click

End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>