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]
           Else
              If sItem = Me![ITEM] Then
                 Me![ITEM HEADER] = "Item"
                 Me![CARRYING HEADER] = "Carrying"
              Else
                 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!!)