Setup Multiple Detail Sections on Microsoft Access Reports

Sometimes, when developing a report in Access, one might wish for more than one single Detail Section. Perhaps 22 inches is just not enough, perhaps conflicting settings should be applied to different areas of the section, perhaps a section break could serve as a conditional page break.

There is a simple trick to obtain as many Detail Sections as needed. Well, there is a limit — isn’t there always? — often user-defined sections on any report (and 200″ total height). There is also a prerequisite: the rows must have a unique identifier.

If there is an “identity” field, or a combination of fields that serve as unique key, then you can create a group on that field or fields. Since the key will change for every record, the “group” will contain only a single record, and thus both the group header and the group footer will behave just like the detail section.

For example, if you are designing a complex financial report for your customers, based on the Customers Table, having the field ID as key field, you simply create a group on that field:

* Open the report in design view,
* activate the “Sorting and Grouping” sheet,
* select the field ID as “Field/Expression”,
* (if you have more than one key field, select them one by one on succesive rows),
* set “Group Header: yes” (on the last field of multi-field keys),
* optionally set “Group Footer: yes” for a total of three Detail Sections.

Tip: If you need to sort the records (e.g. by customer name or by country), the sort order must be given at the very top, before the key fields used to create multiple detail sections. That is because in “Sorting and Grouping”, the “sorting” portion cannot be turned off. Naturally, the same is true for real grouping you might need also: they must be created “before”.

When you open the properties of the sections, you can now adjust each section differently, typically: keep together, can grow and can shrink. A page break can occur between two sections that are “kept together” on a page; this implements a soft page break. Conversely, if you need the sections to always print on a single page, there is a the option “Keep Together: Whole Group” on the “Sorting and Grouping” sheet.

If you control formatting and layout though Visual Basic event handlers, it can be very useful to have distinct “on format” events for each section. A section can also be made visible or invisible with a single line of code, which is more efficient than doing the same for two dozen correlated controls.

Tip: If you need to refer to the sections through code, it is good practice to rename them, for example as secDetail1, secDetail2, and secDetail3. This will make the code easier to read and hints at the fact that they are all just like detail sections.

Theoretically, the same method can be used to obtain several group headers for the same group, by simply repeating the grouping field(s) or expression(s), but that is a rather unlikely requirement.

I have used this trick often, sometimes simply to avoid a sub-report and other times as basis for more convoluted advanced tricks like vertical centring. The day you need it, I hope you will be glad to know how to do it.

Leave a Reply

Your email address will not be published.