Editing Record Source property for a Report in VBA

[smiley1978]

I need to edit the record source of a report using VBA but I do not know the code to set the Report.

In the past, I have used the following lines of code to update various properties in queries or to add records to tables.

Set db = CurrentDb
Set rst = db.QueryDefs("QueryName")
Set ULrst = dbs.OpenRecordset("TableName")

I am looking for the same code that would allow me to set a report something like below.

Set RptDef = db.ReportDefs("ReportName")


I am trying to change the RowSource SQL of a report based on selections made on a form.

 

[Joe4]

Instead of changing the RecordSouce of the Report, I always change the SQL Code of the Query that the Report is based on.
So I use VBA to build the SQL code I need, and then use QueryDefs to change the SQL code on my static query name.

 

[xenou]

Presumably this will also work, same as it would for a form:

Sub foo()
    [Reports]![rptDepartments].RecordSource = "select * from Departments"
End Sub

http://access-excel.tips/difference-record-source-control-row/

one of the things about doing it this way is that the report has to be open (because otherwise it's not something you can change at runtime - in practical terms, you'd have to open the report, then change it's recordsource). Joe's way is how I usually roll as well - change the sql that underlies the report then open the report. You also have the option of tweaking the form's filter values at the time that you open it, if you are opening the report from your form.