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

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

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 Access VBE window.

access report vba

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:

access report vba - preview

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.

This entry was posted in Modules, Reports, VBA and tagged , , , , . Bookmark the permalink.

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

  1. Ben says:

    Hi, There is a dedicated section for ‘group by’ as a property in the report design and can also be coded using VBA.
    It’s just a matter of which field(s) you want to group by. This may help https://accessdatabasetutorial.com/2011/07/05/microsoft-access-database-creating-group-and-summary-reports-the-quick-way/

  2. Germaine says:

    How do I do this for a grouped report?

    Meaning:
    create the line break by the first group.

  3. I discovered your blog site on google and check a few of your early posts. Continue to keep up the very good operate. I just additional up your RSS feed to my MSN News Reader. Seeking forward to reading more from you later on!

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Confirm you are human.