CurrentDb.QueryDefs("MyQuery").SQL = "SELECT * FROM MyTable"
there is no need to Dim or Set anything, simply plug in the SQL and you're ready to go.
However, I just discovered that this does not work in a replicated database. Or I should say, it works in the Design Master but it does not work in a Replica. I needed to apply some complex criteria, more complex than could be expressed simply by using a WHERE condition when opening a report. Testing the code in the DM worked just fine, but users received a message "cannot update...object is read-only" because Access considers this to be a design change & does not allow that in a Replica.
You can code things like changing visibility, font colors, enabling & disabling controls, but QueryDefs are off limits.
The challenge: I had an existing report that used a query for the detail section, and the report footer contained a subreport with a summary that used the same query as the body of the report. Opening the report with a filter or where argument would properly filter the detail section, but the subreport would display all of the data. I tried using the OnOpen or OnFormat event to apply a filter to the subreport but that did not work.
(EDIT) At this point, I tried to change the SQL for the QueryDef using the type of statement shown above, to filter the data for both the body of the report and the summary subreport; this worked in the DM but not the Replica. My original post has not been clear about that. (EDIT)
My solution was basically to create a staging table for the subreport, and flush-and-fill the table prior to opening the report.
In the DM I did this:
- Ran the old (bad) code to set up the SQL statement
- Switched the query to a make-table, to create the staging table
- Switched the query to an append query
- Copied the append SQL and used that to build up a dynamic SQL statement in VBA
