LEARN MS-ACCESS TIPS AND TRICKS

WithEvents Textbox CommandButton Dictionary

a.p.r. pillaiJuly 29, 2019WithEvents No comments

Continued from Last Week's Post

So far, we have used the Collection object to hold all the Form controls' Class Object Instances as an alternative to using class object Arrays.

If you have landed directly on this page, please go through the earlier post, “WithEvents TextBox and CommandButton Control Arrays”, before continuing.

In that earlier post, we used class object arrays of TextBox and CommandButton Controls on the Form. The main challenge with this approach was the need to maintain separate array indexes for each control type’s class object instances, as well as repeatedly re-dimensioning the arrays. When several different types of form controls are involved—such as ComboBoxes, ListBoxes, OptionGroups, TabControls, and others—this method quickly becomes cumbersome and complex.

We overcame this complexity by using a Collection object Container for all the control class object instances.

We have already explored a few examples using the Collection object in some of our earlier posts. Links to all our posts on Form and Report control event capturing are provided at the end of this article for your reference.

Usage of Dictionary Object, Replacing Array

Here, we will replace the Collection Object with the Dictionary Object to hold all the Form Controls' Class Object Instances. Let us see what difference it makes to implement the Dictionary method.

Here, we will examine last week's Derived Class Object (ClsTxtBtn_Derived) Code without any change. The full VBA Code of last week's Class Module Derived Object Code is given below:

Option Compare Database
Option Explicit

Private T() As New ClsText
Private B() As New ClsCmdButton
Private m_Frm As Access.Form

Public Property Get mfrm() As Access.Form
  Set mfrm = m_Frm
End Property

Public Property Set mfrm(ByRef frmObj As Access.Form)
  Set m_Frm = frmObj
  init_Class
End Property

Private Sub init_Class()
   Dim ctl As Control
   Dim tCount As Long
   Dim bCount As Long
Const Evented = "[Event Procedure]"

tCount = 0 'counter for textbox controls
bCount = 0 'counter for Command Button controls
For Each ctl In m_Frm.Controls
   Select Case TypeName(ctl) 'Type name TextBox or CommandButton ?
     Case "TextBox"
         tCount = tCount + 1 'increment counter
         ReDim Preserve T(1 To tCount)  'redimension TextBox Class Objecct Array
         Set T(tCount).p_frm = m_Frm 'pass Form Object
         Set T(tCount).p_txt = ctl 'pass the TextBox control
         
         Select Case ctl.Name
            Case "Text2" ' enable AfterUpdate and OnLostFocus Events
                T(tCount).p_txt.AfterUpdate = Evented
                T(tCount).p_txt.OnLostFocus = Evented
            Case "Text4"
                T(tCount).p_txt.AfterUpdate = Evented
                T(tCount).p_txt.OnLostFocus = Evented
            Case "Text6"
                T(tCount).p_txt.AfterUpdate = Evented
                T(tCount).p_txt.OnLostFocus = Evented
        End Select
            
      Case "CommandButton"
         bCount = bCount + 1 'increment counter for CommandButton
         ReDim Preserve B(1 To bCount) 'redimension Button Class Object Array
         Set B(bCount).p_Btn = ctl 'pass CommandButton control
        
        Select Case ctl.Name
            Case "Command8" 'Enable Click Event
                B(bCount).p_Btn.OnClick = Evented
            Case "Command9"
                B(bCount).p_Btn.OnClick = Evented
        End Select
      End Select
    Next
End Sub

The ClsText and ClsCmdButton classes are defined as array properties—T() and B()—with an initially undefined number of elements. The Form property (m_Frm) is declared next, followed by its Get and Set property procedures for assigning and retrieving the active Form object.

After the Form object is assigned in the Set property procedure, the Class_Init() subroutine is called to enable the required event procedures for the form’s controls.

In this example, three text boxes (Text2, Text4, Text6) are enabled only for the AfterUpdate() and LostFocus() events, since the ClsText class module currently contains subroutines only for these events.

Note: Even if the ClsText class module contains subroutines for other events (such as BeforeUpdate, GotFocus, KeyDown, KeyUp, etc.), not all of them need to be enabled for every project. Only the events required for a specific form are activated in the derived class module, while the other event subroutines remain dormant until needed for a particular form or report.

The Form and TextBox control references are passed from the Derived Object to the ClsText Class Object Properties through the following statements:

Set T(tCount).p_frm = m_Frm
Set T(tCount).p_txt = ctl

Command Button Control reference is passed to the ClsCmdButton Class Module. The Command Button Click Event is enabled.

This procedure is repeated for each Control on the Form in the Derived Class Object Module: ClsTxtBtn_Derived.

Logical Error in VBA Code

The ClsTxtBtn_Derived Class Module Code works perfectly for our earlier example. But there is a logical error in the code, and we will correct it in later versions of the Code.

The placement of the following lines of Code under the Case "TextBox is not in the correct location " is incorrect because there is a chance that the Class Module Object will occupy more memory space.

Case "TextBox"
         tCount = tCount + 1 'increment counter
         ReDim Preserve T(1 To tCount)  'redimension TextBox Class Objecct Array
         Set T(tCount).p_frm = m_Frm 'pass Form Object
         Set T(tCount).p_txt = ctl 'pass the TextBox control

The issue with the current code placement is that if there are extra text boxes on the form with no events enabled, the code will still instantiate class objects for those controls and add them to the array, unnecessarily consuming memory. This happens silently without causing any functional errors, but it is logically incorrect.

To correct this, the lines that instantiate and add the class object must be placed inside the corresponding Case blocks—immediately below each Case "Text2", Case "Text4", and Case "Text6" statement. This ensures that only the intended controls with events enabled will get their class object instances created.

A similar change is required for the Command Button Case statements.

However, if all the TextBoxes and Command Buttons on the form are intended to have the specified events enabled, then there is no code change.

In our demo forms, we only used two or three text boxes and a few command buttons, and we enabled events for all of them. In such situations, the current code placement is acceptable and intentional, as it keeps the logic simple and avoids repeating the instantiation code inside each Case statement.

