Dynamically create master report referencing pre-built subreports

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have been tasked with trying to find a way to programmatically add
subreports to a master report at runtime based on certain criteria
(subreports are already created).

Initially, we have been adding all the possible reports and hiding them if
there isn't any data. However, we are getting up to 1500 reports (due to our
industry, one report may vary for every state) and this is becoming nearly
impossible.

Is there a way to programmatically add reports to a master report (thus
making them subreports) at runtime? Are we stuck having to add them all like
we have been doing?

I have been researching this profusely and haven't been able to find much
information. Any help would be greatly appreciated.

Developing in Access 2003 as we evaluate Access 2007.

Adam
 
Adam Edward said:
I have been tasked with trying to find a way to programmatically add
subreports to a master report at runtime based on certain criteria
(subreports are already created).

Initially, we have been adding all the possible reports and hiding them if
there isn't any data. However, we are getting up to 1500 reports (due to our
industry, one report may vary for every state) and this is becoming nearly
impossible.

Is there a way to programmatically add reports to a master report (thus
making them subreports) at runtime?
Click to expand...


Not really.

However, if you only need a limited number of subreports in
any one printout, then you can have a sufficient number of
subreport controls on the main report and set their
SourceObject property in the main report's Open event.
 
Okay, to make it more challenging. So, we know we will have a max number of
reports ever possible - let's say 150. I have created a report with 150 blank
sub report controls. When trying to set the RecordSource through VBA it
doesn't seem to do anything.

One of the many ideas I've tried:
Me.subreport1.RecordSource = ...


Am I on the right track?

Thank you,
Adam
 
Adam said:
Okay, to make it more challenging. So, we know we will have a max number of
reports ever possible - let's say 150. I have created a report with 150 blank
sub report controls. When trying to set the RecordSource through VBA it
doesn't seem to do anything.

One of the many ideas I've tried:
Me.subreport1.RecordSource = ...
Click to expand...


Sounds like you took a wrong turn somewhere and are lost in
a jungle ;-)

Are you saying that you can have 150 subreports appearing in
a single printout? That is an incredibly complex situation,
which implies there is a conceptual error somewhere in your
design. Are you using separate subreports to deal with
different criteria???

I was assuming that you would only need a half dozen or so
subreports in any one printout. Based on that assumption
you would only put 10 or 12 subreport **controls** on the
main report. Then the main report's Open event would use
code to determine which 6 report objects out of your
possible 150 needed to be displayed as a subreport and set
some of the subreport control's SOURCE OBJECT property to
the **name** of a report object.
 
We've gotten the total number of subreport maximum to about a 100. We'll have
to look at this more closely to see if we can reduce it some more.

Do you have an example of how to set a report objects source on the open
event?

I appreciate your continued help beyond what I can express in words!

Adam
 
An example? It's about as easy as setting the color of a
text box:

If condition1 Then
Me.subreportcontrol1.SourceObject = "report1"
ElseIf condition2 Then
Me.subreportcontrol2.SourceObject = "report2"
. . .

I can't be very specific since I have no idea what
conditions you are using to determine which reports should
be displayed.

There may be other approaches to your report design, but I
can't even guess what might help until you explain why/what
you are doing with so many subreports. I suspect that at
least most of them are there only to filter the records in
different ways and, if that's the case, there are several
alternatives to using a separate report for each set of
criteria (e.g. Link Master/Child properties, parameterized
record source queries and setting RecordSource on open).
 
I have done such a poor job explaining he situation and its time to show all
the cards.

We deal with one type of contract. Each contract has, on average, 50
sections but could possible have around 100 sections total (depending on what
the client is paying for). Each section is its own subreport with various
variables. This gets even more complicated because a section (subreport)
could vary by state. So, section 2 has 10 state variations (California,
Washington, Montana,... etc.). We are trying to find a way to bring this
contract together with the appropriate sections into one report. We looked at
having just one section report and the state variations be inside the report
and show/hide based on a state variable, but it became so layered it was too
much.

