Working and automating an Access report VBA will require some knowledge of the module window (VBE), the defined sections and an insight to pre-defined events for each element of a report design interface.

However, let’s take a step back and identify some of the report properties that can be used in a module that do not appear in a standard design view layout for a report. The three I want to highlight here and you may want to note are:

1. PrintSection
2. NextRecord
3. PrintCount

In fact, to see a full list of report properties, open the Object Browser window (F2 on the keyboard) via the microsoft Access database VBE window.

These hidden properties can be manipulated only during the Access report run time which means the report needs to be in Print Preview mode only (and not the alternative Layout View mode).

Access Report VBA: Using Some VBA Report Properties To Automate Your Microsoft Access Reports

In this example, I want to add a blank line in place of where a record would normally appear within the detail section of my report after every 10th record as an alternative to using either multiple sections or applying complex formatting for each line (using typically some conditional formatting).

My report should look like this:

In order to achieve the above, I will need to add some code and take advantage of using some of the hidden Access report VBA properties.

Here’s the code:

Option Compare Database
Option Explicit

Private int_BlankNext As Integer  ‘A Flag if print next line blank?
Private int_Line As Integer    ‘A line counter.

‘Detail section – OnPrint event…
Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)

    If Me.PrintCount = 1 Then int_Line = int_Line + 1

    If int_BlankNext Then
        Me.PrintSection = False
        Me.NextRecord = False
        int_BlankNext = False
    Else
        Me.PrintSection = True
        Me.NextRecord = True
        int_BlankNext = (int_Line Mod 10 = 0)
    End If

End Sub

‘Page Header section – OnFormat event…
Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As Integer)
    int_Line = 0               ‘Reset line counter at top of each page.
    int_BlankNext = False      ‘Avoid printing first line of page as blank
‘- keeps it tidy.
End Sub

Copy the above code into your report module and make the necesssary adjustments.

Save your changes and run the report in Print Preview mode.

Now, here’s your challenge! Add a Breakpoint and then look through the code and debug step by step each line as you run the report Print Preview again to understand the logic.

You may want to work with a smaller amount of records (at least 30 records) to loop through each record as it counts away. Use the Immediate window to help print out and observe the properties and their values too.

Any questions or comments? Ask away in the reply box below.

Tags: access report vba, access vba code, designing microsoft access reports, ms access reports, using vba in access, vba