Subreports - displaying options

M

Malcolm P

Report = Client Invoice
Name of the report is Client Bill Final LOGO

The report contains the following sections (subreports):
services, alt services, adjustments, expenses, interest
and receipts.

The Services subreport contains the following columns:
date, description, atty, time, rate, charge.

Goal:

I am trying to give the user the option of what columns
are displayed in the Services subreport.
Options (Bill Formats):
1> Date, description, atty, time, rate, charge.
2> Date, description, atty, time, rate
3> Date, description, atty, time
4> Date, description, time, charge
5> Date, description, atty, charge
6> Date, description, atty
7> Date, description, time
8> Date, description, charge
9> Date, description
10> (None of the above. User will type in language to
appear.)

The Services subreport name is Child45. The Source
Object is Report.Client Bill.

What I tried to do:

I setup 10 reports to coincide with the options above:
Client Bill (option 1), Client Bill 2 (option 2), Client
Bill 3 (option 3), etc. My intent was to have a macro
attached to the Client Bill Final LOGO report Detail
section (which these subreports are in) which would change
the property Source Object to the specific reports (
Client Bill, Client Bill 2, Client Bill 3, etc) based on a
text box of Bill Formats. I received an error message
that the property Source Object could not be automated.

I then tried to pile 10 subreports (named Child45_2,
Child45_3, etc.) for each option, on top of each other and
have a macro change the Visible property to Yes or No
based on the Bill Format. The problem I ran into here is
that only one subreport would display and if not visible
it would hide the other subreport that was suppose to be
visible.

Help:
Any way to accomplish my goal?
 
L

Larry Linson

First, I have to comment that I have been in the computer business since
1958 (we weren't quite chiseling code on stone tablets, then, but close) and
have never seen users who needed that kind of report functionality -- they
were perfectly comfortable ignoring the fields they did not need. If I
understand correctly, what you want to do will just "unclutter" the page a
bit, not make it shorter.

If I determined there was really a _requirement_ for what you describe, I'd
have a control on a Form with CheckBoxes or OptionButtons for each of the
fields, which the user could check. I'd make sure that Form was Open while
the Report was executing, have a single Form embedded in a Subform, and set
the Visible property of each Control in the Form embedded in the Subform
based on the corresponding Option chosen by the user.

I, too, have used "stacked" SubformControls, but with this difference...
only one of the stack was to be displayed. Obviously, if they are overlaid,
they will "come to the top" in the same place and obscure the ones below. It
was just a bit clumsy and inconvenient during development.

It is much better to use one Subform Control, and reset the SourceObject to
the particular Report that you want embedded. If you plan to display
multiple Subforms, then they should not be set on top of each other, but
each visible in Design View. If need be, you can also set the Visible
property of the Subform Controls, and they will shrink away to nothing if
you have set the CanShrink property to Yes.

Because macros don't have error handling capabilities, I have not used them
for years except for AutoExec and AutoKeys, but I think you are unlikely to
be able to have the control you want/need using macros. I suspect to do
anything close to what you describe, you'll have to use VBA code.

Larry Linson
Microsoft Access MVP
 
S

Stew Hayles

Hi Larry,

In your answer to Malcolm you mentioned a solution using the Source Object.
Could you please give me an idea on how to implement this in code as I myself
have a report that depending on a field value will display one of two
subreports. I have tried to set the source object property but it has not
worked as yet.

Any advice you may have would be very well received,

Regards

Stewart Hayles
 
Top