How to print full pages of records in Microsoft Access

Recently I was recreating a government form in Access that included some columns with background colors. Each row was a fixed height, and if a page was not completely filled there was a large blank space before the page footer printed. I needed a way to print extra records to fill the empty space. I did some digging and came across some sample reports from ACG Soft that included one for duplicating Detail rows. There wasn’t any documentation or comments in the code so I had to pull it apart and figure it out myself. It turns out it is surprisingly simple.

1. Count how many records fit on a page. No programming involved in this and it should be a constant value.
2. Count the number of records you will be printing.
3. As records are printed compare how many have been printed with how many will fit on a page.
4. When you run out of records to print, stop Access from advancing to the next record until you reach the number that will fit on a page.

Here is the code I ended up using.

Option Compare Database
Option Explicit

Dim RowCounter As Integer
Dim TotalRows As Integer

Const MAX_ROWS As Byte = 17
Const COLOR_WHITE = &HFFFFFF
Const COLOR_BLACK = &H0

Private Sub Report_Open(Cancel As Integer)
  Dim ThisRecordset As DAO.Recordset

  'Get the number of rows to be printed
  Set ThisRecordset = CurrentDb.OpenRecordset("SELECT COUNT(*) FROM [qFuel Log Summary by State2]")

  If Not ThisRecordset.EOF And Not ThisRecordset.BOF Then
      TotalRows = ThisRecordset.Fields(0)
  End If

  Set ThisRecordset = Nothing
End Sub

Private Sub PageHeader_Format(Cancel As Integer, FormatCount As Integer)
  'Reset the row counter for each page
  RowCounter = 0

  'When you hit a new page decrease the total rows to be printed by one page worth
  If Report.Page > 1 Then
      TotalRows = TotalRows - MAX_ROWS
  End If

  'Make sure the text is visible
  Call SetForegroundColor(COLOR_BLACK)
End Sub

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
  'This event is called every time a record prints.

  'Keep track of the number of rows printed
  RowCounter = RowCounter + 1

  If RowCounter < TotalRows Then
      'There is still more to do
      Exit Sub
  Else
      'Make sure you do not overfill a page
      If RowCounter < MAX_ROWS Then
          'This keeps Access from advancing to the EOF marker and printing the Page Footer
          Me.NextRecord = False
      End If
  End If

  'Since you are not advancing to a blank record you have to hide
  '  the text so it does not print.
  If RowCounter > TotalRows Then
      Call SetForegroundColor(COLOR_WHITE)
  End If

End Sub

Private Sub SetForegroundColor(Color As Long)
  Select Case Color
  Case COLOR_BLACK
      Me!St.ForeColor = Color
      Me!Mileage.ForeColor = Color
      Me!Mileage2.ForeColor = Color
      Me![Taxable Gallons].ForeColor = Color
      Me![Tax Pd Gal].ForeColor = Color
      Me![Net Gal].ForeColor = Color
      Me![Tax Rate].ForeColor = Color
      Me![Tax].ForeColor = Color
      Me![Surcharge Rate].ForeColor = Color
      Me![Surcharge].ForeColor = Color
      Me![Net Tax].ForeColor = Color
      Me![NetTax2].ForeColor = Color
  Case COLOR_WHITE
      'Some of the columns use a colored background so I can't just set
      '  all the foreground text to white.  Also, some of the textboxes
      '  have a transparent background and others have white, so I can't
      '  just set all the foreground text the same color as the background.
      Me!St.ForeColor = Me!St.BackColor
      Me!Mileage.ForeColor = Me!Mileage.BackColor
      Me!Mileage2.ForeColor = Me!Mileage2.BackColor
      Me![Taxable Gallons].ForeColor = Color
      Me![Tax Pd Gal].ForeColor = Me![Tax Pd Gal].BackColor
      Me![Net Gal].ForeColor = Color
      Me![Tax Rate].ForeColor = Color
      Me![Tax].ForeColor = Me![Tax].BackColor
      Me![Surcharge Rate].ForeColor = Color
      Me![Surcharge].ForeColor = Color
      Me![Net Tax].ForeColor = Color
      Me![NetTax2].ForeColor = Color
  End Select
End Sub

This LotusScript was converted to HTML using the ls2html routine,
provided by Julian Robichaux at nsftools.com.