Access Report Tricks

Friday Nov 21st 2003 by Danny Lesandrini

Danny Lesendrini contends that if you don't think Access is the best reporting tool around, you haven't experienced the power of writing reports in Microsoft Access. Join him as he demonstrates a few examples of what can be done with Access Reports.

I have said it before, and I will say it again; Access is the best reporting tool on the market. Anyone who thinks otherwise just has not experienced the power of writing reports in Microsoft Access. Power? Yes, and plenty of it. Follow along as I demonstrate just a few examples of things you can do with Access reports.

Simple, but Powerful

  • Standardize Report Captions
    The Report Caption is the text that shows up in the blue title bar of the window.  There is a property in the report designer that allows you to set this value, but all too often, I forget to do it.  Therefore, it usually reads something like "Download_RPT_new_10-22-03," which is pretty ugly.  You can standardize report captions using some simple code.  Here's what you do:

       Add a label to your report named, for example, lblTitle
       Add this code to the report's module:

        Private Sub Report_Open(Cancel As Integer)
    ' Me refers to the container, the report itself.
            Me.Caption = Me.lblTitle.Caption
        End Sub

  • Standardize Report Headers
    Avoid having to modify dozens of reports when header information changes. Create a simple report with all the header information and drag-n-drop it in the Report Header of each of your reports. That way, if something changes, you only have to edit one report object, the header subform, and the changes propagate to all your reports.

    There is, however, a caveat: You MUST place your header controls in the subform's Report Header section. Since the subreport contains no data, the Page Header will never display, so controls there are ignored.

  • Standardize Report Footers
    Hey, footers have to be as easy as headers, right? Well, yes, they are, so long as you do not insert a Page X of Y control into it. To use paging with subforms, you need to understand how Access handles report pages. Let's begin by placing this code in the text box's ControlSource property:

      ="Page " & [Page] & " of " & [Pages]

    The problem is, again the subform has no pages, so it will always display 0 of 0. What you really want is to reference the page count of the report that contains the subform, its "Parent." So, add the word "Parent" to the code, like this:

      ="Page " & [Parent].[Page] & " of " & [Parent].[Pages]

    If you try this out, you'll see there is still a problem. Now the control displays 1 of 0. The subform does not know how many pages there are in the report because the Parent form doesn't know. In fact, Access reports never know how many pages they have, until you force them to calculate it. How do you do that? You insert a control that calls the [Pages] method, as we did in the subform.

    In other words, the [Pages] method forces the report to secretly format itself in hidden mode to calculate the total number of pages. Then, it returns to the beginning with this value safely stored in the [Pages] property and displays it as requested. So, in order to get the [Parent].[Pages] method to work in the subform, there must be one placed on the parent form. I know, it rather defeats the purpose, but that is how it works.

  • Draw a Box Around the Page
    Recently I was asked to draw a box around the entire page of a report. I must admit, I had to search Google for the code, but it is quite simple. Since you want to modify the page, it makes sense to put this code in the Report_Page() event.

      Private Sub Report_Page()
           On Error Resume Next
             ' Set Thickness and Border Style
             Me.DrawWidth = 6   ' larger number, thicker line
             Me.DrawStyle = 0   ' 0 to 6 = solid to invisible
             ' object.Line (x1, y1) - (x2,y2), color, [Box=B]
             Me.Line (0, 0)-(Me.ScaleWidth, Me.ScaleHeight), vbGreen, B
           End Sub

  • Toggling Row or Control Backcolors
    Another frequent request by users is to have report rows alternate backcolor, so they are easier to read. There are two ways to do this: change the detail section color or change the background color for each individual control in the row. Changing the detail secion color is less processing, but it is also easy to manipulate controls as a group. Consider this code, which demonstrates both methods:

           Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
           On Error Resume Next
             Const cYellow As Long = 10092543
             Const cwhite As Long = 16777215
             Const cPurple As Long = 16751052
             Dim ctl As Control
             Dim sec As Section
             Set sec = Me.Section("Detail")
             If sec.BackColor = cwhite Then 
                sec.BackColor = cYellow 
                sec.BackColor = cwhite
             End If
             For Each ctl In sec.Controls
                If ctl.BackColor = cYellow Then
                   ctl.BackColor = cwhite
                   ctl.BackColor = cYellow
                End If
           End Sub 

    Play with the colors a little, using Purple for the controls instead of Yellow and you'll see what I mean.  It creates some very ugly reports, if you are not careful, but it is very powerful for those with a creative, right-brained programmer's eye.  Click the link below to see the output for the above report tricks.

