This Access tutorial explains how to change Access Report Record Source using VBA in order to modify Report criteria automatically.
Change Report Record Source
When you create an Access Report, you can specify the data source of the report in Report Record Source, which can be a Query or Table.
Type the Table / Query name in the Record Source as below.
To add criteria to the Report, there are three ways
Method 1 – Write SQL
Type the full SQL statement directly in Record Source.
Method 2 – Add Filter
Type SQL criteria in the Filter field (the criteria after WHERE Clause in SQL), make sure the Filter On Load property is set to Yes.
Method 3 – Query Builder
Click on the button with 3 dots on the right hand side of the Record Source field, enter into Query Builder to add criteria like how you do in Query Design.
This works well for Table as Record Source, but for Query, the criteria will add to the original Query, so you have to use Method 1 or 2.
Change Report Record Source in VBA
Suppose we have a headcount Report template, but we need to send each department a separate Report. It is time consuming to maintain one Report template for each department, instead we can write VBA to change the department criteria so that we only need to maintain one Report template.
To change Report Record Source in VBA, we do it through OpenReport Method argument “Criteria”, this is equivalent to adding criteria in Filter.
DoCmd.OpenReport "Report Name", acViewPreview, "Query Name", "Criteria"
OpenReport will actually open the Report, so we have to write another statement to close the Report if you want it to close after use.
DoCmd.Close acReport, "Report Name", acSaveNo
Example – Change Report Record Source in VBA
Let’s take the above department scenario as an example. Suppose I have a Report called student_rpt
I have to use Table student_tbl as Record Source for the Report
| Student ID | Student Name | Department |
|---|---|---|
| 001 | David | HR |
| 002 | Elyse | Finance |
In order to maintain only one Report template while I need to send PDF reports to HR and Finance separately, run the below Macro.
Public Sub chg_rcSource() DoCmd.OpenReport "student_rpt", acViewPreview, "student_tbl", "Department='HR'" DoCmd.OutputTo ObjectType:=acOutputReport, ObjectName:="student_rpt", OutputFormat:=acFormatPDF, Outputfile:="C:\test\HR Report.pdf" DoCmd.Close acReport, "student_rpt", acSaveNo DoCmd.OpenReport "student_rpt", acViewPreview, "student_tbl", "Department='Finance'" DoCmd.OutputTo ObjectType:=acOutputReport, ObjectName:="student_rpt", OutputFormat:=acFormatPDF, Outputfile:="C:\test\Finance Report.pdf" DoCmd.Close acReport, "student_rpt", acSaveNo End Sub
Two PDF are exported
