Report QueryDef

[Paul Johnson]

I have been using a saved query (call it qTemp), which is the Record Source
for a report (call it MyReport).

I have been modifying the SQL statement for the query in code, using
CurrentDb.QueryDefs!qTemp.SQL=strSQL,
where strSQL is specific to the set of records I have chosen in a ListBox,
and selected by other parameters.

My question is this:
After seeing that my database contains a saved QueryDef for every instance
of a SQL statement RecordSource, I thought it would be cleanser if I used a
SQL statement (vice pointing to the saved Query object) as the RecordSource
in my report, and altered its value in code. The QueryDef is automatically
named "~sq_rMyReport," and Access doesn't complain when I change the value
in code (for example):
CurrentDb.QueryDefs!~sq_rMyReport.SQL= _
"SELECT * " & _
"FROM MyTable " & _
"WHERE RecordID IN (n, n, ... n) AND FontPitch = 10;"

but this evidently doesn't "stick" with the report, because when I open it
after running the above code, the RecordSource is still the report's
original saved SQL statement. I have tried following the code above with a
call to the "CurrentDb.QueryDefs.Refresh" method, but also no good.

If there is no way to get the value to save in the Report object, the
original method will continue to work fine. I just thought it would be
ideal to manipulate the Report object behind the scenes, and not leave a
saved Query object to tempt deletion by an unwise user.

Any insights? Your help is greatly appreciated.

Paul Johnson

 

[Duane Hookom]

I would not go messing around with undocumented or nearly undocumented
features. You can also set the record source property of the report in the
On Open event of the report.

 

[Marshall Barton]

I have been using a saved query (call it qTemp), which is the Record Source
for a report (call it MyReport).

I have been modifying the SQL statement for the query in code, using
CurrentDb.QueryDefs!qTemp.SQL=strSQL,
where strSQL is specific to the set of records I have chosen in a ListBox,
and selected by other parameters.

My question is this:
After seeing that my database contains a saved QueryDef for every instance
of a SQL statement RecordSource, I thought it would be cleanser if I used a
SQL statement (vice pointing to the saved Query object) as the RecordSource
in my report, and altered its value in code. The QueryDef is automatically
named "~sq_rMyReport," and Access doesn't complain when I change the value
in code (for example):
CurrentDb.QueryDefs!~sq_rMyReport.SQL= _
"SELECT * " & _
"FROM MyTable " & _
"WHERE RecordID IN (n, n, ... n) AND FontPitch = 10;"

but this evidently doesn't "stick" with the report, because when I open it
after running the above code, the RecordSource is still the report's
original saved SQL statement. I have tried following the code above with a
call to the "CurrentDb.QueryDefs.Refresh" method, but also no good.

If there is no way to get the value to save in the Report object, the
original method will continue to work fine. I just thought it would be
ideal to manipulate the Report object behind the scenes, and not leave a
saved Query object to tempt deletion by an unwise user.

If you want the report to "remember" the SQL that was used
the last time the report was run, then modifying your saved
query's QueryDef object is what you'll need to do. Don't
try to fool around with the system generated objects,
besides you can make the saved query a hidden object so
casual users won't see it.

If you want to set the report's query every time it is
opened, then I would do away with the saved query and use
the report's Open event to set the report's RecordSource
property to the desired SQL statement.