The Dictionary Object.

If you have not come across the Dictionary Object and its usage so far, then please visit the following links, or at least the first two:

When comparing the Collection and Dictionary objects, both support the Add method and accept two parameters—Key and Item—but their usage differs:

Dictionary object syntax:

'Object.Add' ItemKey, Item

Both parameters are mandatory.

Collection object syntax:

'Object.Add' Item, ItemKey

The ItemKey is the second parameter and is optional.

Unlike the Dictionary object, the ItemKey in a Collection is optional. However, since all control names on a form are unique, they can serve as ItemKey values when using a Dictionary.

The Dictionary object is part of the Windows Scripting Runtime library and can be created in VBA with the following statement:

Dim D as Object
Set D = CreateObject("Scripting.Dictionary")

Alternatively, you can enable the Microsoft Scripting Runtime library in your project by selecting it from Tools → References in the VBA editor. After adding this reference, you can declare a Dictionary object using early binding, as shown below:

Dim D As Dictionary

Using early binding offers the added benefit of IntelliSense support—when you type the declared object name followed by a dot (for example, D.), the VBA editor will display a list of all available properties and methods.

The new derived Class Module ClsTxtBtn_Dictionary (shown below) demonstrates this approach. Until now, we have used a Collection object as an alternative to arrays of class object instances. In this section, we will explore how to use the Dictionary object as a container for class object instances (specifically, for TextBox and CommandButton controls).

Option Compare Database Option Explicit Private T As ClsText Private B As ClsCmdButton Private m_Frm As Access.Form

Private D As Object Public Property Get mfrm() As Access.Form Set mfrm = m_Frm End Property Public Property Set mfrm(ByRef frmObj As Access.Form) Set m_Frm = frmObj init_Class End Property Private Sub init_Class() Dim ctl As Control Const Evented = "[Event Procedure]"

Set D = CreateObject("Scripting.Dictionary")

For Each ctl In m_Frm.Controls Select Case TypeName(ctl) 'Type name TextBox or CommandButton ? Case "TextBox" Select Case ctl.Name Case "Text2" Set T = New ClsText 'create new instance of Class Module Set T.p_frm = m_Frm 'pass Form Object Set T.p_txt = ctl 'pass the TextBox control T.p_txt.AfterUpdate = Evented ' enable AfterUpdate Event T.p_txt.OnLostFocus = Evented ' enable OnLostFocus Event 'Add ClsTxt Class Object as Dictionary Object Item D.Add ctl.Name, T 'TextBox Name as ItemKey, Class Instance as Item Set T = Nothing 'erase ClsText instance Case "Text4" Set T = New ClsText Set T.p_frm = m_Frm Set T.p_txt = ctl T.p_txt.AfterUpdate = Evented T.p_txt.OnLostFocus = Evented 'Add ClsTxt Class Object as Dictionary Object Item D.Add ctl.Name, T Set T = Nothing Case "Text6" Set T = New ClsText Set T.p_frm = m_Frm Set T.p_txt = ctl T.p_txt.AfterUpdate = Evented T.p_txt.OnLostFocus = Evented 'Add ClsTxt Class Object as Dictionary Object Item D.Add ctl.Name, T Set T = Nothing End Select Case "CommandButton" Select Case ctl.Name Case "Command8" Set B = New ClsCmdButton 'create new instance of ClsCmdButton Set B.p_Btn = ctl 'pass CommandButton control to Class Module B.p_Btn.OnClick = Evented 'Enable Click Event 'Add ClsCmdButton Class Instance as Dictionary Item D.Add ctl.Name, B ' Command Button Name as ItemKey, Class Instance as Item Set B = Nothing 'erase ClsCmdBtn instance Case "Command9" Set B = New ClsCmdButton Set B.p_Btn = ctl B.p_Btn.OnClick = Evented 'Add ClsCmdButton Class Instance as Dictionary Item D.Add ctl.Name, B Set B = Nothing End Select End Select Next End Sub

Preparing for a Trial Run.

Steps to Implement the ClsTxtBtn_Dictionary Class Module
1. Create the Class Module
  - In the demo database you downloaded from last week’s post, open the VBA editor (Alt + F11).
  - From the Insert menu, select Class Module.
  - In the Properties window, set its (Name) property to:
    ClsTxtBtn_Dictionary.
2. Add the Code
  - Copy the ClsTxtBtn_Dictionary class code provided above.
  - Paste it into the newly created class module.
  - From the Debug menu, select Compile <your database name> to recompile the project and confirm that no errors occur.
3. Create a New Form
  - In the Navigation Pane, right-click the existing form frmClassArray and select Copy.
  - Right-click again and select Paste.
  - In the prompt, give the new form the name:
    frmClass_Dictionary.
4. Update the Form’s Code
  - Open frmClass_Dictionary in Design View.
  - Open its Code Module window (right-click the form’s title bar → Build Event → Code Builder, or press F7).
  - Replace its existing code with the new code provided below.
```
Option Compare Database
Option Explicit

Private A As New ClsTxtBtn_Dictionary

Private Sub Form_Load()
   Set A.mfrm = Me
End Sub
```
Save the Form with the changed code.
Open the Form in Normal View and try the TextBoxes and Command Buttons to test that the LostFocus, After Update, and Command Button Clicks work as before.

Revised Code Segments.

Since all three TextBoxes (Text2, Text4, and Text6) are enabled by the same set of Events (AfterUpdate & LostFocus), the Case statements can be clubbed into one line and can avoid duplication of Code as given below:

Select Case ctl.Name
            Case "Text2", "Text4", "Text6"
                Set T = New ClsText 'create new instance of Class Module
                Set T.p_frm = m_Frm 'pass Form Object
                Set T.p_txt = ctl 'pass the TextBox control

                    T.p_txt.AfterUpdate = Evented ' enable AfterUpdate Event
                    T.p_txt.OnLostFocus = Evented ' enable OnLostFocus Event
                
          'Add ClsTxt Class Object as Dictionary Object Item
                    D.Add ctl.Name, T 'TextBox Name as ItemKey, Class Instance as Item
                Set T = Nothing 'erase ClsText instance
        End Select

