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:
- PrintSection
- NextRecord
- 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
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!
How do I do this for a grouped report?
Meaning:
create the line break by the first group.
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/