WithEvents and Access Report Event Sink ~ LEARN MS-ACCESS TIPS AND TRICKS

Introduction

We have explored several WithEvents examples using Class Modules, capturing both built-in and user-defined events from form-based controls. We created Class Module object arrays for multiple TextBox controls on a form, as well as separate Class object instances for each TextBox on the form or its sub-forms, adding them as items in a Collection object.

We observed that both approaches—arrays and collection items—work effectively in capturing events raised from form-based TextBoxes and in executing their respective event-handling procedures.

There are still other control types we can experiment with alongside TextBoxes when multiple controls are present on the same form, and we will definitely explore those later.

For now, after this lengthy trial run with TextBox controls on forms, it’s time for a change of scene—to move away from Access Forms for a while and try a few simple event-handling demo runs in Microsoft Access Reports.

Preparations.

Need the following Objects for the trial run:

Table with Students' Names and Marks.
Report designed with the Students Table.
Class Module to handle the Report Print Event.

We need a sample table containing a few students’ names along with their total exam marks. Our task is to highlight the marks of students who have passed and update a Label control positioned to the right of their marks, setting its Caption to the specified value.

Image of sample Table: Students

Right-click to open Large Image in New Tab/Window.

Sample Report designed using the above Table, and the sample image of the Report is given below, without running the Event Procedures.

Right-click to open Large Image in New Tab/Window.

Report Image Contents

The Report’s Detail section displays the table records, including employee names and their total marks. In the Report Header section, additional information is provided to explain how the pass percentage is calculated. The maximum marks for all subjects are 600, and students scoring 60% or above are considered to have passed.

A Text Box labeled Set Pass % is placed on the far right side of the Header section. The user can enter a different percentage value here (as a whole number), which is currently set to 65. Based on this value, the Print event procedure calculates each student’s percentage and highlights the marks of students who meet or exceed the threshold. A label control with the caption Passed will also appear to the right of their marks.

An example report output, after executing the event procedure and highlighting the passed students’ marks, is shown below:

Right-click to open Large Image in New Tab/Window.

Class Module Code

The Class Module: ClsStudents VBA Code that handles the Report Detail Section Print Event is given below:

Option Explicit

Private txt As Access.TextBox
Private pct As Access.TextBox
Private max As Access.TextBox

Private WithEvents Rpt As Access.Report
Private WithEvents secRpt As Access.[_SectionInReport]

Public Property Get mRpt() As Access.Report
   Set mRpt = Rpt
End Property

Public Property Set mRpt(RptNewVal As Access.Report)
Const strEvent = "[Event Procedure]"

  Set Rpt = RptNewVal
  With Rpt
     Set secRpt = .Section(acDetail)
     secRpt.OnPrint = strEvent
  End With
  
  Set txt = Rpt.Controls("Total")
  Set max = Rpt.Controls("Maxmarks")
  Set pct = Rpt.Controls("Percentage")

End Property

Private Sub secRpt_print(Cancel As Integer, printCount As Integer)
Dim curval As Double
Dim m_Max As Double
Dim pf As Double
Dim pp As Double
Dim lbl As Access.Label

On Error GoTo secRpt_Print_Err

Set lbl = Rpt.Controls("lblPass") 'set reference to Label: lblpass

m_Max = max.Value 'retrieve Maximum Marks (600)
curval = txt.Value 'get current Report Line 'Total' TextBox value
pp = pct.Value 'get the percentage value (65)

pf = curval / m_Max * 100 'calculate obtained marks percentage

If pf >= pp Then 'if it is greater or equal to 65
    txt.FontBold = True
    txt.FontSize = 12
    txt.BorderStyle = 1
    lbl.Caption = "Passed" 'change label caption to 'passed'
Else 'reset to normal
    txt.FontBold = False
    txt.FontSize = 9
    txt.BorderStyle = 0
    lbl.Caption = ""
End If

secRpt_Print_Exit:
Exit Sub

secRpt_Print_Err:
MsgBox Err.Description, , "secRpt_Print()"
Resume secRpt_Print_Exit
End Sub

Let’s briefly review what happens within the above Class Module.

