Several TextBoxes and Event Handling - Part Five

 Continued from the last Episode - Part Four.

Several TextBoxes and Their Event Handling.

Writing Form Module VBA Code in a Standalone Class Module.

Earlier Episode Links:

1. Re-using Form Module VBA Coding for New Projects.
2. Defining Custom Events in Microsoft Access Part Two
3. Objects and Their Built-in Events Part 3.
4. Standalone Class Module and Events - Part Four

Readers still need to go through the earlier posts on this topic. Please read them before continuing.

So far, we have worked with a single TextBox control on a Form and explored several techniques for handling its events by writing VBA code in a standalone Class Module rather than in the Form Module. We are now familiar with the concepts of defining events, raising events to signal specific actions, and declaring a TextBox object instance with the `WithEvents` keyword. This declaration enables the Class Module to capture and process events raised by the TextBox control on the Form.

Next, let us examine how to handle events for multiple TextBox controls on a Form. For this exercise, we will place three TextBox controls on the Form and use them to explore event handling through a standalone Class Module.

In practical applications, a Form may contain 10, 20, or even more TextBox controls. While it is unlikely that every control will require event procedures, this approach can be extended to handle as many controls as necessary when the application design demands it.

Each TextBox control provides a large collection of built-in events. Although not all of these events will be relevant in every application, many of them may be required depending on the functional requirements. Therefore, it is important to understand how to manage multiple control events efficiently.

To begin, we will work with three TextBox controls and implement a few of their event procedures. Initially, we will use a straightforward and easy-to-understand approach. While this method may not be the most sophisticated or reusable solution, it provides a solid foundation for understanding the underlying concepts. Once these fundamentals are clear, we can progressively refine the design into a more efficient, scalable, and reusable event-handling framework.

Demo Form Design.

Create a new Form with the following design in your database:

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

1. Create a new Form and insert 3 unbound TextBoxes, one below the other, on the Detail Section of the Form.  

2. Insert a Label control above the TextBoxes and write the description as shown in the Form Image given above, in its Caption Property.

3. Select the first TextBox and display the Property Sheet. Change the Name Property Value to Quantity and change its Child-label caption to Quantity(1-10).

4. Select the second TextBox, change its Name Property Value to UnitPrice, and change the child-label Caption to Unit Price.

5. Change the Name Property value of the third TextBox to TotalPrice and enter Total Price in the child-label Caption.

6. Display the VBA Code Module of the Form.

7. Copy and paste the following VBA Code into the Form Module, overwriting existing lines of Code, if any:

   Option Compare Database
   Option Explicit
   
   Private C As New Class3_1 'create an instance of Class3_1 Class Object
   
   Private Sub Form_Load()
     Set C.m_Frm = Me 'Pass current Form Object to C.m_Frm Property Procedure
   End Sub
   
   Private Sub Form_Unload(Cancel As Integer)
       Set C = Nothing 'Release the Class Object instance from memory
   End Sub
   

8. Save the Form with the name Form1_RaiseEvent_4

We made some changes in the earlier Global Class Object declaration C to create a Class Module Class3_1 object Instance. This is the short form (Private C As New Class3_1) of the following two-line statements we used in earlier cases:

Private C As Class3_1 'declare an instance of Class3_1 Class Object

Private Sub Form_Load()
  Set C = New Class3_1 'Instantiate Class3_1 Class
End Sub

The New keyword is used in the class object declaration itself to create an instance of the Class Object in memory. The current Form Object Me is passed to the SET Property Procedure C.m_Frm() as a Parameter in the Form_Load() Event Procedure. You will see the m_Frm() Property Procedure when we create the Class3_1 Class Module Code. The Class Object declaration in the Global area alone will not load the Class Object into memory.  We instantiate the class object with the keyword New to load an instance of the class object into memory. When we open a Form, its Class Module is also loaded into memory when the Form's Has Module Property is set to Yes.

The Form_Unload() Event Procedure removes the Class Object Instance C from memory and releases the memory for other uses when the Form is closed.

The New Class Module: Class3_1.

As we progress, we will adopt a more structured naming convention for our Class Modules so that their purpose, ownership, or associated object type can be identified immediately from their names. For now, however, our primary focus is on understanding the underlying concepts. At this stage of the learning process, mastering the fundamentals is far more important than refining naming conventions and other organizational details.

Create a new Class Module, display its Property Sheet (Select Properties Window from the View Menu), and change its Name Property Value to Class3_1.

Copy and Paste the following VBA Code into the Class Module and Save the Module:

Option Compare Database
Option Explicit

Private frm As Form

Private WithEvents Qty As TextBox 'TextBox object Instance - 1
Private WithEvents UPrice As TextBox  'TextBox object Instance - 2
Private WithEvents Total As TextBox 'TextBox object Instance - 3

'---------------------------------------------------------------------

'Form Object's Property GET/SET Procedures
Public Property Get m_Frm() As Form
    Set m_Frm = frm
End Property

Public Property Set m_Frm(ByRef mfrm As Form)

    Set frm = mfrm

'After receiving Form Object, scan for TextBoxes on Form
'and Enable them with required Events.
'
   Call class_Init

End Property
'---------------------------------------------------------------------

'Scan for TextBox Controls on Form
'and Enable the required Events
'
Private Sub class_Init()
Dim ctl As Control
Const EP = "[Event Procedure]"

