Reports: Create a blank line every fifth record

        2 votes: *****     6,381 views      No comments
by Allen Browne, 20 April 2005    (for Access v2+)

MS-Access Tips for Serious Users

Provided by Allen Browne,

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.

HomeIndex of tipsTop

Rate this article:  Your rating: PoorYour rating: Not so goodYour rating: AverageYour rating: GoodYour rating: Excellent

This is a cached tutorial, reproduced with permission.

Have your say - comment on this article.

What did you think of 'Reports: Create a blank line every fifth record'?

No comments yet.

Why not be the first to comment on this article?!

Have your say...

E-mail (e-mail address will be kept private)

Comments require approval before being displayed on this page (allow 24 hours).