June 2023 ~ LEARN MS-ACCESS TIPS AND TRICKS

Continued from the last Episode - Part Four.

Introduction.

Writing Form Module VBA Code in 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 only one TextBox object on the Form and found how it can be programmed in several ways by writing VBA Code in a standalone Class Module rather than on the Form Module. We are familiar with the rules that govern defining Events, raising an Event to announce the Event, and declaring a TextBox object instance with the keyword WithEvents, which allows us to capture events in the class module raised by the TextBox control on the Form.

Now, let’s take a look at how to handle events for more than one TextBox on a form. This time, we’ll place three TextBoxes on the form to experiment with event handling in a standalone Class Module.

In real-world scenarios, a form may contain 10, 20, or even more TextBoxes. Of course, it’s unlikely that every single one of them will require event procedures—but if needed, we can certainly extend our approach to cover them all.

Each TextBox comes with a long list of built-in events. Not all of them will be relevant for every project, but depending on the requirements, you may find yourself using several of them. Our goal is to be prepared for that challenge.

Let’s begin with three TextBoxes and see how we can handle a few of their event procedures. To start off, we’ll use a simple, somewhat crude approach. It may not be the most elegant solution in the long run, but it works—and, more importantly, it’s easy to understand. Once we get the basics clear, we can gradually refine the method into something more efficient and reusable.

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 Declaration of Class Object C to create an Instance of the Class Module Class3_1 object. 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 the Memory. We instantiated 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 the 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.

We’ll eventually come up with a better way to name our Class Modules so that, just by looking at the name, we can quickly tell where they belong or what type of object they represent. For now, though, we’re still on the learning curve—and understanding the concept itself is far more important than these cosmetic 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 some 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 directly 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 be creating 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 Instances with the 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 and 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 only.

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 TextBox 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 with 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 only. 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 are now able to 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 more than one TextBox within the standalone Class Module—and it works exactly as intended. In this case, we handled three TextBoxes, each with three different events, all 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 very 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, it’s not going to scale well. 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.

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

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

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

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

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