Similarly, both the Command buttons have only one common Event, the Click Event. Hence, their Code can also be combined into a single step, like the following code segment:

Select Case ctl.Name
            Case "Command8", "Command9"
                Set B = New ClsCmdButton 'create new instance of ClsCmdButton
                Set B.p_Btn = ctl 'pass CommandButton control to Class Module
           
                    B.p_Btn.OnClick = Evented 'Enable Click Event
            'Add ClsCmdButton Class Instance as Dictionary Item
                    D.Add ctl.Name, B ' Command Button Name as ItemKey, Class Instance as Item
                Set B = Nothing 'erase ClsCmdBtn instance
        End Select

The Revised ClsTxtBtn_Dictionary Code.

The full Class Module Code with the above change is given below:

Option Compare Database Option Explicit Private T As ClsText Private B As ClsCmdButton Private m_Frm As Access.Form Private D As Object Public Property Get mfrm() As Access.Form Set mfrm = m_Frm End Property Public Property Set mfrm(ByRef frmObj As Access.Form) Set m_Frm = frmObj init_Class End Property Private Sub init_Class() Dim ctl As Control Const Evented = "[Event Procedure]"

Set D = CreateObject("Scripting.Dictionary")

For Each ctl In m_Frm.Controls Select Case TypeName(ctl) 'Type name TextBox or CommandButton ? Case "TextBox" Select Case ctl.Name Case "Text2", "Text4", "Text6" Set T = New ClsText 'create new instance of Class Module Set T.p_frm = m_Frm 'pass Form Object Set T.p_txt = ctl 'pass the TextBox control T.p_txt.AfterUpdate = Evented ' enable AfterUpdate Event T.p_txt.OnLostFocus = Evented ' enable OnLostFocus Event 'Add ClsTxt Class Object as Dictionary Object Item D.Add ctl.Name, T 'TextBox Name as ItemKey, Class Instance as Item Set T = Nothing 'erase ClsText instance End Select Case "CommandButton" Select Case ctl.Name 'Both Command Buttons have only the same Click Event Case "Command8", "Command9" Set B = New ClsCmdButton 'create new instance of ClsCmdButton Set B.p_Btn = ctl 'pass CommandButton control to Class Module B.p_Btn.OnClick = Evented 'Enable Click Event 'Add ClsCmdButton Class Instance as Dictionary Item D.Add ctl.Name, B ' Command Button Name as ItemKey, Class Instance as Item Set B = Nothing 'erase ClsCmdBtn instance End Select End Select Next End Sub

You may now create a new Class Module, paste the code provided earlier, and save it. Next, update the Form Module code to reference this new module name, and test the form controls to verify that they function as before.

So far, we have worked with only two types of controls—TextBox and CommandButton—using their respective class modules (ClsText and ClsCmdButton). But what if we need to handle all the types of controls on a form, such as ComboBoxes, ListBoxes, Tab Controls, and Option Groups? Each of these would require its own dedicated Class Module, similar to ClsText and ClsCmdButton.

We will conclude this topic with one or two more posts. We will create a sample database to demonstrate the Structured VBA Event Procedure reusable coding in a Standalone Class Module, rather than duplicating Subroutines for the same Events from the same type of controls in the Form Module. We will incorporate almost all Types of commonly used controls on a Form, and demonstrate how to handle their events through class modules.

If you have followed the earlier posts (links numbered 1 to 12 listed below), you should have no difficulty defining and configuring class modules for these additional controls in a similar way. Their Click events can then be enabled, making them ready for testing on the form.

Links to 'WithEvents' Coding Tutorial Pages.

WithEvents TexBox and CommandButton Control Arrays

a.p.r. pillaiJuly 18, 2019WithEvents 5 comments

Textbox and CommandButton Arrays.

I hope you’ve gone through the last three posts on Report-based Event Trapping in Class Modules and on modifying Report controls in real-time.

For your convenience, the links to those posts are provided below for quick reference, if needed:

So far, we have worked exclusively with TextBox arrays to capture native events in a Standalone Class Module and execute Event Procedures.

The TextBox Events Capturing Route Map.

Create a Base Class Module that defines Text Box Control and Form properties.
Add AfterUpdate() and LostFocus() subroutines to this class to handle these built-in events from the text boxes on the form. Inside each subroutine, identify the text box that triggered the event and execute the corresponding code based on its name.
Next, create a Derived Class Module using this Text Box class as the base class.
Within the derived class, create an array of base-class objects so that each element can handle the required events for a specific text box. This approach eliminates the need to keep empty AfterUpdate() and LostFocus() subroutine stubs on the form’s class module, which are otherwise required just to raise these events.
Because the base class contains only a single txt property (which can hold a reference to only one text box at a time), having multiple class objects—one per text box—is essential.
Instead of using an array of class objects, a Collection object can be used to store all the individual text box class instances. This method has proven to be a more flexible and reliable way to manage multiple text box class objects.

In brief, our idea is to create a dedicated Class Module Object for each control type—such as Text Box, Combo Box, List Box, Tab Control, and Option Group—each containing the required event procedures specific to that control.

In the Derived Class Module, we then include these control-specific class objects as properties (Base Class properties), depending on which control types are present on the form.

One important point to remember:

It is absolutely necessary to reference a different control directly from the Class Module Object (other than the one that triggered the event). For that, we must add a Form object property also to the Base Class Module and assign the current Form object to it through the Derived Class Module. This allows the class to access and update/retrieve values from other controls on the same form or Subforms as needed.

The TextBox and CommandButton Arrays.

So far, we have worked with arrays of class modules containing only the TextBox property.

Now, we will create a few TextBoxes and Command Buttons on a form and explore how their object references can be assigned to instances of the TextBox and Command Button Base Class module arrays within a Derived Class module.

Each array element of a class object corresponds to a specific control (either a TextBox or a Command Button) and is responsible for capturing that control’s built-in events and executing the associated event procedures from within the corresponding array element.

