You have an Amount field, and want to display the Amount total for that page in the Page Footer. Totals are not normally available in the Page Footer, but the task requires just four lines of code!

1. In the PageHeader's Format event procedure, add:

      curTotal = 0 'Reset the sum to zero each new Page.
   

2. In the DetailSection's Print event, add:

      If PrintCount = 1 Then curTotal = curTotal + Me![Amount]
   

3. Place an unbound control called PageTotal in the Page Footer. In the PageFooter's Format, add:

      Me![PageTotal] = curTotal
   

4. In the Code Window under Declarations enter:

      Option Explicit 'Optional, but recommended for every module.
      Dim curTotal As Currency  'Variable to sum [Amount] over a Page.

Extensions:

For a progressive total that accumulates over all pages, move step 1 into the Report Header. (Note: Failing to reset the variable, will give you double the amount if the user Previews the report and then prints it.)

If your progressive total requires a "Balance C/FWD" at the top of the next page, provide an unbound control in the Page Header called BalanceFWD, and in the PageHeader's Format event add:

   Me![BalanceFWD] = curTotal
   Me![BalanceFWD].Visible = (Page <> 1)    ' Hide for Page 1.

Access will not allow you to sum a calculated control on a report; the calculation must become a field in the underlying query. In some circumstances, summing the value into a variable in the Print event may be more convenient.

• MoveLayout: if False, prints on top of what was printed last;
• NextRecord: if False, prints the same record again;
• PrintSection: if False, doesn't print any data.

Each is normally set to True, but the combination of the three allows fine control over what is printed when and where. For example, a report's readability might be enhanced by a blank line every five records.

1. In the report's Declarations enter:

      Option Explicit
      Dim fBlankNext As Integer  ' Flag: print next line blank? (True/False)
      Dim intLine As Integer     ' A line counter.
   

2. Select the Page Header section, and enter this in the OnFormat event procedure:

      intLine = 0                ' Reset line counter at top of page.
      fBlankNext = False         ' Never print first line of page blank.
   

3. Now select the Detail Section's OnPrint, and enter this code without the line numbers:
   1. If PrintCount = 1 Then intLine = intLine + 1
   2. If fBlankNext Then
   3. Me.PrintSection = False
   4. Me.NextRecord = False
   5. fBlankNext = False
   6. Else
   7. Me.PrintSection = True
   8. Me.NextRecord = True
   9. fBlankNext = (intLine Mod 5 = 0)
   10. End If

Need some explanation? In line 9, the statement inside the brackets evaluates to True when the line counter is an exact multiple of 5 (i.e. the remainder is zero.) This True/False result is assigned to fBlankNext, so this flag becomes True every fifth record.

When the next record is about to print and fBlankNext is True, lines 3~5 will execute. MoveLayout is still True, but PrintSection is False, so Access moves down a line and prints nothing. This gives a blank line, at the expense of the record that wasn't printed! By setting NextRecord to False (and resetting our fBlankNext flag), the missed record stays current and is printed next time.

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.

In some applications you may wish to offer the user the choice to print any saved report. It would be handy to have Access fill the names of all reports into a list box for the user to select and print. Here's how. (Note: constant names will need changing for version 2.)
Create a form with:
a list box named lstReports, with a label Reports;
a check box named chkPreview, with a label Preview;
a command button named cmdOpenReport, with caption Open Report.
Set the command button's OnClick property to [Event Procedure]. Click the ..."" button beside this property to open the code window, and enter the following:

Private Sub cmdOpenReport_Click()
'Purpose: opens the report selected in the list box.
On Error GoTo cmdOpenReport_ClickErr
If Not IsNull(Me![lstReports]) Then
DoCmd.OpenReport Me![lstReports], IIf(Me![chkPreview], acPreview, acNormal)
End If
On Error GoTo 0
Exit Sub
cmdOpenReport_ClickErr:
Select Case Err
Case 2501 ' Canceled by user, or by NoData event.
MsgBox "Report canceled, or no matching data.", vbInformation, "INFORMATION"
Resume Next
Case Else
MsgBox "Error " & Err & ": " & Error$, vbInformation, "cmdOpenReport_Click()"
Resume Next
End Select
End Sub

Set the list box's RowSourceType property to EnumReports. Leave the RowSource property blank.

Create a new module and copy the function below into this module:

Function EnumReports(fld As Control, id As Variant, row As Variant, col As Variant, code As Variant) As Variant
' Purpose: Supplies the name of all saved reports to a list box.
' Usage: Set the list box's RowSourceType property to: EnumReports
' leaving its RowSource property blank.
' Notes: All arguments are provided to the function automatically.
' Author: Allen Browne abrowne@odyssey.apana.org.au Feb.'97.
Dim db As Database, dox As Documents, i As Integer
Static sRptName(255) As String ' Array to store report names.
Static iRptCount As Integer ' Number of saved reports.
' Respond to the supplied value of "code."
Select Case code
Case acLBInitialize ' Called once when form opens.
Set db = CurrentDb()
Set dox = db.Containers!Reports.Documents
iRptCount = dox.Count ' Remember number of reports.
For i = 0 To iRptCount - 1
sRptName(i) = dox(i.)Name ' Load report names into array.
Next
EnumReports = True
Case acLBOpen
EnumReports = Timer ' Return a unique identifier.
Case acLBGetRowCount ' Number of rows
EnumReports = iRptCount
Case acLBGetColumnCount ' 1 column
EnumReports = 1
Case acLBGetColumnWidth ' 2 inches
EnumReports = 2 * 1440
Case acLBGetValue ' The report name from the array.
EnumReports = sRptName(row)
Case acLBEnd
Erase sRptName ' Deallocate array.
iRptCount = 0
End Select
End Function
 
How it works
The RowSourceType property of a list box can be used to fill the box programmatically. The five arguments for the function are provided automatically: Access calls the function repeatedly using these arguments to indicate what information it is expecting.

During the initialization stage, this function uses DAO (Data Access Objects) to retrieve and store the names of all reports into an static array. (Note: it is necessary to use the Containers!Reports.Documents collection, as the Reports object refers only open reports.)

The command button simply executes an OpenReport action. If the check box is checked, the report is opened in Preview mode, else it is printed directly.