Access 2007 - Dynamically change the datasource of a report

[Daniel Jacobs]

Is it possible to dynamically change a report's datasource (from one query to
another) based on the value of a user entered parameter?

 

[Jeff Boyce]

Daniel

For that to work, the report would need to have its controls connected to
the dataset fields. Do your queries have the same fields?

Short answer, yes. Longer answer, what are you trying to accomplish? Maybe
folks here can offer an alternative approach, if we knew what you were
trying to do...

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Daniel Jacobs]

I find that I am constantly creating new copies of reports that are based on
queries with subtle differences such as a different subset of the data. This
creates a big maintenance overhead (if the basic report changes, I have to
change each of the report clones).

So. I can either continue to define the same report several times using
different queries (filters), create a horrendously complicated query with
multiple parameter driven filters or (this is the point of the post) create a
report that would change the datasource according to a single user entered
parameter (say).

 

[Larry Linson]

Is it possible to dynamically change a report's datasource
(from one query to another) based on the value of a user
entered parameter?

You can change a report's Record Source in the Open Event of the Report. You
might have passed that string, or a reference to it, in OpenArgs in the most
recent three versions of Access, or you might launch the Report from a Form
with DoCmd.OpenReport, and pick up the string for the Record source by
referring to the Form.

On the other hand, what you describe elsewhere often lends itself very well
to use of the Filter, or WhereCondition arguments of the DoCmd.OpenReport.
However, you should be aware that those arguments both result in a "filter"
being used on the Report, and, for very large volumes of data, replacing the
Record Source may be more efficient, especially if you are linked to backend
tables in a server database.

Larry Linson
Microsoft Office Access MVP