A sample image of the form named frmClassArray in Design View is shown below:

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

There are three Text Box controls on the form named Text2, Text4, and Text6. Each of these will be represented by individual single-dimensional array elements (three elements in total) within the TextBox base class module. Similarly, there are two Command Button controls named Command8 and Command9, which will be represented as a two-element array within the Command Button base class module.

Class Module for TextBox: ClsText.

The Class Module: ClsText Code for Text Box control is given below:

Option Compare Database
Option Explicit

Private frm As Access.Form
Private WithEvents txt As Access.TextBox

Public Property Get p_Frm() As Access.Form
   Set p_Frm = frm
End Property

Public Property Set p_Frm(ByRef pNewValue As Access.Form)
   Set frm = pNewValue
End Property

Public Property Get p_txt() As Access.TextBox
   Set p_txt = txt
End Property

Public Property Set p_txt(ByRef ptxtValue As Access.TextBox)
   Set txt = ptxtValue
End Property

Private Sub txt_AfterUpdate() 'Capture AfterUpdate Event here
Dim txtName As String, varVal As Variant
Dim msg As String

txtName = txt.Name 'save TextBox name
msg = txtName 'save the same name in msg variable for msgbox
varVal = txt.Value 'read the value, if any, from TextBox

'write required code under textbox name
Select Case txtName
    Case "Text2"
        'Code
    Case "Text4"
        'code
    Case "Text6"
        'Code
End Select
'a common message displays for
'all TextBoxes for demo purposes.
msg = msg & ": " & varVal
MsgBox msg, , "AfterUpdate()"

End Sub

Private Sub txt_LostFocus() 'LostFocus actions from TextBoxes are captured here.
Dim txtName As String, varVal As Variant
Dim msg As String, strVal As String
Dim ctl As Control

txtName = txt.Name
varVal = txt.Value
strVal = Nz(varVal, "")

'if any textbox is empty
'then inserts the text: MSAccessTips.com
Select Case txtName
    Case "Text2" ' inserts MSAccessTips.com when empty.
        GoSub Check
    Case "Text4"
        GoSub Check
    Case "Text6"
        GoSub Check
End Select

Exit Sub

Check:
   If Len(strVal) = 0 Then
     txt.Value = "MsAccessTips.com"
   End If
Return

End Sub

The form-level property variable frm is declared at the beginning of the class module, followed by the Access.TextBox property txt. The txt property is declared using the WithEvents keyword, which allows the class to capture both built-in and user-defined events triggered on the form.

The frm object property serves as a reference to any TextBox control on the form other than the one that triggered the event, if needed. Both properties are declared with Private scope to prevent direct access from outside the class module.

Next, Get and Set property procedures — p_Frm() for the frm property and p_txt() for the txt property — are implemented. The active form object is assigned to the frm property during the Form_Load() event, not directly, but through the derived class module ClsTxtBtn_Derived.

The p_txt() property procedures are invoked from the derived class module (ClsTxtBtn_Derived—the VBA code for this module is provided after the ClsCmdButton class module code). These procedures assign each TextBox control on the form to the corresponding element in the ClsTxt Class Module array.

Once the frm property is assigned the active Form object, it becomes possible to reference any control on the form to read or write its values.

Within the event-capturing subroutines, you can also reference a different TextBox control on the same form to read or write its value using the following approach:

'Assume Text2 is the active control now
Set mytxt = frm.Controls("Text6")

'Read value from mytxt
    mytxtvalue = mytxt.Value

'Write value to mytxt
    mytxt = 25

The txt_AfterUpdate() subroutine handles the events for Text2, Text4, and Text6 individually, corresponding to their respective array elements. You can add validation checks on the TextBox values, display messages, replace values, or perform other actions. In this sample subroutine, a generic message is displayed indicating the name of the TextBox and confirming that the subroutine executed upon the event being triggered on the form.

The txt_LostFocus() event procedure automatically inserts the text "msaccesstips.com" if the TextBox is left empty.

Note: During the demo run of the attached database, you can tab through the text fields to automatically insert the default text. Editing a field and pressing Tab or Enter will trigger the AfterUpdate() event.

Class Module of Command Button: ClsCmdButton.

The Command Button Class Module: ClsCmdButton VBA Code is given below:

Option Compare Database
Option Explicit

Private WithEvents Btn As Access.CommandButton

Public Property Get p_Btn() As Access.CommandButton
  Set p_Btn = Btn
End Property

Public Property Set p_Btn(pBtnValue As Access.CommandButton)
   Set Btn = pBtnValue
End Property

Private Sub Btn_Click()
Dim BtnName As String

BtnName = Btn.Name

Select Case BtnName
    Case "Command8"
        DoCmd.OpenForm "Form1", acNormal
    Case "Command9"
        MsgBox "Thank you, " & BtnName & " Clicked.", , BtnName
End Select

End Sub

The Btn property is declared with Private scope and the WithEvents keyword to capture events triggered by the Command Button on the form.

The Get and Set Property Procedures (p_Btn()) are used to assign or retrieve the Command Button object to/from the property.

Within the Btn_Click() event procedure, the name of the clicked Command Button is checked to determine the corresponding action:

Command8: Opens the form Form1.
Command9: Displays a message.

The Derived Class Module: ClsTxtBtn_Derived

In this example, we will monitor and capture the enabled built-in events from both TextBoxes and Command Buttons on the form, and execute their corresponding event procedures within the respective Class Module object instances.

To implement this, we use a TextBox and a CommandButton Class Module as base classes. Both of these base classes are incorporated into the Derived Class Module ClsTxtBtn_Derived, which manages the assignment of form controls to class instances and handles event execution seamlessly.

This approach keeps the form’s module clean while allowing the centralized, reusable code for handling multiple control events.

The derived Class Module's VBA Code is given below:

Option Compare Database
Option Explicit

Private T() As New ClsText
Private B() As New ClsCmdButton
Private m_Frm As Access.Form

Public Property Get mfrm() As Access.Form
  Set mfrm = m_Frm