In the global declarations section of the Class Module, three Text Box control properties are defined. The first property, txt, will be assigned the marks of each student from the Detail section of the Report during its line-by-line printing phase.

The next two properties, max and pct, will be linked to the MaxMarks and Percentage Text Boxes located in the Report Header section. These will hold the maximum marks (e.g., 600) and the pass percentage (e.g., 65), respectively. These values are used to calculate each student’s percentage score.

Following these are two more declarations: the Rpt property, which references the Report object itself, and the secRpt property, which references the Report’s Detail section.

The only Get and Set property procedures in this Class Module are for the Report object. They receive the active Report object from the Report’s Class Module and assign it to the Rpt property. Once this reference is set, the secRpt property is assigned to the Report’s Detail section, and the Report_Detail_Section_OnPrint() event handler is enabled using the following statements:

Set Rpt = RptNewVal
  With Rpt
     Set secRpt = .Section(acDetail)
     secRpt.OnPrint = strEvent
  End With

The next three statements assign references of the Report’s Text Box controls to the txt, pct, and max properties declared at the top of the Class Module.

Before the Report is displayed in Print Preview or sent to the printer, Access performs several formatting passes to arrange the content on each page, line by line. Only after these formatting passes are complete does the Print action occur—this is the final phase in preparing and rendering each page of the Report.

The Report.Section(acDetail).OnPrint() Event.

We are specifically interested in the Print event of the Report’s Detail section, which we capture in the secRpt_Print() subroutine. During this event, the student’s total marks are retrieved into the curval variable using the expression curval = txt.value. The program then calculates the percentage of marks obtained out of 600 and compares it with the pass percentage specified in the Report Header.

If the student meets or exceeds the pass percentage, their total marks TextBox is visually highlighted—the border is emphasized, the font size is increased to 12 points, and the font style is set to Bold. Additionally, a label control appears to the right of the Text Box with the caption “Passed”.

The Report_Students Class Module Code is given below.

Option Compare Database
Option Explicit

Private R As New ClsStudents

Private Sub Report_Open(Cancel As Integer)
  Set R.mRpt = Me
End Sub

The Class Module ClsStudents is instantiated in Class Object R.

On the Report_Open Event, the current Report Object is passed to the Set Property Procedure Set R.mRpt().

Important Points to Note

Once the Report is fully designed and configured as described above, it’s time to view its contents and observe the Print event being captured by the Class Module object.

Microsoft Access provides several viewing modes for Reports besides Design View, such as:

Report View – Displays the report as a scrollable, interactive layout without pagination.
Print Preview – Shows how the report will appear when printed, with page breaks and formatting applied.
Layout View – Allows you to adjust the layout while viewing live data.

For our demonstration, Print Preview is the most suitable option because it triggers the Print event for each detail line, allowing our event-handling code in the Class Module to execute as intended.

The Report or Report Section onPrint or Format Event will not fire on the first two Report Views.

You can find the Report with Data and the way you designed it. But, you will not find the result of your Event Procedure running in the Class Module if you use the first two methods.

In that case, use the following methods:

Right-click on the Report in the navigation pane and select Print Preview from the displayed menu.
If you double-clicked on the Report in the navigation pane and you ended up in the Report view mode, then right-click on an empty area in the Report View and select Print Preview from the displayed menu.

If you are not using Access 2007, always make sure to open the report in Print Preview mode—using whichever option is available in your version—to ensure that the Report_Print or Report_Format events are triggered.

Summary

The active Report Detail Section OnPrint event is enabled from within the Set mRpt() Property Procedure of the ClsStudents Class Module. When this event is raised on the Report, it is captured within the Class Module itself through the Private Sub secRpt_Print() procedure. Each data line in the Report’s Detail Section is validated, and if the student is found qualified, their Marks Text Box is highlighted in the Report’s Print Preview.

All these actions are handled entirely within the Class Module, keeping the Report’s own Class Module almost empty—containing only four lines of code.

A demo database is attached. You may download it to try out the example and study the code. Experiment with something similar on your own as a self-test, using the demo as a reference point whenever you are unsure about syntax or other details.

In the next session, we will explore how to print only the passed students on the Report without using a Query to filter the data. As a hint, we will achieve this by hiding the failed students’ lines in the Report’s Detail Section.