Access Cookbook

Customize a Report’s Grouping and Sorting at Runtime

Problem

You have a report that has several different grouping and sorting fields that you need to rearrange every time you run the report. To do this, you’ve created five or six different versions of the same report, changing only the order of the fields and which fields are sorted or grouped. This is a maintenance nightmare, especially when you want to change some aspect of the report, which means having to change all the variants of this same report. Is there any easier way to do this in Access?

Solution

You can manipulate most aspects of a report’s design using VBA code. This solution shows you how to programmatically open a report in design mode and manipulate several properties of controls and groups. Using this technique and a driving form, you can create a single report that can be customized using different sorting and grouping fields every time it is run.

Load 03-15.MDB and open frm_rptCompaniesSetup, which is shown in Figure 3-36.

Figure 3-36. The frm_rptCompaniesSetup form is used to set up the rptCompanies report

Select a grouping field and zero, one, two, or three other fields for the report (any or all of which can be sorted). When you’re done, press the Preview or Print button and a report matching the chosen sorting/grouping fields will be previewed or printed for you. A sample report using the settings from Figure ...