Report Builder

Advanced Report Builder

Announcing a new cookbook-style series of advanced articles focused on creating a custom report builder in Microsoft Access.

Mike Wolfe

Oct 18, 2023 • 3 min read

One of the best features of Access is its report designer.

In my opinion, this is the feature that really sets Access apart from most other desktop application development environments. It's a big part of the reason why Access excels as a rapid application development (RAD) tool.

The built-in report designer allows developers and power users to quickly build new reports as business requirements evolve. Many in-house-developed Access applications leave this feature exposed, allowing power users to create new report objects to satisfy their needs.

However, allowing end users to create and edit Access report objects is a bad idea for a professionally-designed Access application.

The Need for a Custom Report Builder

All of the Access applications my team builds get run in Runtime mode, even if the end user has a full version of Access on their machine.

This locks them out of all the developer design tools which keeps them from hurting themselves (especially those users who know just enough to be dangerous). That does not change the fact that they may still need to create (and save) their own ad hoc reports from time to time.

One way to solve this need is with a custom report builder.

Our Take on a Custom Report Builder

Over the years, many Access developers have built custom report builders of widely varying complexity and feature sets.

In this series, I will be doing a deep dive into the most feature-rich custom report builder that we've created. The report builder includes several advanced features, including:

A single form to create, modify, save, filter, preview, and export custom reports
Available report controls are stored in a local table
Saved report templates are stored in a combination of local and linked tables
New report objects are created on the fly in VBA
Report columns are auto-sized based on the actual data included on the report
Reports may be run for current or historical data
Report data is saved to temporary tables
The temporary tables are created in temporary back-end files to avoid front-end bloat
Data is cached between report runs to improve performance
The cache is automatically invalidated when any user changes underlying data
Filtering criteria is displayed in plain English on the report header
An unlimited number of columns can be added to the report (the report automatically wraps to additional lines if needed)
Output page size and orientation can be customized and saved
Font size can be customized and saved
Calculated columns can bring in data from dozens of custom queries
A bit mask flag is used to identify and run only required custom queries
Users can save custom reports for later use
Users can run other users' saved custom reports
Developers can easily create "built-in" custom reports
Existing custom reports can be duplicated as a base for a new report
Any custom report can be exported to Excel
Reports may be grouped up to the max number of Access grouping levels
Reports may be sorted up to the max number of Access sorting levels
Group footers will display summary data for appropriate columns

And many more features that I'm not remembering at the moment...

A Report Builder Cookbook

I don't expect anyone to re-implement this entire design in their own projects.

In fact, we've never re-implemented this entire design in any of our other projects.

However, the design includes many advanced topics that would be useful on their own. With that in mind, the series will take more of a cookbook approach, with individual articles addressing specific features and challenges that I had to overcome while creating this report builder.

The eventual goal is to have a linked article for every feature in the bullet list above.

Throwing a Bone to the Advanced Readers

Many of my recent articles have been geared towards beginner developers, as I've been fleshing out my Access 101 series.

As I said in the intro to that series,

I won't suddenly start writing nothing but beginner-level articles; I would get bored with that myself. Instead, expect to see these articles sprinkled in among the usual advanced topics.

If you've been missing the advanced topics, this series is for you. Whereas the Access 101 series is aimed at beginners, the articles in this series will be at the opposite end of that spectrum.

Hold on to your hats.