Re: Dynamic grouping in reports?

Tech-Archive recommends: Fix windows errors by optimizing your registry

Philip Herlihy wrote:

Marshall Barton wrote:
Philip Herlihy wrote:

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)


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.

--
Marsh
MVP [MS Access]
.

Relevant Pages

  • problem with report footer
    ... the last page the report footer info is printed directly under it. ... I would like the report footer to be always at the BOTTOM of the last page ... Set each of these controls Visible properties to No. ...
    (microsoft.public.access.reports)
  • Re: problem with report footer
    ... Basically it works out how big the report footer ... then "shuffles" all the controls down to the bottom of the footer. ... you can get really clever with this and have some controls stay ...
    (microsoft.public.access.reports)
  • Re: using a footer total in details section
    ... getting the correct numbers in the report for the part footer section. ... > Take a look at the report's record source table/query, ... > unaware of controls on the report. ...
    (microsoft.public.access.reports)
  • Re: How do I keep individual controls or groups together in report
    ... you don't have any grouping in your report yet ... You can then split your controls across the group footer and ... using another constant expression to get a third ...
    (microsoft.public.access.reports)
  • Re: Dynamic grouping in reports?
    ... 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. ... One problem is that the group headers and footer need controls, ...
    (microsoft.public.access.reports)