databasedev.co.uk - database solutions and downloads for microsoft access

Printing Two Records Per Page on a Microsoft Access Report

When printing a report in Microsoft Access it is quite simple to specify that we only print one record per page by using the Force New Page options for the Detail Section of the report. It is however, not quite so simple to specify printing a specific number of records per page. In this Microsoft Access Report tutorial, we are going to detail how we can achieve this by setting options to print 2 records per page in the report.

If we look at the original report, shown below, we can see that the report page contains multiple records:

Fig 1. The original report, showing multiple records per page.

Now, if we switch the report to design view, we are going to add a Text Box control to the reports Detail section. We will apply the following settings to the new text box:

• Name: txtcounter
• Control Source: =1
• Running Sum: Over Group
• Visible: No

The image shown below in design view shows the new text box control (shaded yellow) added to the detail section:

Fig 2. The report in design view, including the new text box control.

The property sheet for this text box should look like the following:

Fig 3. The properties for txtcounter

We now need to include the VBA code that will ensure that the report displays two records per page. We add this to the On Format event of the report:

Fig 4. Adding the Event Procedure to the On Format event

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

    If [txtcounter] Mod 2 = 0 Then
        Me.Detail.ForceNewPage = 2
    Else
        Me.Detail.ForceNewPage = 0
    End If
    
End Sub

Now, when we run the report once again, each page of the Microsoft Access report will include only two records per page:

Fig 5. The report, now showing two records per page.