For Each ctl In frm.Controls
    Select Case ctl.Name
    
    Case "Quantity"
 'Assign Quantity TextBox on Form to 'Qty'
 'Private Property of Class_3_1 Class Module
 
          Set Qty = ctl
        
    'Enable RaiseEvent
        Qty.OnExit = EP
        Qty.OnGotFocus = EP
        Qty.OnLostFocus = EP
'------------------------------        
        Case "UnitPrice"
 'Assign UnitPrice TextBox on Form to 'UPrice'
 'Private Property of Class_3_1 Class Module

    Set UPrice = ctl
    
    'Enable RaiseEvent
        UPrice.OnExit = EP
        UPrice.OnGotFocus = EP
        UPrice.OnLostFocus = EP
'------------------------------    

        Case "TotalPrice"
 'Assign TotalPrice TextBox on Form to 'Total'
 'Private Property of Class_3_1 Class Module
        
            Set Total = ctl
    
    'Enable RaiseEvent
            Total.OnGotFocus = EP
            Total.OnLostFocus = EP
    End Select
Next

End Sub

'---------------------------------------------------------------------

'Qty.Exit() Qty.GotFocus() & Qty.LostFocus() Event Procedures

Private Sub Qty_Exit(Cancel As Integer)
Dim i As Integer, Msg As String
Dim info As Integer

i = Nz(Qty.Value, 0)
If i < 1 Or i > 10 Then
    Msg = "Valid Value Range 1 - 10 Only."
    info = vbCritical
    Cancel = True 'I-bar to stay in the TextBox
Else
    Msg = "Quantity: " & i & " Valid."
    info = vbInformation
End If

MsgBox Msg, vbOK + info, "Quatity_Exit()"

End Sub

Private Sub Qty_GotFocus()
    With Qty
        .BackColor = &H20FFFF
        .ForeColor = 0
    End With
End Sub

Private Sub Qty_LostFocus()
On Error Resume Next
    With Qty
        .BackColor = &HFFFFFF
        .ForeColor = 0
    End With
End Sub

'---------------------------------------------------------------------
'UPrice.Exit() Qty.GotFocus() & UPrice.LostFocus() Event Procedures

Private Sub UPrice_Exit(Cancel As Integer)
Dim i As Single, Msg As String
Dim info As Integer

i = Nz(UPrice.Value, 0)
Msg = ""
If i <= 0 Then
    Msg = "Enter a Value greater than Zero!"
    info = vbCritical
    Cancel = True 'I-bar to stay in the TextBox
End If

If Len(Msg) > 0 Then
    MsgBox Msg, vbOK + info, "UnitPrice_Exit()"
Else
    MsgBox "Unit Price: " & i, vbOK + vbInformation, "UPrice_Exit()"
    
End If

End Sub

Private Sub UPrice_GotFocus()
    With UPrice
        .BackColor = &H20FFFF
        .ForeColor = 0
    End With
End Sub

Private Sub UPrice_LostFocus()
    With UPrice
        .BackColor = &HFFFFFF
        .ForeColor = 0
    End With
End Sub

'---------------------------------------------------------------------
'Total.GotFocus() & Total.LostFocus() Event Procedures

Private Sub Total_GotFocus()
    
    With Total
        .BackColor = &H20FFFF
        .ForeColor = 0
    End With
    
    frm!TotalPrice = Qty * UPrice
    frm.TotalPrice.Locked = True
        
End Sub

Private Sub Total_LostFocus()
    With Total
        .BackColor = &HFFFFFF
        .ForeColor = 0
    End With
End Sub

Private Sub Class_Terminate()
    Set Qty = Nothing
    Set UPrice = Nothing
    Set Total = Nothing
End Sub

'---------------------------------------------------------------------

You can ignore/delete the dashed lines I put in between segments for better readability on this Page.

Segment-wise Review of VBA Code.

Declaration of Objects and Get/Set Property Procedures of Form Object.

Let’s go through the Class Module code step by step to understand what each segment does. Please keep in mind that this structure is only our starting point. Later, we’ll introduce major changes that will make the code easier to manage and maintain.

Option Compare Database
Option Explicit

Private frm As Form

Private WithEvents Qty As TextBox     'TextBox object Instance - 1
Private WithEvents UPrice As TextBox  'TextBox object Instance - 2
Private WithEvents Total As TextBox   'TextBox object Instance - 3

'---------------------------------------------------------------------

'Form Object's Property GET/SET Procedures
Public Property Get m_Frm() As Form
    Set m_Frm = frm
End Property

Public Property Set m_Frm(ByRef mfrm As Form)

    Set frm = mfrm

'After receiving Form Object, scan for TextBoxes on Form
'and Enable them with required Event Procedures.
'
   Call class_Init

End Property
'--------------------------------------------------------------------- 

In the global declaration area, we’ve declared a Form object named Frm with Private scope. This means the Form object isn’t accessible from outside the Class Module—it can only be reached through Public Property procedures. To make this work, we pass the current Form object (Me) into the Set m_Frm() property procedure during the Form_Load() event. Once the current Form instance is assigned to the Frm object in the Class3_1 Class Module, we gain access to all its controls and their values. At that point, we can capture any events fired by the controls (via RaiseEvent), update values in TextBoxes, or work with other properties of the Form’s controls.