End Property

Public Property Set mfrm(ByRef frmObj As Access.Form)
  Set m_Frm = frmObj
  init_Class
End Property

Private Sub init_Class()
   Dim ctl As Control
   Dim tCount As Long
   Dim bCount As Long
Const Evented = "[Event Procedure]"

tCount = 0 'counter for textbox controls
bCount = 0 'counter for Command Button controls
For Each ctl In m_Frm.Controls
   Select Case TypeName(ctl) 'Type name TextBox or CommandButton ?
     Case "TextBox"
         tCount = tCount + 1 'increment counter
         ReDim Preserve T(1 To tCount) 'redimension TextBox Class Objecct Array
         Set T(tCount).p_Frm = m_Frm 'pass Form Object
         Set T(tCount).p_txt = ctl 'pass the TextBox control
         
         Select Case ctl.Name
            Case "Text2" ' enable AfterUpdate and OnLostFocus Events
                T(tCount).p_txt.AfterUpdate = Evented
                T(tCount).p_txt.OnLostFocus = Evented
            Case "Text4"
                T(tCount).p_txt.AfterUpdate = Evented
                T(tCount).p_txt.OnLostFocus = Evented
            Case "Text6"
                T(tCount).p_txt.AfterUpdate = Evented
                T(tCount).p_txt.OnLostFocus = Evented
        End Select
            
      Case "CommandButton"
         bCount = bCount + 1 'increment counter for CommandButton
         ReDim Preserve B(1 To bCount) 'redimension Button Class Object Array
         Set B(bCount).p_Btn = ctl 'pass CommandButton control
        
        Select Case ctl.Name
            Case "Command8" 'Enable Click Event
                B(bCount).p_Btn.OnClick = Evented
            Case "Command9"
                B(bCount).p_Btn.OnClick = Evented
        End Select
      End Select
    Next
End Sub

The TextBox Class Module ClsText is instantiated as an array with a dynamically determined number of elements. Similarly, the ClsCmdButton Class Module is declared in the next line.

The Form object m_frm is declared to receive the active form reference passed from the Form_Load() event procedure. It acts as an intermediary, providing the reference to each array element of the ClsText class object.

The Get and Set property procedures mFrm() control the retrieval and assignment of the m_frm form object.

Within the Set property procedure, the Class_Init() subroutine (distinct from the built-in Class_Initialize()) is called to create separate class object arrays for each TextBox and Command Button on the form.

A single Control (ctl) object and two counter variables—tcount for TextBoxes and bcount for Command Buttons—are used as array indexes during the re-dimensioning of the ClsText and ClsCmdButton class object arrays.

The constant Evented variable is assigned the string "[Event Procedure]".

Next, both counter variables—tcount for TextBoxes and bcount for Command Buttons—are initialized to zero.

A For Each ... Next loop iterates through all controls on the form and checks each control's type using the TypeName(ctl) function. If the Control is a TextBox, the tcount variable is incremented by one.

The TextBox class module array T is then re-dimensioned to accommodate the new element while preserving existing data, using:

ReDim Preserve T(1 To tcount)

Note: In this ReDim Preserve statement, we do not specify the class type (e.g., As ClsText) as we would with normal variables like:

ReDim Preserve Qty(1 To cnt) As Single

This is because arrays of objects in VBA can only declare the object type when initially dimensioned, not during ReDim Preserve.

Next, the Form object reference is assigned to the TextBox class module property for the current array element:

Set T(tcount).p_Frm = m_Frm

This ensures that each instance of the class has access to the form.

Then, the TextBox control reference is passed to the same array element:

Set T(tcount).p_txt = ctl

At this stage, we check the TextBox name to enable the required built-in Events. Different TextBoxes may require different Events depending on the purpose of that data field.

For demonstration purposes, we apply a blanket rule and enable both the AfterUpdate and LostFocus Events for all TextBoxes.

Hence, the following statements are added for every TextBox control:

' Example statements to enable Events

T(tCount).p_txt.AfterUpdate = Evented

T(tCount).p_txt.OnLostFocus = Evented

When a Command Button is encountered, its counter variable bcount is incremented by one. The Command Button class array B is then re-dimensioned to 1 to bcount, preserving any previous array elements containing data.

Unlike the TextBox class, the Command Button class module does not require a Form property.

The current Command Button control reference is assigned to the bcount-th element of the array:

Set B(bcount).p_Btn = ctl

Next, the Command Button name is checked to enable the required events. For demonstration purposes, the Click event is enabled for each Command Button.

Note: If all Command Buttons only require the Click event, there is no need to check individual names. Simply assigning the Click event immediately after setting the control reference is sufficient:

B(bcount).p_Btn.OnClick = "[Event Procedure]"

However, the detailed name-based checks are included here for clarity.

This process is repeated for all TextBoxes and Command Buttons on the form. Other controls, such as Labels or non-interactive controls, are ignored.

The Form frmClassArray Class Module Code.

Option Compare Database
Option Explicit

Private D As New ClsTxtBtn_Derived

Private Sub Form_Load()
   Set D.mfrm = Me
End Sub

In the Form_Load() event procedure, the current Form object is passed to the D.mFrm() property procedure of the derived class object ClsTxtBtn_Derived. The derived class then copies this Form object reference to each element of the ClsText class array, ensuring that every TextBox class instance has access to the parent form for reading or writing values as needed.

Downloads

You can download the demo database from the links below to explore and study the code in detail. To follow the execution step by step, run the code in Debug mode by pressing F8, which allows you to step through each line and observe how the Class Module objects handle the form controls’ events.

Download TextandButton2007.zip

Download TextandButton2003.zip

Links to WithEvents ...Tutorials.

WithEvents and Report Line Highlighting

a.p.r. pillaiJune 21, 2019WithEvents No comments

Draw a Circle Around Marks within a TextBox.

This article is essentially a revisit of an earlier post titled “Highlighting Reports” (August 2007). In that earlier version, the entire code resided within the Report’s Class Module.

