MS Access: Printing a “Dictionary Header” on a Report

I wanted to print a report that indicated the first and last item on each page, just like a dictionary has. You know: “Azeri – Babcock”, “Milk – Minder”. It makes it easier to flip through printouts.

This is how to do it. It will put the range in the footer. I haven’t figured out how to do one in the header, which is what I originally wanted, but found too difficult to do. (There is probably a way.)

First, take your report, and add an unbound field to your report. Rename it to “Range”. See the picture below.

Then, set up event handlers for the On Print event of each section. An explanation follows the picture. Here’s my code:

Option Compare Database
Option Explicit
Public FirstRow As String
Public CurrentRow As String

' All this code fails.  I may need to work out a way to put ranges on the
' pages by running this report once to fill values, and again to
' re-populate the report with ranges.

Private Sub Detail_Print(Cancel As Integer, FormatCount As Integer)
    CurrentRow = [OrgName]
    If FirstRow = "" Then
        FirstRow = CurrentRow
    End If
End Sub

Private Sub PageFooterSection_Print(Cancel As Integer, FormatCount As Integer)
    [Range] = FirstRow & " to " & CurrentRow
End Sub

Private Sub PageHeaderSection_Print(Cancel As Integer, FormatCount As Integer)
    ' clear out the tracking variable
    FirstRow = ""
End Sub

Okay, it’s pretty simple. Every report is made up of parts, and Access has added a couple events to the different parts, so you can execute code while the report renders.

This code keeps track of the first and current values of OrgName (the field we sort and group on). When we get to the footer, the current value now holds the last value. These two values are concatenated, and then written to the [Range] field.

Putting this value at the top of the page is hard, because the top is lain out before the bottom, and I can’t figure out a way to cause the top to be reformatted before the final rendering.

Attachment	Size
Range.jpg	114.05 KB