In this demo of the “Streamlining the Form Module VBA Code” procedure, we’ll create a Form object inside every standalone Class Module. You’ll notice how the currently active Form object is passed from the Form_Load() event procedure into the Form object instance within the standalone Class Module, using the Set property procedure parameter. As we go along, keep an eye on how this Form reference is used inside the standalone Class Module (Class3_1) and within its Event Procedure subroutines.

The next three TextBox object declarations—Qty, UPrice, and Total—will be linked to their corresponding TextBox controls on the Form: Quantity, UnitPrice, and TotalPrice, respectively.

Next, the Set Property Procedure will receive the Form object into the Frm property via the Form_Load() event, routed through the m_Frm Property Procedure. For example, in the Form Module, you would write:

Set C.m_Frm = Me

This statement passes the current Form instance to the standalone Class Module. The Get Property Procedure will then be ready to service any external requests for the Form object whenever needed.

The Call Class_Init() statement calls the Subroutine Class_Init().

The Class_Init() subroutine scans the Form for TextBox Objects, and their References are assigned to the three TextBox Control Instance names, Qty, UPrice, and Total, we created in the Global declaration area.

Scan the Form to find the Quantity, UnitPrice, and TotalPrice TextBoxes.

'Scan for TextBox Controls on Form
'and Enable the required Event Procedures
'
Private Sub class_Init()
Dim ctl As Control
Const EP = "[Event Procedure]"

For Each ctl In frm.Controls
    Select Case ctl.Name
    
    Case "Quantity"
 'Assign Quantity TextBox on Form to 'Qty'
 'Private Property of Class_3_1 Class Module
 
          Set Qty = ctl
        
    'Enable RaiseEvent
        Qty.OnExit = EP
        Qty.OnGotFocus = EP
        Qty.OnLostFocus = EP
'------------------------------        
        Case "UnitPrice"
 'Assign UnitPrice TextBox on Form to 'UPrice'
 'Private Property of Class_3_1 Class Module

    Set UPrice = ctl
    
    'Enable RaiseEvent
        UPrice.OnExit = EP
        UPrice.OnGotFocus = EP
        UPrice.OnLostFocus = EP
'------------------------------    

        Case "TotalPrice"
 'Assign TotalPrice TextBox on Form to 'Total'
 'Private Property of Class_3_1 Class Module
        
            Set Total = ctl
    
    'Enable RaiseEvent
            Total.OnGotFocus = EP
            Total.OnLostFocus = EP
    End Select
Next

End Sub

'--------------------------------------------------------------------- 

Let’s walk through the code segment by segment.

First, we declare a general-purpose Control object named Ctl and a Constant EP that holds the text "[Event Procedure]".

Next, the statement:

For Each Ctl In Frm.Controls
    ' …
Next

loops through all the controls on the Form, one by one, and assigns each to the Ctl variable in turn.

Since we’re only interested in three specific TextBoxes — Quantity, UnitPrice, and TotalPrice — the others can be ignored. To filter for the ones we care about, we use a Select Case structure on Ctl.Name:

Select Case Ctl.Name
    Case "Quantity", "UnitPrice", "TotalPrice"
        ' Handle these controls
End Select

This way, the loop scans all controls, but only these three TextBoxes are picked up for further processing.

When the loop encounters Case "Quantity", the control reference held in Ctl is assigned to the Qty TextBox instance declared earlier in the global section:

Set Qty = Ctl

After that, the next three statements configure the event properties of the Quantity TextBox. By assigning the constant EP (which holds the text "[Event Procedure]"), we dynamically enable its event procedures on the Form:

Qty.OnExit = EP
Qty.OnGotFocus = EP
Qty.OnLostFocus = EP

This ensures that whenever the user exits, focuses on, or leaves focus from the Quantity TextBox, the corresponding event procedure will fire — and that action can then be captured and handled in the standalone Class Module.

This time, instead of using the AfterUpdate event, we switched to the OnExit event. The reason is simple: OnExit gives us a little more control. For example, if the user enters a value that’s outside the allowed range, we can immediately display an error message and keep the cursor (the I-bar) inside the same TextBox. All it takes is setting Cancel = True, which prevents the focus from moving away until the user enters a valid value.

The UnitPrice TextBox object is assigned to the UPrice TextBox object instance, which enables the same set of Event Procedures.

The TotalPrice TextBox Reference is assigned to the Total TextBox object instance and enables the OnGotFocus and OnLostFocus Events.

The Quantity TextBox Events

The Qty_Exit, Qty_Gotfocus, and Qty_LostFocus Event Procedures.

'---------------------------------------------------------------------

Private Sub Qty_Exit(Cancel As Integer)
Dim i As Integer, Msg As String
Dim info As Integer

i = Nz(Qty.Value, 0)
If i < 1 Or i > 10 Then
    Msg = "Valid Value Range 1 - 10 Only."
    info = vbCritical
    Cancel = True 'I-bar to stay in the TextBox
Else
    Msg = "Quantity: " & i & " Valid."
    info = vbInformation
End If

MsgBox Msg, vbOK + info, "Quatity_Exit()"

End Sub

Private Sub Qty_GotFocus()
    With Qty
        .BackColor = &H20FFFF
        .ForeColor = 0
    End With
End Sub

Private Sub Qty_LostFocus()
On Error Resume Next
    With Qty
        .BackColor = &HFFFFFF
        .ForeColor = 0
    End With
End Sub

'---------------------------------------------------------------------

