Side-by-Side Reports in Access

A simple technique to view an old and new version of a report during development, while hiding the old (or new) version in production.

Side-by-Side Reports in Access

A couple of weeks ago, I began refactoring a report that required major revisions.  I wanted to be able to compare the old and new versions side-by-side for a variety of situations.  

Here's an excerpt of the original report:

The Real Estate Tax Certification report from TaxColl2k.

Because of the way I implemented this particular report, the changes I needed to make would require a lot of behind-the-scenes refactoring.  However, the report itself should look much like the original version.

What I wanted, then, was an easy way to display the old and new versions of the report side-by-side during development.  

I would also be releasing beta versions of the changes as I made them to a small group of test users.  I did not want the end users to see the two reports side-by-side.  Rather, I wanted them to see only the new report and to see it in full screen.

Objectives Recap

In other words, I wanted a solution that provided the following benefits:

  1. For developers, auto-resize the two reports and display them side-by-side
  2. For end users, show only the new report and show it full screen
  3. Use the exact same code to accomplish both 1 and 2 above

The Output

Here are the two views.  On the left is the developer view, showing the old and new version of the report side-by-side.  On the right is the production view, showing just the new version of the report in full screen.

The Code

Here's the code I used to implement this:

    If App.IsDev Then
        'Compare the old and new tax certs side-by-side
        '   (leave this code in place until 6/30/2022 for debugging,
        '    then delete this code and report object "01_TaxCert_previous")
        PreviewReport "01_TaxCert_previous", "ControlNumber=" & Qt(CtlNum)
        FillAccessWindow , , "NW", , 0.5
    End If
    PreviewReport "01_TaxCert", "ControlNumber=" & Qt(CtlNum)
    If App.IsDev Then FillAccessWindow , , "NE", , 0.5

NOTE: This technique violates one of my many programming axioms:

Thus, I implore you to NEVER leave old versions of code, forms, or reports in place, even if only temporarily...because you WILL forget to remove it.

The future-dated comment I added to my code is intended to account for this.  I have no doubt, though, that come 2030, that temporary comment and report will still be alive and well within my codebase.


Here's what else you need to make this work.

1. Using the registry to manage programming environments

Environmentally Friendly Access
Using the Windows Registry to manage Production, Development, and Testing environments in #VBA and #MSAccess.

2. Resizing reports to fill the available space

PreviewReport Function
This custom function is the simplest and safest way to preview reports in any Microsoft Access application.

3. Precise positioning and sizing of Access windows

Fun with Form Windows
Using my FillAccessWindow function to resize Form and Report objects relative to the amount of available space on the Access canvas.

All original code samples by Mike Wolfe are licensed under CC BY 4.0