MS-Access Tips for Serious Users

Posted 22-Jan-96 by Allen and LeAnne Jergensen.

Reports: Snaking Column Headers

To get a snaking column header above each column but only if the column has data in it do the following:

Say you have two fields in the snaking column in the detail section of your report. ITEM and CARRYING.

  1. Create two unbound fields in the detail section and name them ITEM HEADER and CARRYING HEADER.
  2. Align them above the matching field getting just the way you want them (at the top of the detail section).
  3. Set their properties to be:
        Can grow: Yes
        Visible:  Yes
        Height:   0.0007 in.
  4. Now move the actual fields up just underneath the now very skinny headers.
  5. In the properties for the detail section add an event procedure to the On Format. In it put the following code:
        Sub Detail1_Format (Cancel As Integer, FormatCount As Integer)
            If Me.left <> dLastLeft Then
               Me![ITEM HEADER] = "Item"
               Me![CARRYING HEADER] = "Carrying"
               dLastLeft = Me.left
               sItem = Me![ITEM]
               If sItem = Me![ITEM] Then
                  Me![ITEM HEADER] = "Item"
                  Me![CARRYING HEADER] = "Carrying"
                  Me![ITEM HEADER] = ""
                  Me![CARRYING HEADER] = ""
               End If
            End If
        End Sub
  6. Set up the fields dLastLeft and sItem in any module in the general section:
        Global dLastLeft As DoubleGlobal, sItem As String
  7. Compile and save the module.

Now when you print the report if only one column of the snaking report exist you will only get one heading. But if two exist, you will get two headings.

From: Allen and LeAnne Jergensen (programmers who hate to give up!!)

Home Index of tips Top