In the Qty_Exit() Subroutine, three Variables are declared. The i = Nz(Qty.Value,0) statement reads the Quantity value from the Qty object instance. In the next step, it checks whether the received value is within the valid range of 1 to 10; if not, the Msg and Info String Variables are assigned to the Message Text for the MsgBox. The Cancel = True ensures that the I-Bar stays in the Quantity TextBox till a valid value is entered.

Next, the GotFocus Event Procedure changes the background color, and LostFocus resets the TextBox Color to its default value.

The UnitPrice TextBox goes through the same process.

 '---------------------------------------------------------------------

Private Sub UPrice_Exit(Cancel As Integer)
Dim i As Single, Msg As String
Dim info As Integer

i = Nz(UPrice.Value, 0)
Msg = ""
If i <= 0 Then
    Msg = "Enter a Value greater than Zero!"
    info = vbCritical
    Cancel = True 'I-bar to stay in the TextBox
End If

If Len(Msg) > 0 Then
    MsgBox Msg, vbOK + info, "UnitPrice_Exit()"
Else
    MsgBox "Unit Price: " & i, vbOK + vbInformation, "UPrice_Exit()"
    
End If
End Sub

Private Sub UPrice_GotFocus()
    With UPrice
        .BackColor = &H20FFFF
        .ForeColor = 0
    End With
End Sub

Private Sub UPrice_LostFocus()
    With UPrice
        .BackColor = &HFFFFFF
        .ForeColor = 0
    End With
End Sub

'---------------------------------------------------------------------
 

The TotalPrice TextBox Event Procedures.

'---------------------------------------------------------------------

Private Sub Total_GotFocus()
    
    With Total
        .BackColor = &H20FFFF
        .ForeColor = 0
    End With
    
    frm!TotalPrice = Qty * UPrice
    frm.TotalPrice.Locked = True
        
End Sub

Private Sub Total_LostFocus()
    With Total
        .BackColor = &HFFFFFF
        .ForeColor = 0
    End With
End Sub

The TotalPrice TextBox is enabled with the GotFocus and LostFocus Events. The OnGotFocus Event Procedure will change the background Color. Next, the calculated value Qty * UPrice is updated in the TotalPrice TextBox. Immediately after updating the value, the TextBox is locked by executing the statement frm.TotalPrice.Locked = True. The OnLostFocus Event resets to its earlier background Color.

We can now write the full Event Procedure code directly in the standalone Class Module, with the single exception of the Form_Load() event, which remains in the Form module as planned.

This marks the first time we’ve successfully implemented Event Procedure code for several TextBoxes within the standalone Class Module—and it works exactly as intended. In this case, we handled three TextBoxes, each with three different events, all of them running smoothly outside the form’s module.

Private Sub Class_Terminate()
    Set Qty = Nothing
    Set UPrice = Nothing
    Set Total = Nothing
End Sub

The Sub Class_Terminate () is a special Subroutine that executes automatically when the Class Module Class3_1 is in the closing phase and is used for clearing the memory occupied by the Objects.

There’s another special Subroutine you might come across in a Class Module, called Sub Class_Initialize(). If it’s present, it runs automatically the moment the Class Module is loaded into memory. This makes it handy for setting things up right away, like creating instances of other Class Objects, or initializing objects such as Collection or Dictionary, without requiring any manual calls from outside.

As you can see, the Class3_1 Class Module is starting to feel a bit crowded. We’ve got the TextBox instances defined, Event Procedures enabled, and the Event-handling code itself—all packed into one place. While this works fine for just three TextBoxes, we must handle more TextBoxes and Event Procedures with different requirements.  If we want to handle more than just a few TextBox instances, we’ll need a cleaner, more organized approach that makes it easier to manage and extend the code.

For the Quantity TextBox, we’ve got three Events: OnExit, OnGotFocus, and OnLostFocus. All of these are handled inside the Class3_1 Module. The UnitPrice TextBox also has three Events, while the TotalPrice TextBox uses two, giving us a total of eight Event Procedures—just as we would normally write them in the Form’s own module.

So far, so good. But here’s the bigger question: what if the Form has, say, 15 TextBoxes, and each one needs three Event Procedures? That’s 45 Event Procedures in total. Should we really dump all of them into the Form Module, or just move the entire load into a standalone Class Module?

We must find a better way to organize the VBA Code of several other Control types on the Form. We are now considering only the TextBox objects. What about other Controls, like Command Buttons, combo boxes, and others? 

But first, we will concentrate on refining our current Coding approach for our TextBox Objects. 

We will make a detailed study of this aspect next week. When we are familiar with that procedure, then we can handle other controls on the Form very easily.

Demo Database Download Link:

Streamline5.zip

Streamlining Form Module Code in Standalone Class Module.

