MS-Access Tips for Serious Users

Provided by Allen Browne, allenbrowne.com

Reports: a blank line every fifth record

In addition to the OnFormat and OnPrint events (see Reports: Page Totals for an example), Access 2 and later provide three True/False properties:

• 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.