MS Access Reports VBA Code
Most of us use Access reports to format and print out database
information. The reporting package takes some getting used to, but it is
reasonably powerful.
Sometimes formatting issues come up that can only be solved by
exporting to MS Word or some other document publishing tool. Unfortunately, the
export to Word strips any kind of drawing object (like the dividing lines in
reports) and any binary objects (like pictures or graphics embedded from
records).
Some extra formatting functionality can be put into Access reports
using VBA. While you will never match the formatting capabilities of Word in an
Access report, you can achieve better results by learning about Events in Access
reports.
To give a simple example, suppose you'd like to suppress the page
footer on the first page of your report, then using a 'facing page' format for
the footers on each following page. In other words, pages 2,4,6... will have
right justified text and pages 3,5,7... will have left justified text.
Create your labels or textboxes in the page footer section so that they
cover the entire width of the report. Then, while you're still in design view,
right-click in the page footer section (somewhere not inside a textbox or label)
and choose Build Event...
You should find yourself in the VB Editor inside the
PageFooterSection_Format event. Paste the code below (except for the Private
Sub... and End Sub lines) into the event. Change the control names below -
Text10 and Label21 - to the textbox or label name(s) in your report's page
footer. We have both a textbox and a label just for examples.
You can probably think of other things to do with the Page Headers and
Footers and you can use this code as a model.
'---------------------- Start module ------
'I Get It! Development provides programming examples
'for illustration only, without warranty either
'expressed or implied, including, but not limited to,
'the implied warranties of merchantability and/or
'fitness for a particular purpose. This article
'assumes that you are familiar with the programming
'language being demonstrated and the tools used to
'create and debug procedures. These examples assume that
'you have licensed copies of all relevant software installed
'on the machine upon which the examples will be run.
Private Sub PageFooterSection_Format(Cancel As Integer, FormatCount As Integer)
'This module formats a page footer in the 'facing
page' style
'Int truncates decimal points. Even # pages will pass the test below
'4/2 = 2.000 = 2
'Odd # pages will not
'5/2 = 2.500 <> 2
If Me.Page = 1 Then
Me.Text10.Visible = False
Me.Label21.Visible = False
Else
Me.Text10.Visible = True
Me.Label21.Visible = True
If Me.Page / 2 = Int(Me.Page / 2) Then
'This is an even page
Me.Text10.TextAlign = 3
Me.Label21.TextAlign = 3
Else
'This is an odd page
Me.Text10.TextAlign = 1
Me.Label21.TextAlign = 1
End If
End If
End Sub