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.

Microsoft's idea of backwards compatability

Office 2007 SP1 was released recently. Anyone who installed it and recompiled their Access projects (accde, ade or mde) discovered that the application is now only usable by someone who also has Access 2007 SP1. Microsoft is aware of the problem and has included three options for correcting it:

1. Install Office 2007 SP1 on the computer -- *duh*
   
2. Create the compiled application on a computer without SP1 installed -- *double duh*
3. Use the 2007 SP1 version of the Access 2007 executable file -- are you freaking kidding me?!

Apply Office service packs with caution.

If you use MS Access 2003 do not upgrade to Office 2003 SP3

Since I'm responsible for an ERP written in Access (yes, a full ERP in Access 2003) I'm extremely apprehensive of anything that might affect Access on end user's PC's. Office 2003 SP3 doesn't disappoint. There are several known issues but they pale in comparison to the problems being reported in the wild. These include Access applications that simply stop working in SP3, design elements being corrupted, and subforms that stop inserting records. Scary stuff indeed.

Couple this with Microsoft having to release the Access 2007 runtime three times -- and it's still got problems -- and I'm wondering if they do actually QA this stuff before they release it. I had a friend who worked for Oracle who used to joke their testing consisted of "SELECT * FROM Emp. Looks good? SHIP IT!"

How to programmatically create an Access ADE or MDE file

My primary job is working with a massive Access Database Project (ADP). Before this is distributed to end users it gets compiled to an ADE. We have a utility that handles the deployment, but I still had to create the ADE file manually... and I often forgot, which meant I had to sometimes publish the same update multiple times. It's convoluted, I'll go into detail about it some other time.

Anyway, I finally decided it was time to automate this portion of the process so I couldn't forget it. It took a while, but here are the results. Note that this code is from VB6, but it should work equally well from any VB/VBA type environment:

Sub GenerateMDEFile(SourcePath As String, DestPath)
   'SourcePath is the ADP file.  DestPath is the ADE file.
 
   On Error Resume Next
   'I don't normally do this, but with automation you sometimes get weird errors
   '   that will cause the calling app to crash, but they can be safely ignored.
   '   Also, since we're using SendKeys you don't want an error dialog to pop up
   '   and start getting your keystrokes.  That would be bad.

   Dim objAccess As Variant
   Set objAccess = CreateObject("Access.Application")
 
   'Delete the destination (ADE) file if it exists
   If Dir$(DestPath) <> "" Then
       Kill DestPath
   End If
 
   'Make sure the Access window is active
   objAccess.Visible = True
   DoEvents
 
   'This pastes in the source path, then hits Enter twice.
   '   The first Enter accepts the source path
   '   The second accepts the default name for the compiled version
   SendKeys SourcePath & "{Enter}{Enter}"
   objAccess.DoCmd.RunCommand acCmdMakeMDEFile 'Constant with a value of 7
 
   objAccess.Quit
   Set objAccess = Nothing
End Sub

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

Sources: Xtreme Visual Basic Talk, Microsoft Knowledgebase