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

Saturday, June 8, 2019

WithEvents and Access Report Event Sink

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) work well in capturing Events Raised from Form TextBoxes 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 are present on the same Form. We will definitely take them up a little later.

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

Preparations.

Need the following Objects for the trial run:

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

We need a sample Table with a few Students' names and their exam 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 names, and total marks obtained. In the Report Header area, some extra information is inserted to explain how the Pass Mark percentage of students is calculated. The Maximum Marks for all subjects are 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 the whole number format, currently set to 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 TextBoxes MaxMarks & Percentage in 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 define 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 set 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 capture it in the secRpt_Print() sub-routine when it happens. The Total marks of the student are saved in the 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 compare it with the pass percentage given in 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, and 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 have a complete design and set 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:

Layout View
Report View
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:

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 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 are Validated and if found qualified then highlight 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 for 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 a self-test. You may use the demo database as a reference point when you are in doubt about 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 a clue, we will hide the failed students' lines from the Report detail Section.