How To Setup Multiple Detail Sections on Microsoft Access Reports?

Sometimes, when developing a report in Microsoft 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, but there is a limit user-defined sections on any report (and 200″ total height) and also a prerequisite: the rows must have a unique identifier.

If there is an Identity field or a combination of fields that serve as a 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 customers, based on the Customers Table, having the field ID as a key field, simply create a group on that field:

1. Open the report in Design View.
2. Activate the Sorting and Grouping sheet.
3. Select the field ID as Field/Expression. If you have more than one key field, select them one by one on successive rows.
4. Set Group Header to Yes (on the last field of multi-field keys). Optionally set Group Footer to 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 an option Keep Together: Whole Group on the Sorting and Grouping sheet.

If you control formatting and layout through 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 detailed 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 the basis for more convoluted advanced tricks like vertical centering.