Creating/removing grouping levels in VBA

[Chad1984]

Okay. I'll admit that I feel a little stupid after reading the help on CreateGroupLevel, GroupOn, etc, playing around with it, and I'm still not able to get it to work.

Basically, what I want to do is have a form that allows the user the option of picking which counties to include the report (which is grouped by counties). That's no problem. But I ALSO want the option of not grouping by counties at all. My guess is to include some code in the Report_Open method that would either (a) add a grouping level if conditions on the form were met or (b) remove a grouping level if conditions on the form were not met. How do I do this?

As I write, it occurs to me that a reasonable alternative may be to provide totals at the bottom of the grouped report. The problem with this is that what I want would be sums for the details, not the groups. Let's say that my detail field is Service. It just so happens that the services pulled in by this query happen to be Service=Food and Service=Clothing, let's say. I don't want a total for the Service field, that would mash all the detail into a useless lump; I'd want the report to automatically have a sum for every distinct type of detail record, if that makes any sense.

 

[Remou]

You can set the visibility of the GroupHeader and GroupFooter and change the ControlSource:

DoCmd.OpenReport "rptReport", acViewDesign
Reports(0).GroupHeader0.Visible = False
Reports(0).GroupFooter1.Visible = False
Reports(0).GroupLevel(0).ControlSource = "SomeFieldNotCounty"

DoCmd.OpenReport "rptReport", acViewPreview

You can also add groups programmatically and hide and show the Detail section.

 

[Chad1984]

Thanks... that doesn't quite seem to work, though. Since it refers to Reports(0) instead of an actual name, that can cause bugs if any other reports are open. That's easy enough to fix, but then I ran into other issues- even when the grouping level is removing, the data is still grouped, which isn't what I want; and it doesn't hide the column label... or vice versa, if I create a grouping level, it doesn't create a column label, and I don't think I can create/hide one from outside the report. I could add code inside the report to try to detect the grouping level, but that would overly complicate it and make it even harder for anyone else to make new forms (although I think it will be complicated in any event). In addition, the code just continually adds a grouping level every time it runs, so I'd need something that would detect/remove grouping levels as well.... of course, I'm probably approaching it from an entirely bad angle!

As I think more on it, perhaps sums at the bottom of the report would be better. The problem is, I don't want a sum of all records in the report, or a sum of all the records in each county (remember, it's grouped by county). The detail section automatically has a different line for each service; i'd like a totals section with a total for each service across all the counties. How could that be done? It seems to me that either a pile of calculated fields would be created that say "SELECT COUNT(*) FROM MyQuery WHERE Service_Name = 'blah'", and that would be rather inflexible. The other option is to make a subreport on a similar query. Is there a better way to do this?

 

[dhookom]

You could add as many group levels as will ever be needed. Then add code in the On Open event of the report to change the Control Source properties as needed.

Additional code in the On Format event of group headers or footers can cancel the event to basically hide the sections.

I'm not sure what else you are looking for however you may want to use a subreport based on a totals query in your report footer.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]