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

 

[Access Developer]

Obviously, you will not be offering users an infinite number of choices.
How many different subreports will you actually have from which the user can
choose?

In my experience, unless you are dealing with a data-mining operation (or
certain types of "decision support"), users can determine in advance and
tell you how they actually work, and what types of data they will need
"together" to accomplish their duties.

I suspect it is possible that careful analysis of actual requiremets may
reveal that you need to give the users far fewer choices than you imply.

Larry Linson
Microsoft Office Access MVP

 

[neo]

Hi,

First of all, thank you for reading my post and for your feedback. To
answer you, regarding the database I created, I have so far 100+
subreports and probably I will have more soon as I have to extend the
coverage to other data that I must integrate next. I was thinking at
creating predefined reports that have a predefined number of
subreports but I have to renounce to all the flexibility I get from
letting the user selecting the content. Educate them to create reports
is unbearable- costly and very time consuming for me.

It must be a solution. Maybe I have to approach differently the issue
and think at an alternative way to implement my goal . For the moment
I'm stuck but hope to get out soon.
I'm looking more at a result like this here below:http://
my.advisor.com/doc/19246
The only difference is that I use subreports that have heterogeneous
data sources and RecordSource +SQL string is not appropriate for me.

Thank you again

Best regards,
ionel

 

[Access Developer]

Could you, without revealing proprietary information, give me enough
information on what you are trying to _accomplish_ ? (Not how many
subreports you have -- I think I understand that you are trying to create
something very flexible.)

But if you can give us an idea of what business function you are addressing,
maybe someone could suggest an alternate approach. I just can't think of a
situation that needs the functionality you describe to solve the business
need -- that does not mean that there is not such a situation, but without
understanding, I can't suggest an alternate approach.

Larry

 

[Tony Toews]

Not knowing your structure another option might be to create different
subreports for each child table or query. Then allow the user to
specify which subreports they want to view making them visible or not
as the case may be.

Insert all possible fields on each subreport with a width of say. 0.5
inches but mark them hidden. Then allow the user to specify what
fields they want to see on the subreports. And possibly the left to
right sequence and a field width. If they have chosen too many
fields with too much of a width warn them.

Then you make the appropriate fields visible and move them and the
titles so they are adjacent to each other in the subreports Open
event.

Alternatively allow them to export the data to a spreadsheet with each
of the subreports as a different sheet.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/

 

[Access Developer]

Neo,

I thought it best to just come back to your original post. Your research,
you said, indicated that the Source Object could not be changed in the
Format event, and I believe that to be true. You said it could not be
changed in the Report's Open event, but that is not true -- you can change
the Source Object of Subreports just for this instance of the Report, in the
Open event.

To reassure myself that I was not "mis-remembering", I created a small test
database, and was able to change the SourceObject of Subreport Controls, and
the LinkMasterFields and LinkChildFields properties of the Subreport
Controls in the Report's Open events.

Here is a copy of the OpenEvent code I used to demonstrate:

Private Sub Report_Open(Cancel As Integer)
Me.sbrFirst.SourceObject = "rptProductionFacilities"
Me.sbrSecond.SourceObject = "rptCategories"
Me.sbrSecond.LinkMasterFields = "ProductCategory"
Me.sbrSecond.LinkChildFields = "CategoryID"
End Sub

Of course, for this test, I just hard coded the changes, but you can read a
recordset or refer to global public variables in the Open event code to
accomodate your desire to read from data entered by the user, instead.

Some "tips": Note that the SourceObject property value is Text. You cannot
use the Report object itself. The LinkMasterFields and LinkChildFields are
also text values. Reports are "smart" and don't want to clutter your Record
Source, so for a Field to be available it must be the ControlSource of a
Control on the Report -- but that Control can have its visible property set
to No/False. It's helpful to name your Controls differently than the Field
object that is the Control Source -- then it's clear whether you are
referring to the Control or to the Field in your VBA code.

It is easier to work with Subforms and Subreports if you remember that there
is no such thing, that those are only verbal shorthand -- there are Subform
Controls and Subreport Controls. Subreport Controls' Source Object may be a
Report or a Form, but Subform Controls' Source Object may only be a Form.

You need to assure that you have named the Subreport Control differently
than the Report object that is its SourceObject... you'll see that I prefix
my Subreport Control names with "sbr", but the Report objects' names are
prefixed with "rpt". And, I have used the Name of the actual Fields, not
Controls in which they reside, in setting the values of LinkMasterFields and
LinkChildFields properties. That is, you'll see I used the Field Names
"ProductCategory" and "CategoryID" rather than the (not visible)
"txtProductCategory" and "txtCategoryID" Controls of which those Fields are
the Control Source.

You will have to define Subreport Controls for as many as you wish to allow
the user to display. Once you have defined them, be sure to set the CanGrow
and CanShrink properties to "Yes" for both each Subform Control and for the
Detail Section in which it resides. Then delete the values for the
SourceObject, LinkMasterFields, and LinkChildFields. With no Source Object,
it will not display, and with Can Shrink, it will not take up space. I
tested with minimal data, but believe that may execute faster than having
some datavale and the unused Subreport Controls Visible property set to
No/False.

I think this addresses the problem you were encountering, and perhaps some
of the "tips" may explain why it seemed that SourceObject could not be set
in the Report's Open event.

FYI, my test database was done in Access 2003, running under Windows XP, as
you described your environment to be.

Good luck... if you run into additional problems, post back here.

 

[neo]

Larry,

Thanks for your input. After spending to much time looking for
different solutions, I have finally used your approach to generate the
main report. I confirm that SourceObject can be changed on the onopen
event of a report. The only drawback is the fact that I had to
renounce to grouping by category the sub reports the user selects. It
is not a big loss so far as the content is there.

Thank you again for your precious help,
Best Regards,
ionel