Set subreport Source at runtime?

[SteveH]

How can change which report is used as the SourceObject
of a subreport, based on a criteria field selected on a
form (before the report is run)? Doc says that
SourceObject cannot be changed at runtime. ?Maybe I can
change the design of the report via code before the
report is run? All ideas appreciated.

SteveH

 

[Larry Linson]

It appears to me that the SourceObject of a Subreport Control can, in fact,
be changed in the Report's Open Event -- I just did so, in Access 97, and
while I haven't tried it in later versions, I suspect it will work there
also.

Larry Linson

 

[SteveH]

Wha-da-ya-know ... it does seem to work now ... must have
been doing something else wrong before.

BTW, just checking my sanity, the Access 2002 doc
says: "Note You can't set or change the SourceObject
property in the Open or Format events of a report."

Thanks much ... SteveH

 

[Larry Linson]

Well, should they actually enforce that rule in a later release of Access,
you can always create both subreports, overlay them, and set the visible
property to show the one you want. I don't know why it should be a problem
for them to let you do it in the Open event -- you can even change the
RecordSource of the main report in the Open event. Maybe that was what I was
remembering when I tried it... I admit that I didn't check Help first, and I
tried it in Access 97.

Resetting the Source Object is more efficient, as you don't have two Forms
in Subform Controls being repopulated / filtered / processed with every main
form record.

Larry Linson
Microsoft Access MVP