Does this help? Any thoughts on how to progress?

Thank you, again, for your help,
Adam
 
Adam said:
I have done such a poor job explaining he situation and its time to show all
the cards.

We deal with one type of contract. Each contract has, on average, 50
sections but could possible have around 100 sections total (depending on what
the client is paying for). Each section is its own subreport with various
variables. This gets even more complicated because a section (subreport)
could vary by state. So, section 2 has 10 state variations (California,
Washington, Montana,... etc.). We are trying to find a way to bring this
contract together with the appropriate sections into one report. We looked at
having just one section report and the state variations be inside the report
and show/hide based on a state variable, but it became so layered it was too
much.

Does this help? Any thoughts on how to progress?
Click to expand...


Let's call the pieces of a contract a part so I don't
confuse them with report sections.

Is you main report bound to any data? If it is, how does
that data relate to the parts of a contracts.

And how are you determining which parts of a contract should
be included?

How do you go about varying a part of the for a specific
state?

How are the parts of a contract stored in tables?

I suspect there may be some clever way to use the Link
Master Child properties to help simplify a lot of this.
 
It doesn't seem you can access the source object property of the report
object from the code. How do you set this dynamically?

Answers:
- Main report is not bound.
-We have a table that stores which parts need to go into the contract
-The parts needed are a result of users' choices on a form
-Regarding variations for state variations: each part has separate reports
for each state variation
-Data for contracts are stored in a table, and the variables for each part
are also stored in a separate table.

Is this enough information?

Thank you Marsh,
Adam
 
After some more attempts:

Is the Source Object property of an unbound sub report available only in
certain events? Obviously it can be set in the Design View, but the
SourceObject property is not available in VBA. I tried:

Me.Rpt_1.SourceObject = "Report.rpt_1"


Where Rpt_1 is the name of a sub report and rpt_1 is a report object
available in the application.

Thanks for your help,
Adam
 
I found that although the SourceObject property of the Report does not appear
in the Intellisense window, it is able to be set in the code. I only tested
this in the Report_Open event and perhaps the behavior would vary in
different Events.

Adam
 
Adam said:
I found that although the SourceObject property of the Report does not appear
in the Intellisense window, it is able to be set in the code. I only tested
this in the Report_Open event and perhaps the behavior would vary in
different Events.
Click to expand...


Right, the main report's Open event is the only place you
can do that.

Are you declining to answer my previous questions and
explore potential alternative designs?
 
No sir. I am interested in alternatives. Maybe my first post with the answers
didn't show:

Answers:
- Main report is not bound.
-We have a table that stores which parts need to go into the contract
-The parts needed are a result of users' choices on a form
-Regarding variations for state variations: each part has separate reports
for each state variation
-Data for contracts are stored in a table, and the variables for each part
are also stored in a separate table.

Is this enough information?

Thanks!
Adam
 
Adam said:
No sir. I am interested in alternatives. Maybe my first post with the answers
didn't show:

Answers:
- Main report is not bound.
-We have a table that stores which parts need to go into the contract
-The parts needed are a result of users' choices on a form
-Regarding variations for state variations: each part has separate reports
for each state variation
-Data for contracts are stored in a table, and the variables for each part
are also stored in a separate table.
Click to expand...


Sorry, that post did come through, I just missed it among
the other ones.

If the records in the parts table are identified by a couple
of values, then my thought was to use another table (bound
to the form??) with a list of the sections, etc for a
contract. Then bind this other table to the main report and
use the Link Master/Child properties to select the part
record to display in a single subreport in the detail
section.

If this other table is too difficult to manage, you might be
able to use code in the unbound main report's detail
section's Format event to check the form and set the Link
Master text box values along with setting the NextRecord
property to False.

I'm probably just stirring the pot without knowing enough to
be helpful, but it might be worth thinking about as a way to
avoid having 100+ subreport controls in the main report.
 
Back
Top