Using Subreports to create a Main Report

[neo]

Hi,

I would like to have your opinion on the following issue. I would like
to let a user to create a main Report that includes subreports based
on his choise. These subreports are selected by the user and stored in
a table. The table includes: the id of the main Report, the name of
the main report, the id of the subreport, the name of the
subreportreport the id_category of the subreport and the category name
of the subreport. For example:
id report/report_name/id_subreport/subreport_name/id_category/category
Report1/ Monthly review/ 1/sectors/1/Performance
Report1/Monthly review/2/ assets/1/Performance
Report1/Monthly review/3/ statistics/2/Inventory

The user can add as many subreports he wants to the main report
through out a form that allows him to do these choises. The main
report must show the content of the choosen subreports by each
category. So, in this order I create a Main report based on my table
and I grouped by id_category and then by id_subreport. Then I embeded
a subreport in the detail section of my main report in order to show
the content of the subreports.

My big issue is when I try to change the source of the subreport using
SourceObject on the Detail_onformat event. I spend time readinng post
saying that the SourceObject cannot be changed on the following
events: on open and on format, but I never find any solution to
effectively manage these issue. Given the fact I have many subreports
in may database I cannot use the .Visible=False or True because is
very time consuming. Can anyone please help me with?
I'm looking for any coding idea or any other iventive way to get out
from this turn arround...

Thank you in advance

PS
I use Access 2003 and Xp

 

[Access Developer]

Create for the users a separate database, with linked tables from the main
database,
give them enough training that they can add a SubReport Control and specify
existing Reports as the SourceObject, so that they can create their own
Reports and run them. Better yet, create a Wizard to create the Reports, so
they have to know even less (perhaps no more than a non-Access-knowledgeable
user of your developed application). Wizards aren't as simple to create as
some other kinds of VBA code, but they can provide good return on your
development investment.

Each of these approaches have proved a satisfactory solution for similar
issues in client environments over the years -- in the first instance, the
users were above the intelligence level of "clerks obtained by hiring random
passersby off the street", but were not experienced Access database users,
and certainly far from what we consider "developers". In the second, while
intelligent, they were just application users -- the Wizard led them through
each step, and the steps were quite simple for them.

I suspect, given enough thought, there may be a coding solution in which you
don't have to give the users "design" permissions and that would be easier
than the "brute force" approach of coding something like a Wizard to run,
behind the scenes in design view, read the table, and create a report, which
you save and execute.

Larry Linson
Microsoft Office Access MVP