The key change here is that we are moving all the code from the Report’s Class Module into a standalone Class Module, thereby freeing up the Report’s own module. The Print event of the Report’s Detail section is now captured within the Class Module object, which executes the code to highlight the desired report line item.

If you have experimented with the sample reports included in the demo databases from the previous two posts. You will find that much of the code used here (in the Class Module) is already familiar to you.

If you have not yet seen those articles, their links are provided below:

WithEvents and Access Report Event Sink.
WithEvents and Report Lines Hiding.

The Highlight of this Project.

The OnPrint() event procedure of the Report’s Detail section draws an oval-shaped outline around the Text Box that displays the exam marks retrieved from the Students table.

If a student fails to achieve the minimum passing mark of 60%, the corresponding marks are visually highlighted by enclosing them within this oval-shaped outline.

A sample image from the Report’s Print Preview demonstrating this feature is shown below:

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

Even though the report design is quite simple, I would like to draw your attention specifically to the Text Box that displays the marks, where we dynamically draw the oval shape to highlight the values.

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

The oval shape is drawn within the boundaries of the Total Text Box. However, the size of this Text Box should be chosen carefully—it should neither be too wide (like the Remarks label) nor too narrow. Although the shape can still be drawn within any-sized Text Box, if the size is inappropriate, parts of the oval may overlap the text or fail to align properly around the marks.

The Class Module.

More details on this when we review the Class Module Code.

The Class Module: ClsStudentHighlight Code is given below:

Option Compare Database
Option Explicit

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

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

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

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

On Error GoTo mRpt_Err

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

  Set txt = Rpt.Controls("Total")
  Set max = Rpt.Controls("Maxmarks")
  Set pct = Rpt.Controls("Percentage")
  Set lgnd = Rpt.Controls("Legend")

mRpt_Exit:
Exit Property

mRpt_Err:
MsgBox Err.Description, , "mRpt()"
Resume mRpt_Exit

End Property

Private Sub secRpt_Print(cancel As Integer, PrintCount As Integer)
'  Draw ellipse around controls that meet specified criteria.

Dim m_max As Double
Dim m_pct As Double
Dim curval As Double
Dim pf As Double
Dim pp As Double
Dim yn As Boolean

On Error GoTo secRpt_Print_Err

m_max = max.Value 'read Maxmarks TextBox Value
pp = pct.Value 'read Pass Percentage TextBox value

curval = Nz(txt.Value, 0) 'read obtained marks from Total TextBox
pf = Int(curval / m_max * 100 ^ 2) / 100 'calculate obtained marks percentage
yn = (pf >= pp) 'Passed or Not (TRUE/FALSE)

'call the DrawCircle Subroutine with Pass/Fail flag
'and the Control as parameters
Call DrawCircle(yn, txt)

secRpt_Print_Exit:
Exit Sub

secRpt_Print_Err:
MsgBox Err.Description, , "secRpt_Print"
Resume secRpt_Print_Exit

End Sub

Private Sub secFutr_Print(cancel As Integer, PrintCount As Integer)
Dim y As Boolean, lbl As Control

On Error GoTo secFutr_Print_Err

y = False 'set the flag false to draw oval shape
Set lbl = lgnd 'pass label control in Page Footer
Call DrawCircle(y, lbl) 'draw circle in legend label

secFutr_Print_Exit:
Exit Sub

secFutr_Print_Err:
MsgBox Err.Description, , "secFutr_Print"
Resume secFutr_Print_Exit

End Sub

Private Sub DrawCircle(ByVal bool As Boolean, ovlCtl As Control)
Dim ctl As Control
Dim bolPrintCircle As Boolean
Dim sngAspect As Single
Dim intShapeHeight As Integer
Dim intShapeWidth As Integer
Dim sngXCoord As Single
Dim sngYCoord As Single

On Error GoTo DrawCircle_Err

If bool Then 'if pass no highlighting, change logic for pass cases
    bolPrintCircle = False
Else 'highlight failed cases
    bolPrintCircle = True
End If

Set ctl = ovlCtl
        
    If Not IsNull(ctl) Then
        If bolPrintCircle Then
           ' change this value to adjust the oval shape of the circle.
            sngAspect = 0.25
   
            ' Determine coordinates of ctl and to draw ellipse.
            ' Determine height and width of ellipse.
            intShapeHeight = ctl.Height
            intShapeWidth = ctl.Width
    
            'calculate circle vertical Y coordinate
            sngYCoord = ctl.Top + (intShapeHeight \ 2)

            'calculate horizontal X coordinate of circile
            sngXCoord = ctl.Left + (intShapeWidth \ 2)
            
            'draw an ellipse around the Total TextBox
            Rpt.Circle (sngXCoord, sngYCoord), intShapeWidth \ 2, RGB(255, 0, 0), , , sngAspect
          bolPrintCircle = False
        End If
    End If


DrawCircle_Exit:
Exit Sub

DrawCircle_Err:
MsgBox Err.Description, , "DrawCircle()"
Resume DrawCircle_Exit

End Sub

In the Class Module’s property declarations, the first line defines the Report object in the Rpt variable.

The next two lines declare the Detail and Footer sections of the Report as secRpt and secFutr objects, respectively.

Following that, three TextBox objects—txt, max, and pct—are declared to access values from their corresponding controls on the Report.

Finally, a Label control is declared to function as a legend symbol. This label, together with another label caption: “Not Successful,” will serve as a visual indicator explaining the meaning of the oval symbol drawn around a student’s marks.

The Property Get procedure is not actually required in this module and is included only for completeness, as the Rpt object is never accessed from outside the module.

The Property Set procedure receives the current Report object as a parameter from the Report’s Open (or Load) event procedure and assigns it to the Rpt object.

After this assignment, the Detail and Footer sections of the report are assigned to the secRpt and secFutr properties, respectively. These assignments also enable their Print events to be captured when they are triggered on the report.

Finally, the next three lines in the code assign the report’s TextBox controls to the txt, max, and pct properties declared in the module’s global area.

