Record source change through VBA

[Young H. Lee]

Based on the record selection criteria entered by a user
(by city, by community, by zip code, or for all), a set of
reports have to be generated. Each report has many
supreports. One solution is to create seperate queries
matching to the various record selection criteria, and
create reports/subreports having those queries as record
sources.

Problem with this approach is the proliferation of queries
and reports/subreports that would increase maintenance
costs.

Please show me how to change the record sources of reports
and subreports dynamically, i.e. through an event
procedure activated upon click a button in the record
selection criteria entry form.

Queries are the tools to generate SQL statements, and I
know that the latter can be generated through a VBA. So
the question boils down to the following:

(1) Is it possible to change the record source of a report
dynamically?
(2) If so, how?

Thank you for your attention.

 

[Guest]

You can change the recordsource of a report in the Open event. You could
load public string variables associated with your various
reports/sub-reports with sql strings before opening and have the Open event.
You may have some problems with Master/Child linking though. You'd have to
test.

Another technique is to store one query for each report (or sub-report) in
your database. Base your report on the stored querydef, then update the
querydefs before you open the reports.

Steve