Dynamic grouping in reports?

[Philip Herlihy]

My home-baked billing system generates invoices which are grouped by
date (month), with a text box in the group footer showing the Sum of
charges for each month.

One customer has now asked for invoices grouped by office location instead.

So, I've put a check-box on the form which launches the Invoice report,
and if the box is checked it launches a different report - different
only in that it has different subreports, as it's the subreports which
have the grouping. This all works; customer happy.

The problem with this is that I now have six reports to maintain (if
anything changes) rather than three. I did look for a way of EITHER
changing the grouping via VBA, OR switching the subreports using VBA.
One problem is that the group headers and footer need controls, and they
are different controls. I guess I could include all the controls and
make only the relevant ones visible? Maybe I could figure out a way of
doing without the header/footer controls, leaving only a need to switch
the grouping basis? Is that possible?

I did find code on the web which could select subreports dynamically:

http://www.accessmonster.com/Uwe/Forum.aspx/access-reports/8605/
Any-way-to-control-sub-reports#uUqsZuPQEHA3988tk2msftngp13phxgbl

... but that involves getting Access to change the form design in Design
View. "Live code" - eeek!

Is there a better way to achieve what I want - a report with a
group-based layout which is determined by run-time options? Or am I
just trying to be too neat?

Phil, London

 

[Marshall Barton]

My home-baked billing system generates invoices which are grouped by
date (month), with a text box in the group footer showing the Sum of
charges for each month.

One customer has now asked for invoices grouped by office location instead.

So, I've put a check-box on the form which launches the Invoice report,
and if the box is checked it launches a different report - different
only in that it has different subreports, as it's the subreports which
have the grouping. This all works; customer happy.

The problem with this is that I now have six reports to maintain (if
anything changes) rather than three. I did look for a way of EITHER
changing the grouping via VBA, OR switching the subreports using VBA.
One problem is that the group headers and footer need controls, and they
are different controls. I guess I could include all the controls and
make only the relevant ones visible? Maybe I could figure out a way of
doing without the header/footer controls, leaving only a need to switch
the grouping basis? Is that possible?

I did find code on the web which could select subreports dynamically:

http://www.accessmonster.com/Uwe/Forum.aspx/access-reports/8605/
Any-way-to-control-sub-reports#uUqsZuPQEHA3988tk2msftngp13phxgbl

.. but that involves getting Access to change the form design in Design
View. "Live code" - eeek!

eeek indeed.

A report can change (not add or delete) existing group level
properties in its own Open event. E.g.

Me.GroupLevel(n).ControlSource = "field name"
or
Me.GroupLevel(n).ControlSource = "=some expression"

Then, you will either want to make some of the controls
visible/invisible and/or change their control source.

Speaking of grouping on an expression. I the header/footers
do not need to be changed, you could try just grouping on an
expression like:
=IIf(Forms!theform.thecheckbox, thisfield, that field)

The main report can change its subreports by using code like
this in its Open event:

Me.subreportcontrol.SourceObject = "name of form object"

 

[Philip Herlihy]

eeek indeed.

A report can change (not add or delete) existing group level
properties in its own Open event. E.g.

Me.GroupLevel(n).ControlSource = "field name"
or
Me.GroupLevel(n).ControlSource = "=some expression"

Then, you will either want to make some of the controls
visible/invisible and/or change their control source.

Speaking of grouping on an expression. I the header/footers
do not need to be changed, you could try just grouping on an
expression like:
=IIf(Forms!theform.thecheckbox, thisfield, that field)

....

Got it - all makes perfect sense. Thanks - much appreciated! I think
that would be the way to do it: have a textbox and label in header and
footer and change their properties in code at the same time as setting
the ControlSource of the GroupLevel.

On the SourceObject property, I did find a statement in the Help (Access
2003) that this can't be set or changed in the form's Open or Format
event handlers, or I'd have tried that, but the method you suggest is
clearly far better.

Grateful!

Phil

 

[Marshall Barton]

Got it - all makes perfect sense. Thanks - much appreciated! I think
that would be the way to do it: have a textbox and label in header and
footer and change their properties in code at the same time as setting
the ControlSource of the GroupLevel.

On the SourceObject property, I did find a statement in the Help (Access
2003) that this can't be set or changed in the form's Open or Format
event handlers, or I'd have tried that, but the method you suggest is
clearly far better.

I hate to disillusion you, but Help is not perfect ;-)

IME, for reports, the Open event is the only place where it
can be set.

 

[Philip Herlihy]

I hate to disillusion you, but Help is not perfect ;-)

IME, for reports, the Open event is the only place where it
can be set.

Either way, what you suggested is the best solution and I'll be more
than happy with that!

Phil