1. Re-using Form Module VBA Coding for New Projects.
2. Defining Custom Events in Microsoft Access Part Two
3. Objects and Their Built-in Events Part 3.
4. Standalone Class Module and Events - Part Four
5. Several TextBoxes and Event Capturing Part Five
6.  Class Objects and Wrapper Classes - Part Six
7. Form Module vs. Reusable Class Module Coding Demo - Part Seven
8. Collection Object replaces Class Object Array - Part Eight
9. Reusability of Streamlined VBA Code - Part Nine
10. Organizing Wrapper Classes for Different Forms - Part Ten
11. ComboBox and Option-Group Wrapper Classes - Part Eleven
12. Report Module Code in Class Module - Part Twelve
13. Hiding Report Lines Conditionally - Part 13.
14. Form Report Detail Sections Event Handling - Part 14.
15. New Custom-Made Form Wizard VBA - Part 15.
16. New Custom-Made Report Wizard - Part 16.
17. Streamlining VBA External Files List in Hyperlinks-17
18. Streamlining Event Procedures 3D-Text Wizard-18
19. Streamlining Form Module VBA RGBColor Wizard-19
20. Form VBA Structured Coding Numbers to Words Converter-20
21. Form VBA Structured Coding Access Users-Group Europe Presentation-21
22. The Event Firing Mechanism in Access Objects-22
23. One TextBox and Three Wrapper Class Instances-23
24. Streamlining Code Synchronized Floating Popup Form-24
25. Streamlining Code Compacting/Repair Database-25
26. Streamlining Code Remainder Popup Form-26
27. Streamlining Code Editing Data in Zoom-in Control-27
28. Streamlining Code Filter By Character and Sort-28
29. Table Query Records in Collection Object-29
30. Class for All Data Entry Editing Forms-30
31. Wrapper Class Module Creation Wizard-31
32. wrapper-class-template-wizard-v2

Read More

Standalone Class Module and Events - Part Four

 Continued from the earlier episodes of 'Objects and Their Built-in Events Part 3.'

Links to the earlier episodes of the above topic are given below for Reference:

1. Re-using Form Module VBA Coding for New Projects.
2. Defining Custom Events in Microsoft Access - Part Two
3. Objects and Their Built-in Events - Part 3.

A Quick Look at Last Week's Session.

The Quantity TextBox object's events are enabled by selecting the [Event Procedure] option in the control's Event Property settings. When an event is raised (fired) by the TextBox object instance, it is captured by the Form's Class Module, where the corresponding event procedure is executed. This process is illustrated in the graphical image below.

The events raised (fired) by the Quantity TextBox on the Form can also be captured in the standalone `Class1` Class Module. To achieve this, an instance of the TextBox object must be declared in `Class1` and qualified with the `WithEvents` keyword. A Reference to the Quantity TextBox control on the Form is then assigned to this object variable in the `Class1` Class Module.

Object Reference: Explained by ChatGPT AI

Note: What does “reference” mean in the context of a Form, Control, or Object?

When a Form or Control is loaded into memory, it occupies a specific area of the computer’s memory with an addressable reference (or memory pointer). Although we don’t know the exact memory address, it is directly associated with the object’s name, such as Text0  Quantity.

When this reference is assigned to an instance of the same object type—for example:

Set Txt = myForm.Quantity

—can manipulate the physical object indirectly through the instance. For example, setting

Txt.Value = 20

stores the value 20 in the Quantity TextBox on the Form.

Similarly, when we pass a Form or TextBox to a function, we are actually passing the reference to that object, not a copy of the object itself.

This concept of working with object references is the foundation of streamlining Form and Report module code using standalone class modules.

Here’s a simple conceptual diagram (by ChatGPT) to illustrate how object references work in VBA:

+----------------------+          +----------------------+
|  Form: myForm        |          |  Memory (Objects)    |
|----------------------|          |----------------------|
| TextBox: Quantity    |  --->    | [Memory Address 101] |  <- Physical TextBox in memory
| TextBox: Name        |          | [Memory Address 102] |
| CommandButton: OK    |          | [Memory Address 103] |
+----------------------+          +----------------------+
         |
         | Set Txt = myForm.Quantity
         v
+----------------------+
|  Txt (Variable)      |
|----------------------|
| Reference -> 101     |  <- Points to the Quantity TextBox in memory
+----------------------+

Usage Example:
Txt.Value = 20  ' Updates the actual Quantity TextBox on the Form

Explanation:

1. Each control on the Form occupies memory when the Form is loaded.

2. Assigning a control to a variable Set does not create a new control; it creates a reference pointing to the same memory location.

3. Any changes made via the reference (Txt.Value = 20) directly affect the original control.

4. Functions that accept a Form or Control as a parameter actually receive the Reference, allowing the function to work directly with the original object.

Diagram provided by ChatGPT

Once the reference assignment is complete, events raised by the Quantity TextBox can be captured and processed within the `Class1` Module. The same event procedures that are traditionally written in the Form's Class Module, where the Quantity TextBox control resides, can instead be implemented in the `Class1` Class Module. The graphical illustration below depicts this event-handling mechanism and the associated object relationships.

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

We want the `AfterUpdate` event, along with other events, to be raised by the Quantity TextBox on the Form to validate the value and ensure that it falls within the permitted range of 1 to 10. The `GotFocus` event changes the background color of the active TextBox, while the `LostFocus` event restores the control's original background color. Selecting the [Event Procedure] option for these event properties ensures that the corresponding events are raised at the appropriate time.

When an event is raised, the VBA code written within the associated event procedure stub is executed. In this example, however, we want to redirect these events to event procedures located in a standalone Class Module. The objective is to use the Form primarily for user interface design while moving business logic and event-handling code into separate Class Modules, thereby improving code organization, maintainability, and reusability.

This approach is particularly beneficial when the same functionality must be applied to multiple controls. For example, if a Form contains ten TextBox controls, implementing the background-color change in the Form Module would typically require ten separate `GotFocus` event procedures. By handling the events through a standalone Class Module, a single `GotFocus` event procedure can service all applicable TextBox controls, eliminating repetitive code and simplifying maintenance.

