Skip to Content

[Solved] 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.

[Solved] How To Setup Multiple Detail Sections on Microsoft Access Reports

[Solved] How To Setup Multiple Detail Sections on Microsoft Access Reports

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:

Step 1: Open the report in Design View.

Step 2: Activate the Sorting and Grouping sheet.

Step 3: Select the field ID as Field/Expression. If you have more than one key field, select them one by one on successive rows.

Step 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.

Alex Lim is a certified IT Technical Support Architect with over 15 years of experience in designing, implementing, and troubleshooting complex IT systems and networks. He has worked for leading IT companies, such as Microsoft, IBM, and Cisco, providing technical support and solutions to clients across various industries and sectors. Alex has a bachelor’s degree in computer science from the National University of Singapore and a master’s degree in information security from the Massachusetts Institute of Technology. He is also the author of several best-selling books on IT technical support, such as The IT Technical Support Handbook and Troubleshooting IT Systems and Networks. Alex lives in Bandar, Johore, Malaysia with his wife and two chilrdren. You can reach him at [email protected] or follow him on Website | Twitter | Facebook

    Ads Blocker Image Powered by Code Help Pro

    Your Support Matters...

    We run an independent site that is committed to delivering valuable content, but it comes with its challenges. Many of our readers use ad blockers, causing our advertising revenue to decline. Unlike some websites, we have not implemented paywalls to restrict access. Your support can make a significant difference. If you find this website useful and choose to support us, it would greatly secure our future. We appreciate your help. If you are currently using an ad blocker, please consider disabling it for our site. Thank you for your understanding and support.