Reports: Create a blank line every fifth record

        2 votes: ****      6,125 views      0 comments
by Allen Browne, 20 April 2005    (Access 2+)

MS-Access Tips for Serious Users

Provided by Allen Browne, allen@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.


HomeIndex of tipsTop

Rate this article:   Your rating: Poor Your rating: Not so good Your rating: Average Your rating: Good Your 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...

Name
E-mail (e-mail address will be kept private)
Comments
Verify Code
Verification Code


It may take 30 seconds after clicking submit - please be patient to avoid duplicate submissions.  

All comments must be approved before being displayed on this web page.  This process may take up to 24 hours.