Report Trick Example Report (Requires the Snapshot Viewer)

Filtering and Grouping

Eventually, you are going to need to be able to dynamically change the content or format of the data contained in reports.  While your needs may vary, here are some common methods for accomplishing simple filtering and grouping.


The simplest way to filter for a single parameter is to put a criteria expression in the query, like this:

    SELECT * FROM tblDownloads WHERE tblDownloads.Type = [Enter Type of Download] 

The problem with this approach is that your users need to know what "types" are available. Wouldn't it be nicer to offer them a drop-down list of options? You can easily do this by creating a form as shown in the image below, but then your query must be modified to reference this control value, as follows:

  SELECT * FROM tblDownloads WHERE tblDownloads.Type = Forms!frmFilter!cboType 
Criteria collection form

This form is included in the download for this article, so you can examine it yourself, but it has a combo box that looks up valid Type values so that users can simply select a valid value. It stores that value in the control named "cboType".

On the other hand, how do you get this form to "pop up" at the right moment? Again, there are various ways to accomplish that, but here's a very simple approach:  Load the form in the Report's Report_Open() event. The trick necessary to make this process work is that you open the form in Dialog mode.

In Dialog mode, the form halts all other processing until it is either closed or hidden. In our case, we cannot close it or we will lose the value of cboType, so the OK button simply hides the form, which allows the report to continue opening. As it does, its RecordSource, our query described above, is filtered for the selected type. The code is simple, and looks like this:

  Private Sub Report_Open(Cancel As Integer)
       On Error Resume Next)
         DoCmd.OpenForm "frmFilter", , , , , acDialog)
       End Sub


If you want to filter more than one field, then simply add combo boxes to the filter form and additional parameter criteria to the query. You can even pass sorting information, as shown in the image below of a much more complicated filter criteria form. The code behind this example is too complicated to describe here, but you may examine it yourself by downloading the Report Filter Example code file.

Report filter example


One last trick: Dynamic Grouping. The idea here is to create a report with detail records and group totals but determine at run-time whether or not you want to display the detail lines or not.

We use the same method as above, but instead of altering a query, we add code to the Report_Open() event to show or hide the detail section. In the example below, I have renamed the detail section to Detail_001 (by default, it is named "Detail"). This is just to illustrate that each report section is really just a control, whose properties you can modify. Notice the code required to hide the detail section:

Criteria collection form

        Private Sub Report_Open(Cancel As Integer)
       On Error Resume Next

         DoCmd.OpenForm "frmOption", , , , , acDialog

         blnHideDetail = CBool(Nz(Forms!frmOption.cboType, False))

         If blnHideDetail Then
           GroupFooter1.Height = 315
           Detail_001.Visible = False
         End If

       End Sub


So, based on the user's selection, the detail section is either left untouched (visible) or is hidden. The result is a report with group totals only.  You now have effectively delivered two reports to your users:  one with detail records and one without.  I've saved the report output as snapshot files, which you can examine for yourself:

Detail-Summary Example with detail records (Requires the Snapshot Viewer)
Detail-Summary Example without details (Requires the Snapshot Viewer)


There is just so very much you can do with Access reports. What I have shown here only scratches the surface and your current needs may vary, but the ideas described in this article and the associated code will get you started. The rest is limited only by your imagination. All the code for this article is available below. Download it, play around with it, and soon you will come up with some tricks of your own.

Report Tricks Code
Report Filter Example

» See All Articles by Columnist Danny J. Lesandrini

Mobile Site | Full Site