To implement this technique, we must adopt a different approach to event handling. Developers who are new to VBA event programming may find some aspects of this concept challenging at first. Since the complete architecture and its implications cannot be fully explained in a few pages, the topic is presented as a step-by-step tutorial. This method allows both the underlying concepts and their practical implementation to be examined in a structured manner, making the learning process more effective and easier to follow. The effort invested in understanding this approach will be rewarded with a more scalable, reusable, and maintainable coding framework.

The new RaiseEvent Procedure.

Instead of manually setting the [Event Procedure] option in the Property Sheet for Events: AfterUpdate, OnGotFocus, or OnLostFocus, we can configure them in real-time through code. This allows us to directly invoke the RaiseEvent action from the object itself. To demonstrate this approach, we’ll create a new form by copying earlier Form1 from episode three and renaming it Form1_RaiseEvent_2. In this new form, we’ll implement the updated method within its Class Module.

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

Make a copy of Form1 as explained above. Open the new Form in Design View, and change the Detail Section background color, then display its VBA Module. Copy the following code and paste it over the existing Code to replace it.

Option Compare Database
Option Explicit

'Decare an Object Variable C as Type Class1
Private C As Class1

Private Sub Form_Load()
'Instantiate Class1 Class Object
  Set C = New Class1

'Assign Quantity TextBox Object to C.Txt Property
  Set C.Txt = Me.Quantity
  
'Enable Event Procedures of Quantity TextBox
'This method replaces the empty
'Event Procedure stub used earlier.

    Me.Quantity.AfterUpdate = "[Event Procedure]"
    
    Me.Quantity.OnGotFocus = "[Event Procedure]"
    
    Me.Quantity.OnLostFocus = "[Event Procedure]"
    
End Sub

Private Sub Form_Unload(Cancel As Integer)
'Release Class1 Object instance C from memory
    Set C = Nothing
End Sub

VBA Code Change Review.

As shown at the end of the `Form_Load()` event procedure, three lines of VBA code have been added to set the [Event Procedure] option programmatically for the corresponding event properties when the Form is loaded into memory. These settings remain active until the Form is closed. This approach eliminates the need to maintain empty event procedure stubs in the Form Module solely to trigger the event-raising mechanism.

Save and close the Form, then reopen it in Normal View. Enter a value greater than 10 in the Quantity TextBox and verify that the `AfterUpdate` event is raised and that the appropriate invalid-quantity message is displayed.

Next, enter a valid value from the range of 1 to 10. The validation process should function as expected, confirming that the event-handling mechanism continues to operate correctly.

It is important to note that all of the changes made so far have been implemented within the Form Module itself, primarily to demonstrate how events raised by the TextBox control can be captured and processed by the TextBox object instance declared in the `Class1` Class Module. However, our ultimate objective is different. We want to move the event-handling and application logic out of the Form Module and into a standalone Class Module. This design allows the Form to remain focused on user interface functionality. The Class Module handles the event processing and business logic, except for a few essential initialization lines that must remain in the Form Module.

The real-time RaiseEvent is enabled in the Class1 Module.

Now, we understand the need for the real-time Event-enabling Property option setting on the Form and have successfully completed the VBA Code. Let us see how we can implement this method in the stand-alone Class Module rather than coding in the Form Module.

Make a copy of the Class1 Module and rename it as Class2. We will preserve the Code in the Class1 Class Module for reference, and the changes we made in the new Copy.  Make a copy of the earlier Form1_RaiseEvent_2 Form and rename it as Form1_RaiseEvent_3.

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

New Form Module Code Change.

Open the new Form and open its VBA Module. Copy the following code and paste it over the existing Form Module Code.

Option Compare Database
Option Explicit

Private C As Class2

Private Sub Form_Load()
  Set C = New Class2
  Set C.m_Frm = Me 'What is m_Frm member in Class2 Module?
End Sub

Private Sub Form_Unload(Cancel As Integer)
    Set C = Nothing
End Sub

Save the Form with the new Code. 

Now that we’ve removed the Me.Quantity.AfterUpdate = "[Event Procedure]" event-enabling lines from the Form Module, those actions will instead be handled in the new Class2 Class Module. The earlier Class1 Class Module code is still in place, so you can easily compare the two and see the transformation takes place in Class2.

The second line in the Form_Load() Event Procedure is new and needs some explaining. As you can see on the left side of the = symbol, the Class2 Class Module object C has a Property Procedure named m_Frm that requires a Form Object passed as a parameter. 

On the right side of the = symbol, the Form object (Me) is passed to the C.m_Frm Property procedure of the Class2 Class Module. In simple terms, we are passing the current Form object to a property of the same type within Class2. In earlier examples, we passed the Form object directly to the Form property declared with Public scope in Class1. However, in Class2, we declared the Frm object with a Private scope. By routing the assignment through a Public Property procedure, we ensure that external programs cannot directly modify the Frm property value, keeping it safe. This difference will become clearer when we examine the new VBA code in Class2.

Class2 Class Module Code Change.

Open Class2 Class Module (normally, Class Modules need a meaningful name, instead of Class1, Class2). Copy the following VBA Code and paste it, overwriting the existing Code in the Module:

Option Compare Database
Option Explicit

Private Frm As Form
Private WithEvents Txt As TextBox

Public Property Get m_Frm() As Form 'GET Property Procedure
    Set m_Frm = Frm
End Property

