Access Report Tricks

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 
             Else 
                sec.BackColor = cwhite
             End If
    
             For Each ctl In sec.Controls
                If ctl.BackColor = cYellow Then
                   ctl.BackColor = cwhite
                Else
                   ctl.BackColor = cYellow
                End If
             Next
    
           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)

Danny Lesandrini
Danny Lesandrini
Danny J. Lesandrini currently works as the IT Director for Pharmatech Oncology Inc. at http://www.pharmatechoncology.com/. He holds Microsoft Certifications in Access, Visual Basic and SQL Server and has been programming with Microsoft development tools since 1995.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles