Several TextBoxes and Event Handling - Part Five ~ LEARN MS-ACCESS TIPS AND TRICKS

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:

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.

Create a new Form and insert 3 unbound TextBoxes, one below the other, on the Detail Section of the Form.
Insert a Label control above the TextBoxes and write the description as shown in the Form Image given above, in its Caption Property.
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).
Select the second TextBox, change its Name Property Value to UnitPrice, and change the child-label Caption to Unit Price.
Change the Name Property value of the third TextBox to TotalPrice and enter Total Price in the child-label Caption.
Display the VBA Code Module of the Form.

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

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

LEARN MS-ACCESS TIPS AND TRICKS

Friday, June 23, 2023