Access VBA change Report Record Source

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.

access-vba-change-report-record-source-01

 

To add criteria to the Report, there are three ways

Method 1 – Write SQL

Type the full SQL statement directly in Record Source.

access-vba-change-report-record-source-02

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.

access-vba-change-report-record-source-05

Method 3 – Query Builder

access-vba-change-report-record-source-01

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.

access-vba-change-report-record-source-05

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

access-vba-change-report-record-source-03

 

I have to use Table student_tbl as Record Source for the Report

student_tbl
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

access-vba-change-report-record-source-04

 

 

Wyman W
Wyman is a Business Analyst based in Hong Kong, specialized in business analysis, project management, and also creating custom Function and Sub solutions, and is proficient in report automation with Access.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

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