Change Report RecordSource Property with VBA.

[Andrew]

I am trying to change the RecordSource Property of a report. The selected
fields will remain the same, but the related fields and "Where" criteria will
constantly change. I keep getting a runtime error when I attempt to assign a
new select statement to the report's RecordSource Property.

How do I make the assignment?

 

[Duane Hookom]

Where/when are you attempting to change the Record Source? Did you try in the
On Open event of the report?

I generally change the SQL property of a saved query prior to opening the
report. The saved query is used as the Record Source of the report.

 

[Andrew]

There is no Saved query the RecordSource is an SQL Statement assigned to the
RecordSource Property within the report.

However, I did attempt what you suggested and could figure out how to change
the query before opening the report either.

 

[Andrew]

I meant couldn't figure out how to change the saved query.

 

[Duane Hookom]

You didn't need to use the saved query if you didn't want to. It was only a
suggestion.

I use some DAO code like:
CurrentDb.QueryDefs("qselMyQuery").SQL = "SELECT ... FROM .... WHERE ...."

My function for changing the SQL is a little more robust but this is the one
line version.

Did you attempt to change the Record Source in the On Open event of the
report?

 

[fraseathome]

Possible Solution

I would send the SQL/table name/query name to use as your record source in the openargs when you open the report.

DoCmd.OpenReport "ReportName", acViewPreview, , "Any report filters you want to set",, "SELECT * FROM MyTable"

Then in the open event of the report (ctrl+F11 when you have the report in design view will open the code) I would put this.

Private Sub Report_Open(Cancel As Integer)
Me.RecordSource = Me.OpenArgs
End Sub

I hope this helps.