Public Property Set m_Frm(ByVal mFrm As Form) 'SET Property Procedure

If Left(TypeName(mFrm), 4) = "Form" Then
    Set Frm = mFrm
Else
    MsgBox "Invalid Object " & mFrm & " passed as Form!"
    Exit Property
End If
    
    Call Class_Init 'Run Class_Init() Subroutine
End Property


Private Sub Class_Init()
    Set Txt = frm.Quantity
    
    Txt.AfterUpdate = "[Event Procedure]"
    Txt.OnGotFocus = "[Event Procedure]"
    Txt.OnLostFocus = "[Event Procedure]"
    
End Sub

Private Sub txt_AfterUpdate()
Dim i As Integer, msg As String
Dim info As Integer

i = Nz(Txt.Value, 0)
If i < 1 Or i > 10 Then
    msg = "Valid Value Range 1 - 10 Only."
    info = vbCritical
Else
    msg = "Quantity: " & i & " Valid."
    info = vbInformation
End If

MsgBox msg, vbOK + info, "txt_AfterUpdate()"

End Sub

Private Sub txt_GotFocus()
    With Txt
        .backcolor = &H20FFFF 
        .forecolor = 0
    End With
End Sub

Private Sub txt_LostFocus()
    With Txt
        .backcolor = &HFFFFFF
        .forecolor = 0
    End With
End Sub

Save the Class Module, open the new Form in Normal View, and try out the TextBox Quantity as before by entering Test Data. 

Let us check the Code segment-wise to understand it. We are already familiar with the AfterUpdate and other Event Procedures.

Private Frm As Form
Private WithEvents Txt As TextBox

We declared two object Properties in the global declaration area of the Class Module: Frm the Form object Property and the earlier Property, Txt, the TextBox Object. Both are declared with Private scope here rather than Public Scope, which we preferred in earlier examples. As I stated earlier, the Public scope declaration is not advisable for Class Object Properties because their values can be changed by external programs.

In this case, we should pass the Form object reference through the Property procedure, as shown in the code segment below. This approach not only ensures proper assignment but also allows us to validate the data passed into the Property procedure before actually storing it in the Property.

Public Property Get m_Frm() As Form
    Set m_Frm = Frm
End Property

Public Property Set m_Frm(ByRef mFrm As Form)

If Left(TypeName(mFrm), 4) = "Form" Then
    Set Frm = mFrm
Else
    MsgBox "Invalid Object " & mFrm & " passed to Form"
    Exit Property
End If

    Call Class_Init 'Run Class_Init() Subroutine
End Property

We are receiving a Form Object in the mFrm Parameter in the Public Property Set m_Frm() Property Procedure. We check the received object Type using the TypeName() Function and extract the first four characters of the object name, Form_Form1_RaiseEvent_3.  If the first Four Characters match the text 'Form', then it is the correct object for the Frm Property declared in the Global area. The Set Frm = mFrm assigns the Form object to the Property Frm.  

When we need the Form reference stored in the Frm property, we use the public Property Get m_Frm() procedure. For example:

Debug.Print m_Frm.Name

However, from within the Class2 module itself, we can directly reference the property without going through the m_Frm wrapper, like this:

Debug.Print Frm.Name

The SET and GET Property Procedures.

Both the GET and SET Property Procedures must have the same name, m_Frm, or any suitable name you prefer, and their Data Type is Form Object. If you recall the Form Module statement: Set C.m_Frm = Me, that passes the active Form Object as a parameter to the Set Property Procedure in Class2 Class Module. In this Property Set Procedure, perform a validation check to ensure that the parameter value is a Form Object reference assigned to the Frm Property. The SET keyword is used for Objects only, and the LET keyword is used for other data Types: String, Integer, and others.

Note: To know more about Property Procedures and how they work in the Class Module, visit an earlier Page: MS Access Class Module and VBA

Once we receive the Form Object reference in our Class Module, we can easily address the controls on the Form and get their references in the Class Module to work with them. We need the reference of the TextBox named Quantity to enable its Events and validate the value entered into the TextBox by running the Event Procedures in the Class Module. For these actions, we have created a separate Subroutine called Class_Init()

The Class_Init() Subroutine is called from the Property Set m_Frm() Procedure, immediately after assigning the Form (Form_Form1_RaiseEvent_3) reference to the Global Property Frm. 

Private Sub Class_Init()
    Set Txt = Frm.Quantity
    
    Txt.AfterUpdate = "[Event Procedure]"
    Txt.OnGotFocus = "[Event Procedure]"
    Txt.OnLostFocus = "[Event Procedure]"
    
End Sub

In this procedure, we first assigned the Quantity TextBox reference to the Txt TextBox Object Instance declared in the global declaration area of the Class3 Class Module. Check how we address the Quantity TextBox on the Form from the Class3 Class Module (Frm.Quantity like Me.Quantity). If you would like to assign some value to the Quantity TextBox on the Form, you can do that with the statement Frm!Quantity = 25, from the Class Module.

The next three statements enable the AfterUpdate, OnGotFocus, and OnLostFocus Event Procedures by setting the text "[Event Procedure]" Option and setting them dynamically when the Form is Open. Earlier, this procedure ran in the Form Module, and now executes the Event Procedure Code in the Class Module instead.

Did you notice the change in the Event Procedure Names? In the Form Module, the AfterUpdate Event Procedure name is written as Private Sub Quantity_AfterUpdate(), the Textbox name as a name prefix follows the Event name, and both elements are joined with an underscore character. 

