Change Report RecordSource Property with VBA.

  • Thread starter Thread starter Andrew
  • Start date Start date
A

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?
 
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.
 
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.
 
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?
 
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.
 
Back
Top