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)