In the Class Module, the TextBox object instance declaration name Txt becomes the Event Procedure name prefix: Private Sub Txt_AfterUpdate()

Instead of repeating the text "[Event Procedure]", we can define this as a Constant and use a simple name instead, like:

Private Sub Class_Init()
Const EP = "[Event Procedure]"
    Set Txt = frm.Quantity
    
    Txt.AfterUpdate = EP
    Txt.OnGotFocus = EP
    Txt.OnLostFocus = EP
    
End Sub

Loading Standalone Class Module in Memory.

Note: One important point is that the standalone Class Module Object cannot load itself into memory like the Class Modules of a Form or Report. 

We need to instantiate the Class2 Class Module in memory through a Form Module or another Class Module.  The standalone Class Module cannot load itself into memory. A Class Module attached to the form is loaded into memory when the form is opened. We already did this in the Form_Load() Event Procedure of the Form, with the following declarations:

Option Compare Database
Option Explicit
Private C As Class2

Private Sub Form_Load()

  Set C = New Class2
  
  Set C.m_Frm = Me 'What is m_Frm member in Class2 Module?
  
End Sub

Now, returning to the remaining code in the Class2 module, let’s review the event procedure we already know well—the one that validates the value entered in the Quantity textbox on the form.

Private Sub txt_AfterUpdate()
Dim i As Integer, msg As String
Dim info As Integer

i = Nz(Txt.Value, 0)
If i < 1 Or i > 10 Then
    msg = "Valid Value Range 1 - 10 Only."
    info = vbCritical
Else
    msg = "Quantity: " & i & " Valid."
    info = vbInformation
End If

MsgBox msg, vbOK + info, "txt_AfterUpdate()"

End Sub

Private Sub txt_GotFocus()
    With Txt
        .backcolor = &H20FFFF
        .forecolor = 0
    End With
End Sub

Private Sub txt_LostFocus()
    With Txt
        .backcolor = &HFFFFFF
        .forecolor = 0
    End With
End Sub

For now, there’s no change in the event procedure code itself. Up to this point, we’ve only worked with a single TextBox object on the form. If you’ve followed the concept this far, that’s a big step forward—it will make it much easier to learn and explore more advanced techniques and tricks.

Open Form1_RaiseEvent_3 in normal view and try it out by entering a value greater than 10 first, then retry with a new value from the range 1 to 10 to ensure that all three Event Procedures work as planned. 

Event Procedure of all Controls on Form in Standalone Class Module.

If you’re starting to feel impatient or bored with all the details and explanations, you might be wondering what this is really leading to. So, let me give you a quick preview of the final product. Open the link “WithEvents and all Form Control Types” to see a sample demo of what we’re ultimately working toward.

At the end of the link Page, you will find a Demo Database Download Link. Download the Demo Database and open it. Open the frmControls_All Form in Design View and then display its Form Class Module. You will find only three or four lines of Code. Open the Form in Normal View, enter some Data in TextBoxes, and select options from ComboBox, ListBox, and other controls on the Form. 

You will find responses either by displaying the data you entered in the Message Box or in the colored Help text displayed by the Label control at the bottom of the Form, which indicates that the Events in the Form are tracked and responded to by the Class Module objects in memory. This is only a sample database demonstrating how the Events of the Form-based controls are captured in the Class Module and displays messages.

Database Download for this session's Code Demo Run.

Streamline4.zip

Streamlining Form Module Code in Standalone Class Module.

1. Re-using Form Module VBA Coding for New Projects.
2. Defining Custom Events in Microsoft Access Part Two
3. Objects and Their Built-in Events Part 3.
4. Standalone Class Module and Events - Part Four
5. Several TextBoxes and Event Capturing Part Five
6.  Class Objects and Wrapper Classes - Part Six
7. Form Module vs. Reusable Class Module Coding Demo - Part Seven
8. Collection Object replaces Class Object Array - Part Eight
9. Reusability of Streamlined VBA Code - Part Nine
10. Organizing Wrapper Classes for Different Forms - Part Ten
11. ComboBox and Option-Group Wrapper Classes - Part Eleven
12. Report Module Code in Class Module - Part Twelve
13. Hiding Report Lines Conditionally - Part 13.
14. Form Report Detail Sections Event Handling - Part 14.
15. New Custom-Made Form Wizard VBA - Part 15.
16. New Custom-Made Report Wizard - Part 16.
17. Streamlining VBA External Files List in Hyperlinks-17
18. Streamlining Event Procedures 3D-Text Wizard-18
19. Streamlining Form Module VBA RGBColor Wizard-19
20. Form VBA Structured Coding Numbers to Words Converter-20
21. Form VBA Structured Coding Access Users-Group Europe Presentation-21
22. The Event Firing Mechanism in Access Objects-22
23. One TextBox and Three Wrapper Class Instances-23
24. Streamlining Code Synchronized Floating Popup Form-24
25. Streamlining Code Compacting/Repair Database-25
26. Streamlining Code Remainder Popup Form-26
27. Streamlining Code Editing Data in Zoom-in Control-27
28. Streamlining Code Filter By Character and Sort-28
29. Table Query Records in Collection Object-29
30. Class for All Data Entry Editing Forms-30
31. Wrapper Class Module Creation Wizard-31
32. wrapper-class-template-wizard-v2

Read More