There is an empty Label control named Legend placed in the Report Footer section. A Label property named lbl is declared in the global area of the Class Module. Within the Set property procedure, the Legend label is assigned to the lbl property using the statement:

Set lbl = Rpt.Controls("Legend").

The Class Module contains three subroutines:

secRpt_Print() — triggered by the Print event of the Detail section.
secFutr_Print() — triggered by the Print event of the Report Footer section.
DrawCircle() — a shared routine called from both of the above subroutines to draw an oval (ellipse) shape around certain Total text boxes in the Detail section, and around the Legend label in the Footer section.

The Report Detail Section Print Event

When the Report’s Detail section begins printing (in Print Preview mode, not in Report View), the Print event is triggered, and the secRpt_Print() subroutine captures this event and starts executing the code.

The values from the TextBox properties (max, pct, and txt) are read into the local variables m_max, pp, and curval, respectively. The student’s percentage of marks is then calculated with two decimal places and compared against the pass percentage (pp). Based on this comparison, the result—Passed (TRUE) or Not Successful (FALSE)—is stored in the Boolean variable yn.

Finally, the DrawCircle() subroutine is called, passing yn as the first parameter and the Total TextBox control as the second parameter.

The DrawCircle() Sub-Routine.

The DrawCircle() subroutine first checks whether the Boolean value received as its first parameter is TRUE or FALSE. Based on this, a local Boolean variable named bolPrintCircle is set accordingly. This variable is a flag to signal whether the circle-drawing code segment should execute or be skipped.

In this sample demo, the focus is on highlighting the marks of students not successful. When a student’s calculated percentage is below 60%, the yn flag is set to FALSE. Consequently, when yn is FALSE, bolPrintCircle is set to TRUE, instructing the routine to draw an oval shape around those marks.

The TextBox’s positional values—Left, Top, and its Width and Height—are then used to calculate the center point (horizontal and vertical coordinates) of the circle. The radius of the circle is determined as half the width of the TextBox.

If the TextBox is too wide, the circle drawn within it will appear distorted—the top and bottom parts may be cut off, while the left and right edges will look like two separate arcs. To fix this, you need to reduce the vertical radius of the circle relative to the horizontal radius calculated from the TextBox width.

This adjustment is done by setting the circle's aspect ratio. For example, by setting

sngAspect = 0.25, the vertical radius becomes one-fourth of the horizontal radius, producing a neat oval shape around the TextBox value instead of a distorted circle.

Aligning Text inside the Text Box

The TextBox value is horizontally centered within the control. However, vertically, the text usually appears near the top edge of the TextBox (and therefore close to the top edge of the circle as well). To visually center the value vertically inside the oval shape, the Top Margin is manually set to 0.1 cm in Design View. This property can only be adjusted at design time, not through code.

In the Report Footer Section, there is a label control named Legend. During the Report_Footer_Print() event, the DrawCircle() subroutine is called with this label control as a parameter to draw an oval shape inside it. Another label control with the caption “Not Successful” is placed alongside, serving as a legend to explain the meaning of the oval shape drawn around the marks of students who did not achieve passing scores.

Report Module Code

Option Compare Database
Option Explicit

Private R As New ClsStudentHighlight

Private Sub Report_Load()
  Set R.mRpt = Me
End Sub

The ClsStudentHighlight Class Module is instantiated in Object R.

On the Report_Load() Event Procedure, the current Report Object is assigned to the Property R.mRpt.

Download the Demo database from the link given below and try out the Report and Code.

Download MarksHighlight2007.zip

Download MarksHighligh2003.zip

Links to WithEvents ...Tutorials.

WithEvents and Report Line Hiding

a.p.r. pillaiJune 15, 2019WithEvents 5 comments

Report Event Handling.

If you have already tried last week’s Report OnPrint() event trapping in the Access Class Module, this next step will be easier to follow. In that earlier example, we captured the Report’s Detail Section OnPrint event inside a Class Module, validated the field values, and highlighted the TextBox and marks of students who passed their exams. We will do something similar here as well.

However, if our goal is only to highlight Text Boxes on the Report with colors or apply styles like Bold, Italic, or Underline, then we don’t need to capture the Print event in a Class Module. This can be done more simply using Conditional Formatting.

For example, in the Conditional Formatting dialog box, we can use an expression like:

Expression Is: [Total]/[MaxMarks]*100 >= [Percentage]

This will highlight the qualifying students’ marks automatically, based on the percentage threshold.

But Conditional Formatting has its limitations. It cannot perform tasks like changing the font type, adjusting the font size, or highlighting the border of the Text Box. These advanced styling changes can be done through event handling in the Class Module.

In the previous example, we captured the Detail Section OnPrint() event. This time, we will try out the OnFormat() built-in event to hide certain report lines, leaving only the required ones (either the passed or failed students) visible on the Report.

We will reuse the same Report from last week’s example to generate either a Passed-Students-List or a Failed-Students-List from the same report.

Sample Images of Report View, Print Previews

1. Full List of Students in Report View (not in Print Preview)

The complete list of students is shown below in Report View.

Keep in mind that while the formatting and printing processes do occur internally in Report View, the built-in Report events (such as OnFormat and OnPrint) are not triggered in this mode. These events are fired only when the report is opened in Print Preview mode.

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

2. The Passed Students' List in Print Preview is achieved by preventing the failed students' report lines from appearing on the Report.

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

3. Failed Students' List in Print Preview and Passed Students report lines will not appear on the Report.

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

Report options 2 and 3 are prepared without applying any filtering condition directly on the source data, but showing or hiding the report lines in the Detail Section's Format Event does the job.

Class Module: ClsStudentsList VBA Code

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

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

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

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

  Set Rpt = RptNewVal

  With Rpt
     Set secRpt = .Section(acDetail)
     secRpt.OnFormat = strEvent
  End With
  
  msg = "1. Passed List" & vbCr & "2. Failed List"
  i = 0
  Do While i < 1 Or i > 2
    i = Val(InputBox(msg, "Report Options", 1))
  Loop

  Set txt = Rpt.Controls("Total")
  Set max = Rpt.Controls("MaxMarks")
  Set pct = Rpt.Controls("Percentage")
