Introduction

We have tried out several WithEvents examples with Class Modules, capturing built-in Events and User-defined Events from Form based Controls.  We have created Class Module Object Arrays for several Text Box Controls on Form. We have also tried creating separate Class Object instances for each Text Box on the Form or sub-forms and added them  to Collection Object Items.  We found both the above methods (Arrays or Collection Object Items) works well in capturing Events Raised from Form Text Boxes and in executing the Event handling sub-routines. 

There are other control types to try out with Text Box controls on the Form, when several of them present on the same Form. We will definitely take them up little later.

After lengthy trial runs with Text Box Controls on Form need some change of scene now, away from Access Forms for the time being, and go for the Microsoft Access Reports for few simple Event handling demo runs.

Preparations

Need the following Objects for the trial run:

1. Table with Students Name and Marks.
2. Report designed with the Students Table.
3. Class Module to handle the Report Print Event.

We need a sample Table with few Students name and their exam's marks total. Our task is to highlight the passed student's mark and update a Label control, to the right of their marks with the Caption value passed .

Image of sample Table: Students

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

Report Image Contents

The Report Detail Section shows the Table records, employee name and total marks obtained.  On the Report Header area some extra information is inserted to explain how the Pass Mark percentage of student is calculated.  The Maximum Marks of all subjects is 600.  Students with 60% or more marks are in the Passed category.

There is a Text Box on the Header Section, to the extreme right area, with the header label Set Pass %.  The user can set a different percentage parameter value expressed in whole number format, currently set with the value 65.  Based on this value the Print Event Procedure will calculate the obtained marks percentage and highlight the qualified student's marks. A label control with the Caption Passed will appear to the right of the Marks.

The sample Report image after executing the Event Procedure, highlighting passed students Marks, is given below:

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 us have a brief introduction to what is going on within the above Class Module.

Out of the top three Text Box control declarations in the Global Area of the Class Module the first txt Property will be assigned with the Marks of each student from the Detail Section of the Report, during the line by line Printing phase of the Report.

The next two Text Box Properties max and pct will be set with direct references of the Text Boxes MaxMarks & Percentage on the Header Section of the Report.  These will be assigned with the values Maximum Marks and Percentage, sample values 600 and 65 respectively.  These will be used in calculations to find the percentage of marks of each student.

The first of the next two lines declares the Access.Report Property Rpt and the second line defines Property secRpt for the Report's Detail Section.

The only Get/Set Property Procedures in the Class Module are for the Report Object and assigns the active Report Object from the user Report's Class Module.

Once the Report reference is received in the Set Property Procedure the Report Detail Section is assigned to the SecRpt Property and the Report_Detail_Section_OnPrint() Event is enabled through the following statements:

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

The next three statements sets the references of the Text Box Controls on the Report to the txt, pct & max Properties, declared at the top of the Class Module.

Before the Report is shown to us, in Print Preview or sent to Printer, the Report Page Lines undergoes several formatting passes by the System to lay out the Page contents line by line and finally Prints it line by line on the Report Page.  This Print action is the final phase in the preparation of a Report Page.

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

We are only interested in the Report's Detail Section Print Event and captures it in secRpt_Print() sub-routine, when it happens.  The Total marks of the student is saved in curval Variable by the expression curval = txt.value.  Calculations are made to arrive at the percentage of marks obtained by the student out of 600 and compares it with the pass percentage given on the Report Header. When the student is found passed then his/her Total marks Text Box is highlighted.  The Text Box border will be highlighted, the font-size will be increased to 12 points and set with Bold style.  A label control to the right of the Text Box will appear 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 you complete designing and setting up the Report as explained above it is time to view the Report Contents with the Print Event sink action in Class Module Object.  There are different Views for an Access Report, besides Design View:

1. Layout View
2. Report View
3. Print Preview

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:

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

If you are not using Access 2007 Version then always try to select the Print Preview Mode through whatever option available there, to Raise the Report_Print or Report_Format Event.

Summary

The active Report Detail Section onPrint Event is enabled from within the Set mRpt() Property Procedure of ClsStudents Class Module.   The Event when Raised on the Report it is captured from within the Class Module itself in the Private Sub secRpt_Print() sub-routine.  The data in each line in the Detail Section of the Report is Validated and if found qualified then highlights the Text Box in the Report Print Preview. 

All actions are executed from within the Class Module leaving the Report Class Module nearly empty except four lines of Code.

The Demo database is attached.  You may download and try out and study the code.  Try out something similar on your own for self test.  You may use the demo database as a reference point when you are in doubt with the Syntax or some other issues.

Next week we will try how to take Printout of Passed Students alone on the Report without creating a Query to filter the Data.  I will give you clue, we will hide the failed students lines from the Report detail Section.

1. WithEvents Ms-Access Class Module Tutorial
2. WithEvents and Defining Your Own Events
3. withevents Button Combo List TextBox Tab
4. Access Form Control Arrays and Event Capturing
5. Access Form Control Arrays and Event-2
6. Access Form Control Arrays and Event-3
7. WithEvents in Class Module for Sub-Form TextBox Events
8. WithEvents in Class Module and Data Entry