Re: Dynamic grouping in reports?

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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.

Grateful!

Phil
.

Relevant Pages

  • Re: Dynamic grouping in reports?
    ... So, I've put a check-box on the form which launches the Invoice report, ... One problem is that the group headers and footer need controls, ...
    (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)
  • Re: Sum Total in Text box of Report gives #Error
    ... Putting it in the report footer works fine.... ... >You can't Sumcontrols and aggregates don't work in page footers and ... >headers. ...
    (microsoft.public.access.reports)
  • RE: unexpected page break
    ... letter, with report header, page header, ... detail section, page footer, and report footer (for ... The headers and footers display and print basically the same ... dozens of text and combo boxes, mixed for 40-50 subreports. ...
    (microsoft.public.access.reports)
  • Re: Ending page numbers
    ... pages that controls whether headers and footers are linked to those in the ... The first section is about 56 pages with page numbering. ... If I get rid of the page numbering in the footer in section 2 ...
    (microsoft.public.word.docmanagement)