End Property

Private Sub secRpt_Format(Cancel As Integer, FormatCount As Integer)
Dim curval As Double
Dim m_Max As Double
Dim m_pass As Double
Dim mk As Double
Dim pf As Double
Dim yn As Boolean
Dim lbl As Access.Label

On Error GoTo secRpt_Print_Err

m_Max = max.Value
pp = pct.Value
curval = txt.Value

pf = curval / m_Max * 100

yn = (pf >= pp)

Set lbl = Rpt.Controls("lblpass")

secRpt.Visible = False
       
       If yn Then
            txt.FontBold = True
            txt.FontSize = 12
            txt.BorderStyle = 1
            lbl.Caption = "Passed"
            lbl.ForeColor = RGB(0, FF, 0)
            lbl.FontBold = True
                If i = 1 Then
                    secRpt.Visible = True
                End If
        Else
            txt.FontBold = False
            txt.FontSize = 9
            txt.BorderStyle = 0
            lbl.Caption = "Failed"
            lbl.FontBold = False
                If i = 2 Then
                    secRpt.Visible = True
                End If
        End If

secRpt_Print_Exit:
Exit Sub

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

In the global declaration area of the Class Module, two key properties are declared: the Report property Rpt and the Report Section property secRpt.

Next, three Text Box control properties—txt, max, and pct—are declared. These will later be assigned references to their corresponding Text Box controls on the Report. Through these references, we can read the student’s marks (txt), the maximum marks (max, representing the total marks for all subjects), and the pass percentage (pct). These values are used to calculate each student’s percentage score and categorize them as either Passed or Failed.

An Integer-type property i is also declared to hold the Report option chosen by the user at runtime:

1 for Passed Students List
2 for Failed Students List

Set mRpt(): The Property procedure assigns the active Report object to the Rpt property when called from the Report_Open() event. It also passes the user-selected report option (RptNewVal) into the Rpt object.

Immediately after assigning the Rpt object, the Report Detail Section is assigned to the secRpt property, and the Detail Section’s OnFormat event is enabled within the same Set mRpt() procedure. This allows the Class Module to capture the OnFormat event and apply formatting logic line by line during the report’s generation.

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

We cannot use last week’s OnPrint() event to hide specific report lines because the Print event occurs after the Formatting phase has been completed. Once the report enters the printing stage, it is too late to selectively hide individual lines. If we attempt to execute secRpt.Visible = False at this point, it will hide the entire Detail Section altogether, rather than just the specific lines we want to remove.

Report Events before Viewing/Printing.

During the first pass of report formatting, the Format event is triggered. At this stage, Access determines which data will appear on the report page, and our programmed logic and conditional actions are executed.
In the second formatting pass, the selected report lines are prepared and laid out for printing or previewing.
The Print event fires immediately after the second formatting pass, just before the formatted report lines are printed on the Detail Section.

Inside the Do While ... Loop, an InputBox() statement is used to obtain the user’s choice for the report option:

1 – Passed Students List
2 – Failed Students List

The user must enter either 1 or 2. Any other value is rejected, and the Do While...Loop continues prompting until a valid input is received and stored in the variable i.

After receiving a valid option, the next three statements assign the Report’s TextBox controls to their respective declared Properties in the Class Module.

When the report is opened in Print Preview mode, the Detail Section’s Format event is triggered and captured in the secRpt_Format() subroutine of the ClsStudentLine Class Module. A few local variables are declared at the start of this subroutine.

The Maximum Marks and Pass Percentage values are read from their respective TextBox controls and assigned to the max and pct properties.
The statement curval = txt.value retrieves each student’s total marks and assigns them to the curval variable.

Next, the statement pf = curval / m_Max * 100 calculates the percentage of marks obtained by the student.

The statement yn = (pf >= pp) compares the student’s obtained percentage (pf) with the Pass Percentage (pp).

If the obtained percentage is greater than or equal to the pass percentage, yn = TRUE (Student Passed).
Otherwise, yn = FALSE (student failed).

The lbl property is assigned to the Label control that appears to the right of the Total Marks TextBox on the Report.

Initially, the Report Detail Section is kept hidden. When a student is found to be in the Passed category (yn = TRUE), the Total Marks TextBox is formatted (highlighted), and the Label control’s Caption is set to "Passed".

If yn = FALSE, the formatting is reset to normal, and the Label Caption is set to "Failed", depending on which Report option the user has selected.

The user’s choice is obtained from the statement i = InputBox() within the Do While...Loop, which prompts for one of the two options:

1 – Passed Students List
2 – Failed Students List.

How it works.

Option 1:

If the student in the current line of the Detail Section is found to have passed the exam, the Detail Section is made visible, allowing that report line to appear on the Report. This check is performed for each line of the Report, and only the passed students’ lines are displayed.

Option 2:

If Option 2 is selected, the Detail Section is made visible only for failed students’ data lines, while all other lines are hidden from the Report.

Report Class Module Code.

The Report Class Module Code is given below:

Option Compare Database
Option Explicit

Private R As New ClsStudentsList

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

In the Report’s Code Module, the ClsStudentList Class Module is instantiated as the object R. In the Report_Open() event procedure, the current Report object (Me) is passed to the class object through its R.mRpt() Set property procedure. These are the only lines of code required within the Report’s own Class Module.

All other operations are handled internally, behind the scenes, by the ClsStudentList Class Module.

Note: Always open the report in Print Preview mode (not in Report View) to ensure that the Format event in the Detail section is triggered.

Download the Demo Database from the Link given below and try out the Report and Code.

Download StudentsList2007.zip

Download StudentsList2003.zip

Links to WithEvents ...Tutorials.

WithEvents and Access Report Event Sink

a.p.r. pillaiJune 08, 2019WithEvents No comments

Access Report and Print Format Events.

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, and added them as items of the Collection object.

We observed that both approaches—arrays and collection items—work effectively for 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 with a few student names and 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 explains 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 input 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—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 ensure 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.