July 2023 ~ LEARN MS-ACCESS TIPS AND TRICKS

Streamlining Form Module Code - Part Eight

a.p.r. pillaiJuly 28, 2023 No comments

Introduction.

Continued from "Streamlining Form Module Code - Part Six & Seven"

In the past, we used the TextBox Wrapper Class Array to store TextBox instances with Events enabled, allowing us to capture them in memory and execute their associated Event Subroutines. While this Array-based method worked, it required frequent re-dimensioning and the maintenance of separate indices for different object types, such as Command Buttons, ComboBoxes, and ListBoxes. Fortunately, there is a more efficient solution—the Collection Object.

The earlier Article Links are given below:

In this session of our Tutorial on the Title topic, we will learn the following tricks:

Usage of the Data Table.
Collection Object replaces the Object Array.
Command Button Wrapper Class.
Form Wrapper Class. Sections: Header, Detail, and Footer Event Procedure.
Using Timer-Interval Property for Digital Clock on the Form.
Form Closing Count Down without using TimerInterval Property.

Trial Run Form-Image, taken from the Countdown and Form closing phase.

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

Using a Data Table.

We often run various validation checks on the data entered into a TextBox before accepting it. The entered data may also participate in calculations, with the results stored in another TextBox on the Form. These operations can be handled from the Class Module, regardless of whether the TextBox’s Control Source is bound to a table field or not. The only difference is that, in the unbound case, the data is not stored anywhere—but we can still perform all necessary validations outside the Form Module, within the stand-alone Class Module.

The Collection Object replaces the Object Array.

With the new Coding procedure, we could reduce the number of Event Subroutines of a particular Event into a single one, without writing a separate Event Procedure for each Textbox.

For example, check the following sample Exit() Event Procedure of three TextBoxes written under a single Event Subroutine:

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

Select Case Txt.Name
    Case "Description"  'TextBox - Description
        If Len(Nz(Txt.Value, "")) = 0 Then
            MsgBox "Item Description Empty"
            Cancel = True
        End If
        
        
    Case "Quantity"     'TextBox - Quantity
        i = Nz(Txt.Value, 0)
        If i < 1 Or i > 10 Then
            Msg = "Valid Value Range 1 - 10 Only."
            info = vbCritical
            MsgBox Msg, vbOK + info, "Quatity_Exit()"
        GFColour frm ', Txt
            Cancel = True
        Else
            Msg = "Quantity: " & i & " Valid."
            info = vbInformation
            MsgBox Msg, vbOK + info, "Quatity_Exit()"
        End If
        
        
    Case "UnitPrice" 'TextBox - UnitPrice
    
        i = Nz(Txt.Value, 0)
        Msg = ""
        If i <= 0 Then
            Msg = "Enter a Value greater than Zero!"
             info = vbCritical
         GFColor frm 
            Cancel = True
            MsgBox Msg, vbOK + info, "UnitPrice_Exit()"
        Else
            info = vbInformation
            MsgBox "Unit Price: " & i, vbOK + vbInformation, "UPrice_Exit()"
        End If
    End Select
End Sub

If additional TextBoxes require Exit() Event Subroutines, they can all be written within this structured Subroutine. If we place them in the Form Module instead, each would end up in separate Subroutines, scattered across different locations alongside other object programmes. This makes the code harder to manage compared to the organized approach within the stand-alone Class Module.

The TextBox names (highlighted in red along with other TextBoxes) can also be enabled (RaiseEvent) with other required inbuilt Events such as GotFocus, LostFocus, and more. These will then be grouped in the same manner as shown above—all GotFocus cases within a single GotFocus() Event Subroutine, and all LostFocus cases within a single LostFocus() Event Subroutine.

In short, you need to write only one BeforeUpdate() Event Subroutine in the standalone Class Module, even if there are 25 TextBoxes on the Form enabled with this Event. All BeforeUpdate() procedures for those TextBoxes can be handled within that single Subroutine. The same principle applies to all other TextBox Events as well. This way, all related logic is centralized in one standalone TextBox Wrapper Class Module, simplifying both code maintenance and debugging.

All Object Types on the Form will have their own Standalone Wrapper Classes, and their Event Subroutines are well organized in this way.

All these Event Procedures are executed one at a time, as and when the corresponding event occurs. They may reside either in the Form Module or in the Wrapper Class instance loaded in memory, both having the same reference to the Control on the Form.

The TextBoxes (and other controls) on the Form are each declared with the WithEvents keyword when inserted. Since they are spread across the Form, separate Event Subroutines must be written for each TextBox within the Form Module.

However, by using our Wrapper TextBox Class, we need only a single instance of the TextBox object, declared with the WithEvents keyword. In the Wrapper Class, the TextBox is declared with the object name: Txt, as in Private WithEvents Txt As TextBox. As a result, all TextBox-related Event Subroutines are automatically prefixed with the object name: Txt, for example, Private Sub Txt_GotFocus().

We create separate instances of the Wrapper Class Module for each TextBox on the Form, and their references are automatically assigned to the corresponding TextBoxes through an initialization procedure. This approach eliminates the need to manually write and maintain individual Event Subroutines in the Form Module for each TextBox, saving considerable time and effort during the database development phase.

All GotFocus Events are captured in a single Subroutine. Within this Subroutine, we use a Select Case Txt.Name block to identify which TextBox triggered the Event—for example, Case "Quantity"—and then write the corresponding VBA code under that TextBox name. This individual name-checking approach is unnecessary if all TextBoxes require the same code for a particular Event, such as the TextBox highlighting example we discussed in an earlier episode. In that case, the code can be written once and applied globally. Alternatively, you can combine both approaches: write specific code for selected TextBoxes inside the Select Case structure, while placing the common logic outside it to apply across all TextBoxes.

The Wrapper Class also includes a declaration for a Form object, typically named frm, without the WithEvents qualification. This reference is essential because it allows the Wrapper Class, while in memory, to read from or write to other controls on the Form. In this way, the Wrapper Class is not limited to handling events of a single TextBox but can also interact with and manipulate other controls on the Form as needed.

To keep the Wrapper Class instances in memory, we initially used the Wrapper Class Array approach within the Class_Init() Subroutine of the WrapObject_Init2 Intermediate Class Module. However, when Wrapper Classes are also created for other Access controls—such as Command Buttons, Combo Boxes, List Boxes, and others—this method requires maintaining separate indices for each control type and re-dimensioning arrays accordingly.

A more flexible alternative is the Collection object. Unlike arrays, a Collection can hold any type of object or value, with or without an associated item key. This makes it far more convenient for managing multiple wrapper instances of different control types in a single structure.

For those unfamiliar with the Collection object, please review the following resources:

COLLECTION OBJECT

The Class_Init() Subroutine code for the WrapTextBox_Init Class was presented earlier in the Title Topic – Part Six. In the updated version, WrapTextBox_Init2, the same functionality has been implemented using the Collection object instead of Arrays.

WrapTextBox_Init Version VBA Code.

'Scan for TextBox Controls on Form
j = 0
'and Enable the required Event Procedures
For Each ctl In iFrm.Controls
Select Case TypeName(ctl)
    Case "TextBox"
        Select Case ctl.Name
            Case "Quantity"
                
                    Set iTxt = New WrapTextBox 'Create Instance
                    Set iTxt.tx_Frm = iFrm     'Pass Form object
                    Set iTxt.t_Txt = ctl       'Pass TextBox Control on Form
            
                    iTxt.t_Txt.OnExit = EP     'Enable Event
                    iTxt.t_Txt.OnGotFocus = EP '     "
                    iTxt.t_Txt.OnLostFocus = EP '    "
        
                j = j + 1                       'increment counter
                ReDim Preserve TxtArray(1 To j) 'Redim Array
                Set TxtArray(j) = iTxt          'Save WrapTextBox Class
                Set iTxt = Nothing              'Erase temp Instance

            Case "UnitPrice"
                
                    Set iTxt = New WrapTextBox 'Create Instance
                    Set iTxt.tx_Frm = iFrm
                    Set iTxt.t_Txt = ctl
            
                    iTxt.t_Txt.OnExit = EP
                    iTxt.t_Txt.OnGotFocus = EP
                    iTxt.t_Txt.OnLostFocus = EP
        
                j = j + 1
                ReDim Preserve TxtArray(1 To j)
                Set TxtArray(j) = iTxt          'Save it in Object Array
                Set iTxt = Nothing              'Erase temp Instance
    
            Case "TotalPrice"
            
                Set iTxt = New WrapTextBox      'Create Instance
                Set iTxt.tx_Frm = iFrm
                Set iTxt.t_Txt = ctl
            
                iTxt.t_Txt.OnGotFocus = EP
                iTxt.t_Txt.OnLostFocus = EP
        
                j = j + 1
                ReDim Preserve TxtArray(1 To j)
                Set TxtArray(j) = iTxt           'Save it in Object Array
                Set iTxt = Nothing               'Erase temp Instance
            End Select
        
    End Select
Next

WrapTextBox_Init2 Version VBA Code.

'Scan for TextBox Controls on Form
'and Enable the required Event Procedures
For Each ctl In iFrm.Controls

    Select Case TypeName(ctl)
        Case "TextBox"
            Select Case ctl.Name
                Case "Description"
                    Set iTxt = New WrapTextBox2 'Create Instance
                    Set iTxt.tx_Frm = iFrm
                    Set iTxt.t_Txt = ctl       'Pass TextBox Control on Form
                     
                    iTxt.t_Txt.OnGotFocus = EP '     "
                    iTxt.t_Txt.OnLostFocus = EP '    "
                
                    Coll.Add iTxt 'Save WrapTextBox2 Class
                
                    Set iTxt = Nothing 'Erase temp Instance
                    
                Case "Quantity", "UnitPrice"
                    Set iTxt = New WrapTextBox2 'Create Instance
                    Set iTxt.tx_Frm = iFrm
                    Set iTxt.t_Txt = ctl       'Pass TextBox Control on Form
                     
                    iTxt.t_Txt.OnExit = EP     'Enable Event
                    iTxt.t_Txt.OnGotFocus = EP '     "
                    iTxt.t_Txt.OnLostFocus = EP '    "
                
                    Coll.Add iTxt 'Save WrapTextBox2 Class
                
                    Set iTxt = Nothing 'Erase temp Instance
                
                
                Case "Discount"
                    Set iTxt = New WrapTextBox2 'Create Instance
                    Set iTxt.tx_Frm = iFrm
                    Set iTxt.t_Txt = ctl       'Pass TextBox Control on Form
                     
                    iTxt.t_Txt.OnGotFocus = EP '     "
                    iTxt.t_Txt.OnLostFocus = EP '    "
                
                    Coll.Add iTxt 'Save WrapTextBox2 Class
                
                    Set iTxt = Nothing 'Erase temp Instance
            End Select
        
    End Select
Next

Compare the two versions of the same VBA code. The lines highlighted in red in both versions pertain to the TextBox object instances.

The updated diagram illustrates the Z-Concept – a streamlined logic for handling Form Module coding through Class Objects, providing a clear overview at a glance.

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

Form Sections Header, Detail, Footer, Event handling.

There are several events associated with a Form object. In this section, we will explore how the Click and MouseMove events are triggered from different areas of the form: the Detail, Form Header, and Form Footer sections.

You don’t need to design a form exactly like the one shown at the top of this page. Instead, you can download the demo database from the link at the end of this page and experiment with it yourself. Pay attention to the changes made in the WrapObject_Init2 and WrapTextBox modules. Then, compare this updated VBA code with the version you already have from Episode Six.

Episode Eight introduces two new wrapper classes: WrapForm and WrapCmdButton. The WrapForm class handles Form event subroutines as well as events specific to different Form sections.

WrapForm Class Module VBA Code is given below:

Option Compare Database
Option Explicit

Private WithEvents Sfrm As Access.Form
Private WithEvents ScD As Access.Section

'------------------------------------------------------
'Streamlining Form Module Code
'in Stand-alone Class Modules
'------------------------------------------------------
'Access.Form Wrapper Class Module
'Author: a.p.r. pillai
'Date  : 27/07/2023
'Rights: All Rights(c) Reserved by www.msaccesstips.com
'------------------------------------------------------
Public Property Get s_frm() As Access.Form
Set s_frm = Sfrm
End Property

Public Property Set s_frm(ByRef FFrm As Access.Form)
    Set Sfrm = FFrm
End Property

Public Property Get s_SecD() As Section
    Set s_SecD = ScD
End Property

Public Property Set s_SecD(ByVal iSec As Section)
    Set ScD = iSec
End Property

Private Sub scD_Click()
Select Case ScD.Name
    Case "FormHeader"
        MsgBox "FormHeader Click", , "FormHeader Section"

End Select
End Sub

Private Sub scD_DblClick(Cancel As Integer)
Select Case ScD.Name
    Case "FormFooter"
        MsgBox "FormFooter DblClick", , "FormFooter Section"
End Select
End Sub

Private Sub scD_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
Select Case ScD.Name
    Case "Detail"
    Sfrm.Label39.Caption = X & " , " & Y
End Select
End Sub

In the Global declaration area of the Class Module, two Class objects, Form and Form Section objects, are declared. Followed by their Object assignment, Public Property procedures.

In the WrapForm Class Module demo, the Form sections’ Click, MouseMove, and Double-Click events are enabled. The MouseMove event in the Detail section displays the X and Y coordinates in a label. The Form Header is set up to respond to the Click event, while the Footer section responds to the Double-Click event. Both the Header and Footer events display a message when triggered.

Note: The indicator label 'Click here' in the Form Footer area may read as 'Double Click'.

The Digital Clock is run from the WrapObject_Init2 Class Module. The iFrm_Timer() Subroutine is also placed in this Module to update the label control Caption Property with the Time in "hh:nn:ss" format.

The clock runs at one-second intervals. It is not recommended to place this code in the WrapForm Class Module because each form section event creates a separate instance of the WrapForm Class in memory. If the clock code is placed there, it would run simultaneously from all three instances, causing unintended behavior.

The WrapCmdButton Class Module VBA Code.

Option Compare Database
Option Explicit

Private WithEvents cfrm As Form
Private WithEvents cmd As CommandButton 'CommandButton object

'------------------------------------------------------
'Streamlining Form Module Code
'in Stand-alone Class Modules
'------------------------------------------------------
'Access.CommandButton Wrapper Class Module
'Author: a.p.r. pillai
'Date  : 27/07/2023
'Rights: All Rights(c) Reserved by www.msaccesstips.com
'------------------------------------------------------

'Form's Property GET/SET Procedures
Public Property Get c_Frm() As Form
    Set c_Frm = cfrm
End Property

Public Property Set c_Frm(ByRef cmdfrm As Form)
    Set cfrm = cmdfrm
End Property

'TextBox Property GET/SET Procedures
Public Property Get c_cmd() As CommandButton
    Set c_cmd = cmd
End Property

Public Property Set c_cmd(ByRef pcmd As CommandButton)
    Set cmd = pcmd
End Property

Private Sub cmd_Click()
Select Case cmd.Name
    Case "cmdClose"
        If MsgBox("Close this Form?", vbOKCancel + vbQuestion, "cmd_Click") = vbOK Then
            DoCmd.Close acForm, cfrm.Name
        End If

End Select
End Sub

'Form Closing CountDown...
'Do Not write Form_Unload() on Form Module

Private Sub cfrm_Unload(Cancel As Integer)
Dim T As Double, t2 As Double
Dim strMsg As String

'cFrm.TimerInterval = 0 - Disable Clock, if necessary
strMsg = "Form will Close in "
cfrm.Label29.Visible = True
T = Timer
Do While Timer < T + 10
            t2 = Timer
            Do While Timer < t2 + 0.25
                DoEvents
            Loop
cfrm.Label29.Caption = strMsg & " " & Int((T + 10) - Timer) & " Seconds."
    DoEvents
Loop

End Sub

The Command Button Wrapper Class is straightforward, primarily handling the Click event. In the global declaration area, the Command Button and Form objects are defined. Following that, the Get and Set property procedures for both objects are implemented.

Next, the Private Sub cmd_Click() event subroutine handles the Click event for the cmdClose button. If additional command buttons are added to the form, their Click events can also be managed within this same subroutine by using a Select Case ... End Select structure to identify each button by name and handle their events individually.

Before the form closes, the Form_Unload() event fires. The remaining code runs a 10-second form-closing countdown simulation. A hidden label control positioned above the textboxes is made visible, and the countdown text is displayed there. When the count reaches zero, the form automatically closes.

Note: If a Form_Unload() event subroutine exists in the Form Module, the cfm_Unload() event subroutine in the WrapCmdButton class will not execute. This is because the Form Module’s Form_Unload() event takes priority and closes the form first.

Demo Database Download Link:

Streamline8.zip

Streamlining Form Module Code in Standalone Class Module.

Streamlining Form Module Code - Part Seven

a.p.r. pillaiJuly 17, 2023 No comments

Introduction

Streamlining Form Module Code in Standalone Class Module.

Next week, we may continue from Part Six, refining the coding methods introduced earlier and introducing further improvements in their implementation.

Some readers may still question the effectiveness of these new coding methods. This page demonstrates the traditional Form Module approach alongside the streamlined standalone Class Module method. The demo is divided into three parts, ranging from standard coding to advanced automation, which helps determine which approach works best.

The Normal Coding method.
The Stand-alone Class Module supported method.
Two Stand-alone Class Modules supported the Option.

Part-I.

The review of existing Coding practices to assess the differences.

When designing a data entry or view form, it’s often helpful to highlight the active field with a distinct color or border, making it easy to spot or resume work from that point. Achieving this requires two event subroutines for each TextBox: a GotFocus event to apply the highlight and a LostFocus event to reset the field’s original formatting. For example, with 10 TextBoxes, we would need 10 GotFocus and 10 LostFocus subroutines—a total of 20. The usual approach is to copy and paste one pair of subroutines and then adjust the TextBox name in each subroutine header.

The Event Subroutines can call predefined Functions or write Code for Back Color, Border Color, and Border Width Property settings within all the Subroutines.

In either case, the time spent manually implementing this method for all TextBoxes, ComboBoxes, and ListBoxes on a form offers no flexibility or reusability. The effort cannot be leveraged for another form design or project, except for reusing the public function code stored in a standard module.

We’ll now explore a simple yet powerful technique using a standalone class module. This approach automatically handles any number of TextBoxes, ListBoxes, and ComboBoxes you add to a form—without requiring additional code. Even better, the same class module can be easily reused in other projects.

Even though the common Event Subroutines GotFocus() and LostFocus() for TextBox Color Attribute settings can be written in the stand-alone Class Module. However, it is better to write them in the Standard Module as two separate Functions, and it is easy to call them from the Form Module or from the stand-alone Class Module.

We’ll kick things off with the traditional Form Module approach so you can see firsthand how much time and repetitive effort it takes. Then, in the second part, we’ll switch gears and bring in a standalone class module. Here, only a small bit of code stays in the Form Module, while the heavy lifting is handled automatically behind the scenes. The real payoff comes when you see how effortless it is to add new TextBoxes to the form—no extra coding required. By the end, you’ll have a clear picture of just how much time and frustration this new method can save compared to the old way.

The Traditional Method.

Create a new form and place eight TextBoxes on it, arranged one below the other. Rename them to: SID, Description, Quantity, UnitPrice, TaxPcnt, TotalPrice, Discount, and NetPay.

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

Let’s begin with the traditional method. First, select the OnGotFocus event property of a TextBox and set its value to [Event Procedure]. Then, click the Build Code button to open the Form Module, where Access automatically creates an empty GotFocus event subroutine. Inside this subroutine, we’ll add a single line of VBA code to call the GFColor() function, passing the current Form object as the parameter. In the same way, we’ll create the LostFocus event subroutine to call the LFColor() function from the Standard Module, as shown below.

If we follow the same route to write Event Subroutines for 16 Event Procedures, you can imagine how much time it will take to complete all of them.

Private Sub SID_GotFocus()
    GFColor Me
End Sub

Private Sub SID_LostFocus()
    LFColor Me
End Sub

Next, copy and paste the GotFocus/LostFocus subroutine pair, then update the code to match each of the other TextBox controls on the form. Once you’ve completed this process for all eight text boxes, the Form Module will be filled with multiple event procedures, similar to the example shown in the image below.

Option Compare Database
Option Explicit

'------------------------------------------------------
'Streamlining Form Module Code
'in Stand-alone Class Modules
'------------------------------------------------------
'Highlighting Textboxes on Form
'Author: a.p.r. pillai
'Date  : 13/07/2023
'Rights: All Rights(c) Reserved by www.msaccesstips.com
'------------------------------------------------------

Private Sub SID_GotFocus()
    GFColor Me
End Sub

Private Sub SID_LostFocus()
    LFColor Me
End Sub

Private Sub Description_GotFocus()
    GFColor Me
End Sub

Private Sub Description_LostFocus()
    LFColor Me
End Sub

Private Sub Quantity_GotFocus()
    GFColor Me
End Sub

Private Sub Quantity_LostFocus()
    LFColor Me
End Sub

Private Sub UnitPrice_GotFocus()
    GFColor Me
End Sub

Private Sub UnitPrice_LostFocus()
    LFColor Me
End Sub

Private Sub TaxPcnt_GotFocus()
    GFColor Me
End Sub

Private Sub TaxPcnt_LostFocus()
    LFColor Me
End Sub

Private Sub TotalPrice_GotFocus()
    GFColor Me
End Sub

Private Sub TotalPrice_LostFocus()
    LFColor Me
End Sub

Private Sub Discount_GotFocus()
    GFColor Me
End Sub

Private Sub Discount_LostFocus()
    LFColor Me
End Sub

Private Sub NetPay_GotFocus()
    GFColor Me
End Sub

Private Sub NetPay_LostFocus()
    LFColor Me
End Sub

Each time you add new TextBox controls to the form, you’ll need to create a corresponding pair of GotFocus and LostFocus subroutines to apply the highlight feature to them as well. Once this step is complete, save the form with the name Form1_Normal.

Next, we’ll create two small, reusable functions that define the color attributes for highlighting controls. The first one will be used in the OnGotFocus event to highlight the active TextBox (and works equally well for ComboBoxes and ListBoxes). The second one LFColor() will reset the control’s colors when it loses focus. These functions will also play an important role in our standalone Class Module–based demos. If you’d like to customize the color scheme, you can easily update it directly within the functions.

The Color Attributes change the Function Code in the Standard Module.

Option Compare Database
Option Explicit

Dim save_BackColor As Variant
Dim save_BorderWidth As Variant
Dim save_BorderColor As Variant

'------------------------------------------------------
'Streamlining Form Module Code
'in Stand-alone Class Modules
'------------------------------------------------------
'Highlighting Textboxes on Form
'Author: a.p.r. pillai
'Date  : 13/07/2023
'Rights: All Rights(c) Reserved by www.msaccesstips.com
'------------------------------------------------------
Public Sub GFColor(ScrForm As Form)
'Active Field Highlight
   With ScrForm.ActiveControl
      save_BackColor = .BackColor
      save_BorderWidth = .BorderWidth
      save_BorderColor = .BorderColor
 
        .BackColor = &HD1FDFF
        .BorderWidth = 2
        .BorderColor = &H1914BA
    End With
End Sub

Public Sub LFColor(ScrForm As Form)
'Reset Active Field Highlight
    
   With ScrForm.ActiveControl
        .BackColor = save_BackColor
        .BorderWidth = save_BorderWidth
        .BorderColor = save_BorderColor
    End With
End Sub

Copy and paste the above VBA Function Code into a Standard Module and save it.

Now, open Form1_Normal in normal view and tab through the TextBoxes to see how it works. If you add two more textboxes to the Form, you need to add Event Subroutines manually in the Form Module for those two new textboxes.

Part-II.

The Stand-alone Class Module supported method.

First, let us create a stand-alone Class Module for the same Functions we wrote in the Standard Module. Let us see what difference it makes.

Open the VBA Editing Window (ALT+F11).
Select the Class Module Option from the Insert Menu to add a new Class Module.
Click on the new Class Module to select it, and click on the Properties button above to open the Properties Window in the left panel.
Change the name of the Class Module to myClass1.

Copy and paste the following VBA Code into the myClass1 Class Module.

Option Compare Database
Option Explicit

Public WithEvents Tx As TextBox
Public Fm As Form

Dim save_BackColor As Variant
Dim save_BorderWidth As Variant
Dim save_BorderColor As Variant

'------------------------------------------------------
'Streamlining Form Module Code
'in Stand-alone Class Modules
'------------------------------------------------------
'Highlighting Textbox on Form
'Author: a.p.r. pillai
'Date  : 13/07/2023
'Rights: All Rights(c) Reserved by www.msaccesstips.com
'------------------------------------------------------
Private Sub Tx_GotFocus() 'TextBox
    'GFColor Fm 'Call the standard Module Function
    
    With Tx
      save_BackColor = .BackColor
      save_BorderWidth = .BorderWidth
      save_BorderColor = .BorderColor
      
         .BackColor = &HD1FDFF
         .BorderWidth = 2
         .BorderColor = &H1914BA
    End With
End Sub

Private Sub Tx_LostFocus()
   'LFColor Fm 'Call the standard Module Function
    With Tx
        .BackColor = save_BackColor
        .BorderWidth = save_BorderWidth
        .BorderColor = save_BorderColor
    End With
End Sub

Let us review the above VBA Code to understand what they do for highlighting the TextBox on the Form.

The first declaration line Public WithEvents Tx As TextBox creates an instance of the Access TextBox class. For demonstration purposes, it is declared as Public though, in practice, it would normally be Private. This approach helps us avoid writing additional Property Procedures and keeps the myClass1 module simple. When the form is opened, the currently active TextBox instance is assigned to the Tx object in the myClass1 module. The WithEvents keyword enables the Tx instance to capture events triggered by the TextBox (in this case, the GotFocus and LostFocus events) and handle them through the corresponding event procedures coded within the myClass1 Class Module.

The next line declares a Form object with the instance name Fm. This will later be assigned to the Form1_myClass form object through the Fm property. (We’ll create this form once we finish the VBA code in the myClass1 Class Module.) Additionally, three Variant-type variables are declared to store the original color and property values of a TextBox before they are modified in the GotFocus event procedure. These stored values are then restored in the LostFocus event procedure, ensuring the control returns to its original appearance once it loses focus.

Next, in the GotFocus event subroutine, Private Sub Tx_GotFocus() the Tx object represents the currently active TextBox on the form. For example, if the active TextBox is named, Description this works just like having a procedure named Private Sub Description_GotFocus() in the form’s module.

The next (currently disabled) line is meant to call the GFColor() function we wrote earlier in the Standard Module. For this demo, however, we’ve chosen to keep it disabled and instead write the color-setting procedure directly here in the class module. In our earlier ‘normal’ demo, we called the function with the form object Me as a parameter. But since the built-in GotFocus and LostFocus event procedures don’t allow parameters, we’ll now assign the values directly to the Fm (Form) and Tx (TextBox) object instances. This way, we can easily reference these objects in our code while the form is running.

Next, a few lines of Code set the appropriate color attribute values to highlight the active TextBox Control.

Similarly, the Tx_LostFocus() Event Procedure resets the Color attributes when the TextBox activity is lost.

We are writing this code in the Design View of the myClass1 class module. These actions take effect only when the module is loaded into the computer’s memory. Unlike form modules, a class module cannot open and run directly. To make it active, we have two options available.

Open myClass1 Class Module in memory through the Form_Load() Event Procedure.
Seek the help of another stand-alone Class Module to do that. We will use the first option in this case. We will demonstrate the second option in Part III.

The Second Part Demo Form.

Create a Copy of the first Form, Form1_Normal, and rename it as Form1_myClass.
Let the TextBoxes remain as they are on the Form.
Open the Form in Design View and display its Form Module.

Copy the following VBA Code and paste it over the existing VBA Code in the Form Module.

Option Compare Database
Option Explicit

Private TBox As myClass1
Private Col As New Collection

'------------------------------------------------------
'Streamlining Form Module Code
'in Stand-alone Class Modules
'------------------------------------------------------
'Highlighting Textbox on Form
'Author: a.p.r. pillai
'Date  : 13/07/2023
'Rights: All Rights(c) Reserved by www.msaccesstips.com
'------------------------------------------------------
Private Sub Form_Load()
Set TBox = New myClass1
Call Class_InitEnd Sub

Private Sub Class_Init()
Dim ctl As Control

For Each ctl In Me.Controls
Select Case TypeName(ctl)
    Case "TextBox"
        Set TBox = New myClass1
        Set TBox.Fm = Me
        Set TBox.Tx = ctl
        
        TBox.Tx.OnGotFocus = "[Event Procedure]"
        TBox.Tx.OnLostFocus = "[Event Procedure]"
        
        Col.Add TBox
End Select
Next
End Sub

Private Sub form_Unload(cancel As Integer)
Set Col = Nothing
End Sub

Select Compile from the Debug Ribbon to ensure that no issues with the VBA Code.
Save the Form with the VBA Code.
If curiosity gets the better of you, go ahead and open the form in Normal View. Experiment with it first—we’ll review the code afterward to understand exactly how it works.
Press the Tab Key to move from one TextBox to the other.
Let’s try another quick experiment. Close the form and reopen it in Design View. Resize the Detail section so there’s enough space to duplicate the controls. Now, select the entire set of TextBoxes along with their labels, copy them, and paste them into the adjacent area once or twice. Don’t worry about the TextBox names for now.

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

Save the form and open it in Normal View. Use the Tab key to move from one TextBox to another. You’ll notice that the Tab order may not follow the expected sequence. Check whether the highlight moves from one TextBox to the other and reaches the new TextBox group's end.
Add a few TextBoxes manually anywhere on the Form and try them out to check whether the new TextBoxes also get highlighted without any changes to the Code..

If you are ready to continue, then let us review the Form Module VBA Code segment-wise.

Option Compare Database
Option Explicit

Private TBox As myClass1
Private Col As New Collection

In the global section of the Class Module, two objects are declared. The first one TBox is defined as a myClass1 object, which in turn represents a TextBox. This declaration sets the stage for bringing the standalone Class Module myClass1 into memory. However, the declaration by itself does not load the object into memory. To do that, we must instantiate the Class Object—that is, create an instance of it. In practice, we’ll need multiple instances of this object, one for each TextBox on the form. These instances are created within the Class_Init Subroutine.

The second declaration is a Collection Object with the Object name Col, and the usage of the New keyword in the declaration creates an Instance of the Collection object in memory. If you are not familiar with the Collection Object, please go through the following Links to learn the Basics of this Object:

The Form_Load() Event Subroutine.

Private Sub Form_Load()
    Call Class_Init
End Sub

The Class_Init statement calls the Subroutine to take over the rest of the serious work. The Class_Init part of the Code can be written within the Form_Load() Event Procedure, too. But, we will be moving the Class_Init() Subroutine Code into a different Class Module later.

Private Sub Class_Init()
Dim ctl As Control

For Each ctl In Me.Controls
Select Case TypeName(ctl)
    Case "TextBox"
        Set TBox = New myClass1
        Set TBox.Fm = Me
        Set TBox.Tx = ctl
        
        TBox.Tx.OnGotFocus = "[Event Procedure]"
        TBox.Tx.OnLostFocus = "[Event Procedure]"
        
        Col.Add TBox
        
        Set TBox = Nothing
End Select
Next
End Sub

Private Sub form_Unload(cancel As Integer)
  Set Col = Nothing
End Sub

First, we declare an Access.Control object variable named Ctl. Inside the For…Next loop, this variable is used to iterate through all the controls on the form, one by one. To filter out only the controls we are interested in, we use a Select Case…End Select block with multiple conditions. Within this block, the TypeName() function helps us check the type of control currently stored in Ctl. If the control is identified as a TextBox (Case "TextBox"), we then create a new instance of the myClass1 Class by assigning it to the TBox object using the statement Set TBox = New myClass1.

Note: We found the first TextBox object, probably with the name: SID, found in the Ctl Control. Since all the TextBoxes need the same Property settings, their names (like Quantity or UnitPrice) are not important here. We only need to make sure that it is a TextBox.

In the next step, we assign the Form object Me to the TBox.Fm property of the myClass1 Class, and the current TextBox control (Ctl) to the TBox.Tx property of the same Class instance.

The following two lines of code activate the GotFocus and LostFocus events for this TextBox by setting their event-enabling property values to "[Event Procedure]".

At this point, our first instance of the myClass1 Class is fully initialized with references to the Form and the first TextBox control. Once this setup is complete, the reference to the current TextBox is released from the TBox instance, making the Class ready to be reused for the next TextBox on the Form.

Think of it like plugging each TextBox into its own event-handler socket. Each time the loop encounters a new TextBox, it ‘plugs’ that control into a fresh instance of the Class, wiring it up with the necessary events. Once connected, the socket is free again to handle the next control in line.

The best way to manage the current instance of the myClass1 Class is to store it in the Collection object that we already instantiated in memory. The statement Col.Add TBox takes care of this. Using a Key value for Collection members is optional, and in this case, unnecessary.

Our goal is not to retrieve these Class instances later, but simply to keep them alive in memory so they can ‘listen’ for events fired by their assigned TextBox controls, capture those events, and run the corresponding Event Subroutines. When the Form closes, the Collection object (along with all its stored Class instances) is automatically cleared from memory.

The Collection Object itself was already instantiated in the global declaration area using the New keyword; all we need to do after saving the current instance is release its reference to the current TextBox with the statement Set TBox = Nothing. This frees the TBox variable to handle the next TextBox on the Form, while the saved instance in the Collection continues doing its job.

Think of it like parking cars in a garage: once you park one car safely inside (store the instance in the Collection), you step out and free yourself up (Set TBox = Nothing) to drive in the next car. Each stays parked (active in memory) until the entire garage (the Form) is closed.

The For…Next loop continues this process for every TextBox on the Form, one after another. Each TextBox is linked to a new instance of myClass1 its events are enabled, and the reference is stored in the Collection object. By the time the loop finishes, all TextBoxes on the Form are dynamically equipped with their GotFocus and LostFocus event procedures, ready to respond as soon as the Form is open.

Finally, in the Form_Unload() event, the Collection object itself is cleared from memory. This automatically removes all the stored class instances, ensuring a clean release of resources when the Form is closed.

Note: Normally, on a Form, we create physical instances of the TextBox class as properties of the Form, and their event procedures are written directly in the Form’s Class Module. In our new coding approach, however, we introduce a Wrapper Class Module that contains both a Form object (since every Access object is ultimately implemented as a stand-alone Class Module) and a TextBox object declared with the WithEvents keyword. This special declaration allows the Wrapper Class to capture the events fired by the assigned TextBox and handle them within its own event procedures.

For each TextBox on the Form, we create one instance of the Wrapper Class. Once the reference of a Form TextBox is assigned to the WithEvents TextBox in the Wrapper Class, it effectively mirrors the Form’s TextBox—listening to its events and executing the event procedures defined in the Wrapper Class.

An important point to note is that the Form-based events and the Wrapper Class–based events are independent, and both will execute if defined. For example, if you place a simple MsgBox "Hello World" in the GotFocus event of a TextBox in the Form Module, and also define an event procedure for the same TextBox in the Wrapper Class, the Form Module action will execute first, followed by the Wrapper Class action.

Save and Close the Form Form1_myClass.

Open the Form in Normal View, try moving the focus from one TextBox to the other, and check whether the TextBox highlighting works as before. You may remove/add TextBoxes and try again.

Part-III

First, we will prepare the myClass2 (Version 2 of myClass1) Class Module, then an intermediate Class Module to move the Class_Init Subroutine into it, and then create a new Demo Form. Creating separate Class Modules and Forms will be helpful to go back to check the earlier methods and to understand where the change took place.

Create a New Class Module with the name myClass2.

Copy and paste the following VBA Code into it and save the Class Module.

Option Compare Database
Option Explicit

Public WithEvents Tx As TextBox
Public WithEvents Lst As ListBox
Public WithEvents Cbo As ComboBox
Public Fm As Form

'------------------------------------------------------
'Streamlining Form Module Code
'in Stand-alone Class Modules
'------------------------------------------------------
'Highlighting Textbox,ListBox & ComboBox on Form
'Author: a.p.r. pillai
'Date  : 13/07/2023
'Rights: All Rights(c) Reserved by www.msaccesstips.com
'------------------------------------------------------
Private Sub Tx_GotFocus() 'TextBox
    GFColor fm 'Call Function from Standard Module
End Sub

Private Sub Tx_LostFocus()
    LFColor fm
End Sub

Private Sub Lst_GotFocus() 'ListBox
    GFColor fm
End Sub

Private Sub Lst_LostFocus()
    LFColor fm
End Sub

Private Sub cbo_GotFocus() 'Combo0Box
    GFColor fm
End Sub

Private Sub cbo_LostFocus()
    LFColor fm
End Sub

In the above Class Module, we have now added two more properties—Lst for the ListBox and Cbo for the ComboBox—alongside the earlier Tx (TextBox) and Fm (Form) properties in the global declaration area. All of these are declared as Public properties to avoid the need for writing separate property procedure pairs for each control type.

The event subroutines originally defined for the TextBox are duplicated for the ListBox and ComboBox as well, with distinct prefixes in their names. For the ListBox, the event subroutine names begin with Lst_ , for example: Private Sub Lst_GotFocus() and Private Sub Lst_LostFocus(). Similarly, for the ComboBox, the prefix Cbo_ is used, as in Private Sub Cbo_GotFocus() and Private Sub Cbo_LostFocus().

In these subroutines, rather than repeating explicit color-setting statements for each control type, we call the GFColor() and LFColor() functions to apply the highlight or reset effect. This keeps the code centralized, consistent, and easier to maintain across TextBoxes, ListBoxes, and ComboBoxes.

Relocating the Form Module Class_Init() Subroutine.

We will transfer the Class_Init() Subroutine into a separate stand-alone Class Module and free the space in the Form Module. We need only a few essential lines of Code on the Form Module. The Class_Init Subroutine will be run from the intermediate Class Module-based Subroutine Class_Init().

In this demo, with some changes in the Code, we will include the ComboBox and ListBox Controls for highlighting them too, if they are present in the Form.

Creating the Intermediate Class Module.

Create a new Class Module in the VBA Editing Window.
Rename it as myClass_Init.
Copy and paste the following VBA Code into the Module and save it:

Option Compare Database
Option Explicit

Private TBox As myClass2
Private Fom As Form
Private Col As New Collection

'------------------------------------------------------
'Streamlining Form Module Code
'in Stand-alone Class Modules
'------------------------------------------------------
'Highlighting Textbox, ListBox and ComboBox on Form
'Author: a.p.r. pillai
'Date  : 10/07/2023
'Rights: All Rights(c) Reserved by www.msaccesstips.com
'------------------------------------------------------
Public Property Get o_Fm() As Form
    Set o_Fm = Fom
End Property

Public Property Set o_Fm(ByRef vFrm As Form)
    Set Fom = vFrm
    
    Call Class_Init
End Property

'Class Init Subroutine transferred from Form Module
'
Private Sub Class_Init()
Dim ctl As Control

For Each ctl In Fom.Controls
Select Case TypeName(ctl)
    Case "TextBox"
        Set TBox = New myClass2 'Instantiate myClass2 Class
        Set TBox.Fm = Fom
        Set TBox.Tx = ctl
        
        TBox.Tx.OnGotFocus = "[Event Procedure]"
        TBox.Tx.OnLostFocus = "[Event Procedure]"
        
        Col.Add TBox
        Set TBox = Nothing       'Erase MyClass2 Class
        
    Case "ListBox"
        Set TBox = New myClass2  'Instantiate myClass2 Class
        Set TBox.Fm = Fom
        Set TBox.Lst = ctl
        
        TBox.Lst.OnGotFocus = "[Event Procedure]"
        TBox.Lst.OnLostFocus = "[Event Procedure]"
        
        Col.Add TBox
        
        Set TBox = Nothing     'Erase MyClass2 Class

        
    Case "ComboBox"
        Set TBox = New myClass2
        Set TBox.Fm = Fom
        Set TBox.Cbo = ctl
        
        TBox.Cbo.OnGotFocus = "[Event Procedure]"
        TBox.Cbo.OnLostFocus = "[Event Procedure]"
        
        Col.Add TBox
        
        Set TBox = Nothing
End Select
Next
End Sub

By looking at the above code, you can see the changes we made. In the global declaration area, we declared the myClass2 class with the object name TBox. In the earlier version myClass1 handled only the TextBox class, but in this new version myClass2 has been expanded to include the ListBox and ComboBox classes as additional properties.

The myClass2_Init Procedure scans the form using a For … Next loop, checking for the presence of ListBox and ComboBox controls in addition to TextBoxes. Whenever one of these controls is found, an instance of the myClass2 class is created and assigned to the TBox object.

Take a look at the following four lines of code, which show this process in action:

    Case "TextBox"
        Set TBox = New myClass2 'Instantiate myClass2 Class
        Set TBox.Fm = Fom
        Set TBox.Tx = ctl

When the Case "TextBox" condition is met, the control Ctl is identified as a TextBox. The myClass2 class is then instantiated into the TBox object. The Form object is assigned to the TBox.fm property, and the TextBox control Ctl is assigned to the TBox.Tx property. The GotFocus and LostFocus Events are enabled, and the TBox instance is added to the Collection object. Finally, the TextBox reference TBox is released with Set TBox = Nothing.

For ListBoxes and ComboBoxes, the process is similar. The control is assigned to the appropriate property—Set TBox.Lst = Ctl for ListBoxes or Set TBox.Cbo = Ctl for ComboBoxes. When these controls trigger the GotFocus or LostFocus events, their corresponding event subroutines are executed through the myClass2 instance stored in the Collection object.

The intermediary Class Module myClass2_Init is ready. Now we will create our Demo Part-III Form.

Prepare Form1_myClass2 Form.

Make a Copy of Form Form1_myClass and rename it as Form1_myClass2.
Open the new Form in Design View.

Right-click to open Large Image in New Tab/Window.
Delete some Textboxes that we copied and pasted to the right side of the Form.
Add two or three List Boxes and a few Combo Boxes on the right side of the Text Boxes.
Save the Form and display the Form VBA Module.
Copy and paste the following VBA Code in the Module, overwriting the existing Code in there.

Option Compare Database
Option Explicit

Private Clr As New myClass2_Init

Private Sub Form_Load()
    Set Clr.o_Fm = Me
End Sub

Private Sub Form_UnLoad()
    Set Clr = Nothing
End Sub

Review of the Form1_myClass2 Module Code.

In the Form Module, the necessary VBA code is required to load the myClass2_Init class module into memory. The class is declared with the object name Clr (short for Color), and when declared with the New keyword, memory is allocated for the myClass2_Init object.

Within the Form_Load() event, the Form object (Me) is assigned to the Clr.o_Frm property of the myClass2_Init class. Once the Form reference is passed, the Class_Init() A subroutine is called to scan the Form for TextBox, ListBox, and ComboBox controls. This process enables the required events and ensures that the corresponding event handlers are captured in the respective instances of these controls, which are stored in the Collection object in memory.

When the Form is closed, the Form_Unload() subroutine executes, and myClass2_Init The object is released from memory with the statement Set Clr = Nothing.

Loading myClass2, myClass2_Init Class Modules in Memory.

When the Form is opened, the other two stand-alone class modules must also be loaded into memory so that they remain synchronized and work together.

In the Form Module, the declaration Private Clr As New myClass2_Init instantiates the myClass2_Init class and loads it into memory.

myClass2_Init class The module holds a reference to the myClass2 class and creates instances of it. These instances enable events after assigning references to the Form, TextBox, ListBox, and ComboBox controls, and then storing them in the Collection object.

With these declarations, all three modules—the Form Module myClass2_Init and myClass2—along with the Collection object, are loaded into memory. When the Form is closed, all of them are automatically released from memory. An image posted in an earlier article on this topic is reproduced here to illustrate this process graphically.

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

So, all three Class Modules will reside in memory and work together to streamline the Form Module code within the stand-alone Class Module.

The Class_Terminate() Subroutine in the myClass2_Init Class Module runs automatically, much like the Form_Unload() Event Procedure in the Form Module.

The Form_Unload() Event Subroutine executes the statementSet Clr = Nothing , and it signals the closure of the myClass2_Init Class Object. This, in turn, triggers the shutdown of the myClass2 Class Module. Before it closes, the Class_Terminate() Subroutine clears the Collection object along with all its contents—the instances of TextBoxes, ListBoxes, and ComboBoxes.

We will continue from this point in our next episode. I hope you found this session insightful and engaging.

Download Demo Database

Streamline7.zip

Streamlining Form Module Code in Standalone Class Module.

Streamlining Form Module Code - Part Six

a.p.r. pillaiJuly 04, 2023 No comments

Continued from the last Episode - Part Five.

Introduction.

Streamlining Form Module VBA Code in Standalone Class Module.

Earlier Episode Links:

After experimenting with the previous examples, you should now have a clear understanding of how the Events Subroutines of TextBox objects work on a Form and how they can be relocated into a standalone Class Module to run the Event Procedures for specific tasks on the Form.

In our earlier experiments, we worked mostly with a single TextBox to understand the fundamentals. The only exception was in Part Five, where we extended the idea to three TextBoxes to demonstrate how to enable events and capture raised events within a standalone Class Module. It is important to note that all of these actions were carried out entirely in the standalone Class Module, rather than in the Form’s own module.

I hope you remember an important point I mentioned earlier: we can write Event Procedures directly in the Form’s module because, behind the scenes, every Access object is actually designed as a standalone Class Module.

Note: If you’d like to see this for yourself, just open the Object Browser in the VBA window and select Access from the <All Libraries> dropdown. In the left pane, under the Classes section, you’ll see a complete alphabetical list. Scroll through it and you’ll find familiar objects like CommandButton, Form, Label, ListBox, TextBox, and many others—each one represented as its own class.

Whenever you insert a TextBox on a form, Access automatically declares an instance of the TextBox object using the WithEvents keyword. By default, Access gives it a unique name such as Text0, Text1, and so on. Of course, you can rename it to something more meaningful by changing the Name property to suit your needs.

When a TextBox instance is declared with the keyword WithEvents, it allows the TextBox to capture and handle its own built-in collection of events and execute the Event-related User-written Code within an Event Subroutine, like AfterUpdate. In this case, the event subroutine can be written in the parent Form Module, with the TextBox name as the prefix for the subroutine name.

However, when creating a standalone Class Module, specifically for capturing events from the TextBox from the Form, we need to declare a TextBox object instance with the keyword WithEvents within the Class Module. Then assign the TextBox Control Reference from the Form to the TextBox Instance. This allows the Class Module to capture and handle the Events Raised by the TextBox. The event subroutine will then be written within the Standalone Class Module itself, rather than in the parent Form Module.

By declaring the TextBox instance with the keyword WithEvents within the Class Module, we establish the necessary connection between the TextBox control and the event handling logic defined within the Class Module. This enables us to encapsulate the event-handling functionality within the Class Module, providing a more modular and reusable approach for handling TextBox events.

In the earlier examples, we were able to capture events from TextBox controls by declaring them with the keyword WithEvents in the standalone Class Module. However, when there are several TextBoxes or other controls on the form, it becomes difficult to declare individual instances for each control and manage them in the Class Module.

A Flexible Approach for Class Module Coding.

To handle events from multiple controls efficiently, we can use a dynamic approach. Instead of declaring individual control instances in the Class Module, we can use arrays or collections to manage and iterate through the controls. This allows for capturing events from multiple controls without the need for individual declarations.

For example, if there are 25 TextBox controls on the form and we need to handle their Events, we can declare a single TextBox Instance with the keyword WithEvents in a Standalone Class Module. Once it is done, create an Array of these TextBox Instances to hold references to several TextBox controls on the Form.

Similarly, other types of controls, like Command Buttons or Combo Boxes, follow a similar approach to manage them through arrays or Collection Object Items and capture their events in the Standalone Class Module.

By using this dynamic approach, it is easy to handle events from a large number of controls on the form in a more scalable and manageable way. It allows us to centralize the event handling logic in the standalone Class Module and provides a more modular and reusable solution.

Re-organizing the stand-alone Class Module.

With all those points in mind, we need to reorganize the Class Module of our last episode and split it into two Parts.

The Event enabling Segment (Class_Init()) in one Class Module
In the second Class Module, a single TextBox Instance and a Form Object Instance Declaration, their Property Procedures, and their Event Subroutines will be placed.

The last episode's Class Module Class3_1 VBA Code is listed below for reference.

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'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

Call class_Init
End Property

'==================================================
Private Sub class_Init()
Dim ctl As Control
Const EP = "[Event Procedure]"

'Scan for TextBox Controls on Form
'and Enable the required Event Procedures
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
        Qty.Visible = True
    '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

'==================================================

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
Else
    Msg = "Quantity: " & i & " Valid."
    info = vbInformation
End If

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

End Sub

Private Sub Qty_GotFocus()
    With Qty
        .backcolor = 65535 '&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

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
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


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

The Class_init() Subroutine segment, the area marked by double-dash lines above and below, will be placed within a separate Class Module, named "WrapObjects_Init". The Class Module will undergo changes to handle more than one TextBox and other Objects when needed. This Class Module will be dedicated to the Event-enabling (RaiseEvent) of all categories of Objects. This Class Module plays an intermediary role in streamlining the Form Module Event Subroutine Code in standalone Class Modules.

The Code Segment above the first double line and Event Subroutines below the second double line will be placed in a separate Class Module with the name "WrapTextBox". This Class Module is dedicated to the TextBox Objects and for their Event Subroutines only.

We will create the WrapTextBox Class Module with a single TextBox object declaration and with a Form object Instance. We are familiar with the Property Procedures of Form and TextBox controls. This single Class Module Instance will serve all the TextBoxes on the Form and their Event Subroutines.

The WrapTextBox Class Module VBA Code.

Option Compare Database
Option Explicit

Private frm As Form 'Form Object declaration

'TextBox Object declaration with keyword WithEvents
Private WithEvents Txt As TextBox 'TextBox object

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

Public Property Set tx_Frm(ByRef pfrm As Form)
    Set frm = pfrm    'Assin Form
End Property


'TextBox Property GET/SET Procedures
Public Property Get t_Txt() As TextBox
    Set t_Txt = Txt
End Property

Public Property Set t_Txt(ByRef tTxt As TextBox)
    Set Txt = tTxt
End Property

'Event Subroutines Section
'-------------------------
Private Sub Txt_Exit(Cancel As Integer)
Dim i As Integer, Msg As String
Dim info As Integer

Select Case Txt.Name
    Case "Quantity"
        i = Nz(Txt.Value, 0)
        msg = ""
        If i < 1 Or i > 10 Then
            Msg = "Valid Value Range 1 - 10 Only."
            info = vbCritical
            MsgBox Msg, vbOK + info, "Quatity_Exit()"
            Cancel = True
        Else
            Msg = "Quantity: " & i & " Valid."
            info = vbInformation
            MsgBox Msg, vbOK + info, "Quatity_Exit()"
        End If
        
    Case "UnitPrice"
    
        i = Nz(Txt.Value, 0)
        Msg = ""
        If i <= 0 Then
            Msg = "Enter a Value greater than Zero!"
            info = vbCritical
            Cancel = True
            MsgBox Msg, vbOK + info, "UnitPrice_Exit()"
        Else
            info = vbInformation

            MsgBox "Unit Price: " & i, vbOK + vbInformation, "UPrice_Exit()"
        End If

    End Select

End Sub

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

Select Case Txt.Name
    Case "TotalPrice"
        frm!TotalPrice = (frm!Quantity * frm!UnitPrice)
        frm.TotalPrice.Locked = True
End Select
End Sub

Private Sub txt_LostFocus()
Select Case Txt.Name
    Case "Quantity", "UnitPrice", "TotalPrice"
        With Txt
            .backcolor = &HFFFFFF
            .forecolor = 0
        End With
End Select
End Sub

Create a new Class Module and change its name to WrapTextBox. Copy and paste the Code given above into the Class Module and save the file.

The naming convention you choose for your wrapper class, such as "WrapTextBox," is flexible and can be customized according to your preference and coding standards. The term "Wrap" in the class name often indicates that the class serves as a wrapper or encapsulation for another class or object.

In this case, the "WrapTextBox" class serves as a wrapper for the 'Access.TextBox' class and the Access.Form class objects. It encapsulates the functionality related to capturing and handling events fired from the TextBoxes on the form.

By declaring the TextBox instance with the WithEvents keyword in the WrapTextBox class, you establish a direct connection between the TextBox object on the form and the event handlers within the WrapTextBox class. This allows the WrapTextBox class to capture and execute the event subroutines associated with the TextBox on the Form.

The purpose of using a wrapper class like WrapTextBox is to centralize the event-handling logic for multiple TextBox objects and provide a modular and scalable approach to managing those events. The wrapper class acts as an intermediary between the form and the individual TextBox instances, making it easier to handle events and apply common functionality across multiple TextBoxes.

Ultimately, the specific name you choose for the wrapper class should align with your project's naming conventions and accurately represent its purpose as a wrapper for TextBox objects.

How to capture TextBox Events originating from the Form in a single instance of the TextBox object in the Wrapper Class? In last week's demo trial runs, we declared three TextBox instances for Quantity, Unit Price, and Total Price. That is the new trick we will explore here.

We used a generic name, Txt, for the TextBox instance declaration in the WrapTextBox Class. The Property Procedure Code of the TextBox and Form is transferred as it is from Class Module Class 3_1.

Since Txt is the TextBox object instance name, we must use this name as the Event Subroutine name prefix in all cases, rather than using the TextBox Control names on the Form. Besides that, in earlier trial runs, there were a total of eight Event Procedures: for Quantity and UnitPrice TextBoxes (OnExit, OnGotFocus, and OnLostFocus), three Event Procedures each, and two (OnGotFocus & OnLostFocus) for TotalPrice TextBox. But here, there are only three physical Event Subroutines: Sub txt_Ext(), Sub txt_GotFocus(), and Sub txt_LostFocus().

The logic is simple: when the Exit() Event fires from the Quantity or UnitPrice TextBoxes, the wrapper class calls the Sub txt_Exit() Event Subroutine. We already enabled the OnExit Event in Quantity and UnitPrice Textboxes on the Form. At this point, it is unknown exactly from which TextBox the Event is fired. Because both the TextBox's Exit Event uses the same txt_Exit() Event Procedure Name. We need to identify the name of the TextBox that fired the event, and then only the specific Validation check can be executed for each TextBox. The Select Case ... Case ... End Select structure runs a test and identifies the TextBox name. Based on that, the corresponding Event Subroutine is executed as shown above.

Note: Assume that there are 15 TextBoxes on the Form, and you enabled all the Events of all the TextBoxes without checking their Names. Then you write the Event Subroutine of a few TextBoxes for their selected Events by checking their names; only those Event Subroutines will be executed, other TextBoxes and enabled Events are ignored.

The same procedure can be followed to identify the Events OnGotFocus and OnLostFocus and write their Code in the same way. Since the LostFocus Event Procedure applies to all TextBox Controls to reset the background, no testing was necessary.

Create another Class Module with the name WrapObjects_Init. Copy and paste the following Event-enabling Code into the Class Module and save it.

Option Compare Database
Option Explicit

Private iTxt As WrapTextBox 'Instance of WrapTextBox
Private TxtArray() As WrapTextBox 'TextBox Array
Private ifrm As Access.Form

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

Public Property Set m_Frm(ByRef mfrm As Form)
    Set ifrm = mfrm

Call class_Init
End Property

Private Sub class_Init()
Dim ctl As Control
Dim j As Integer

Const EP = "[Event Procedure]"

'Scan for TextBox Controls on Form
'and Enable the required Event Procedures

For Each ctl In ifrm.Controls

    Select Case TypeName(ctl)
        Case "TextBox"
            Select Case ctl.Name
                Case "Quantity"
        
                Set iTxt = New WrapTextBox 'Create Instance
                Set iTxt.tx_Frm = ifrm 'assign Form reference
                Set iTxt.t_Txt = ctl   'assign TextBox reference
            
                iTxt.t_Txt.OnExit = EP
                iTxt.t_Txt.OnGotFocus = EP
                iTxt.t_Txt.OnLostFocus = EP
        
                j = j + 1
                ReDim Preserve TxtArray(1 To j)
                Set TxtArray(j) = iTxt          'Save it in Object Array
                Set iTxt = Nothing              'Erase temp Instance

                Case "UnitPrice"
                    Set iTxt = New WrapTextBox 'Create Instance
                    Set iTxt.tx_Frm = ifrm
                    Set iTxt.t_Txt = ctl
            
                    iTxt.t_Txt.OnExit = EP
                    iTxt.t_Txt.OnGotFocus = EP
                    iTxt.t_Txt.OnLostFocus = EP
        
                j = j + 1
                ReDim Preserve TxtArray(1 To j) 'we will use a better method than Array
                Set TxtArray(j) = iTxt          'Save it in Object Array
                Set iTxt = Nothing              'Erase temp Instance
    
            Case "TotalPrice"
                Set iTxt = New WrapTextBox      'Create Instance
                Set iTxt.tx_Frm = ifrm
                Set iTxt.t_Txt = ctl
            
                iTxt.t_Txt.OnGotFocus = EP
                iTxt.t_Txt.OnLostFocus = EP
        
                j = j + 1
                ReDim Preserve TxtArray(1 To j)
                Set TxtArray(j) = iTxt           'Save it in Object Array
                Set iTxt = Nothing               'Erase temp Instance
            End Select
        
        Case "CommandButton"
            Select Case ctl.Name
                Case "cmdClose"
                    'Code
            End Select
    End Select
Next

End Sub

Private Sub Class_Terminate()
    Set ifrm = Nothing
    Erase TxtArray
End Sub

VBA Code Review.

In last week’s demo, we declared three separate TextBox object variables—Qty, UPrice, and Total. That approach works fine when dealing with just a few controls. However, if a form has 25 textboxes, declaring 25 different variables and writing matching Set statements quickly becomes tedious and impractical.

The same issue arises with event handling. In the demo, we manually enabled events like OnExit, OnGotFocus, and OnLostFocus for each textbox. While manageable for three controls, it leads to repetitive boilerplate code when scaled up. Similarly, writing one event procedure per control-event pair meant we had eight procedures in the demo, but 25 textboxes and three events each, we’d end up with 75 separate procedures—clearly not sustainable.

The key takeaway is that last week’s example demonstrated the mechanics, but it doesn’t scale. Our next step is to replace these hard-coded references with a more generic, reusable structure: one that uses a wrapper class for each control, managed through a collection, and routes events through a centralized handler.

The solution lies in using a more flexible and scalable approach. Creating a standalone Class Module defines a custom TextBox Class that encapsulates the event-handling logic. This Class can be instantiated multiple times, allowing us to handle events for any number of TextBoxes dynamically. The WrapTextBox Class is created for that.

In the updated code, you'll find the implementation of the custom TextBox class (WrapTextBox Class) in the standalone Class Module. This class will contain the necessary event procedures and any additional functionality required. The WrapObjects_Init Module will then instantiate instances of this custom TextBox class for each TextBox control on the Form.

This approach enables the centralization of event handling logic, making it more manageable and scalable, regardless of the number of Text Boxes present on the Form.

In the global declaration section of the demo form’s class module, we declare an instance of the WrapObjects_Init class. When the form opens, this class is instantiated and becomes active in memory. The WrapObjects_Init class, in turn, creates two instances of the WrapTextBox class, which are also loaded into memory.

As a result, when the form is open, three sets of class objects are active concurrently:

The form’s own class module.
The WrapObjects_Init class.
Two instances of the WrapTextBox class.

The functional diagram below shows the relationships among these objects and how they remain active in memory while the form is open.

Class Modules in Memory.

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

In our earlier demo, the segment Class_Init() has now been refactored into a standalone class module named WrapObjects_Init. Within this module, an instance of the WrapTextBox class, named iTxt, is declared in the global declaration area.

A second Instance of the WrapTextBox Class Module is declared as an Array object with an unspecified number of elements, with the object name TxtArray() in there.

The first one, iTxt, is a temporary instance to assign the TextBox's reference one by one from the Form, to access its Event Properties to enable the required Events, and then pass it on to the TxtArray(). After that, iTxt initialized to take the next TextBox reference from the Form.

The TxtArray() will be re-dimensioned dynamically, based on the number of Textboxes on the Form, their events enabled before the TextBox is stored in the Array. In both of these global declarations, the keyword WithEvents is not used, because the TextBox object declaration in the Global area of the WrapTextBox Class is already qualified with the Keyword WithEvents to enable them to listen to the TextBox Events on the Form. Each TextBox Reference from the Form is assigned to different Instances of the WrapTextBox Class, and required Events are enabled and stored in the TxtArray().

Access.TextBox Class Instances

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

After these two declarations, as usual, the Form Object declaration is also inserted in the Global declaration area with the Property name iFrm. The Form object that is passed from the Form Class Module through the Form Property Procedure m_Frm() is assigned to the iFrm Property. This will be passed on to the Frm Property declaration in the global area of the WrapTextBox Class through the tx_Frm() Property Procedure.

It is essential to get familiar with the Code in this particular Class Module and its functions. The WrapObjects_Init Class Module, along with the open Form and the TextBox Wrapper Class, should work all together instantly when the Form is open. It should enable the Events of the required Controls on the Form. The WrapTextBox Class should capture the events when fired from the Form and execute the Event Subroutine. There will be only one Class_Init Wrapper Class for a Form, but there can be several Object Wrapper Classes, one for each type of Control on the Form, like Command Buttons, Combo boxes, and others.

The Class_Init() Subroutine.

Let us check the Class_Init() Subroutine Code. There are two local variable declarations. The first one is a Control Variable Ctl to scan through the Form to find the TextBoxes, and the second one is an Integer Variable. The third one is a constant that holds the text "[Event Procedure]".

The For Each ctl In ifrm.Controls are the start of the For ... Next Loop, and in the next step, we should check whether the first Control found in the Ctl Variable is the Type of control that we are looking for, the TextBox. The Select Case Typename(Ctl) does that. We are only interested in the TextBox Controls on the Form. So the Case "TextBox" does just that. If that is proven true, then we will further filter them by their names with an inner Select Case 'Ctl.Name' check and take only the Quantity, UnitPrice, and TotalPrice TextBoxes on the Form. Let us bring that Code Segment here and take a close look at the VBA Code.

For Each ctl In ifrm.Controls

    Select Case TypeName(ctl)
        Case "TextBox"
            Select Case ctl.Name
              Case "Quantity"
        
                Set iTxt = New WrapTextBox 'Create an Instance of WrapTextBox Class
                Set iTxt.tx_Frm = ifrm     'Pass the Form Object to WrapTextBox Property
                Set iTxt.t_Txt = ctl       'Pass the TextBox Control 'Quantity'
            
                iTxt.t_Txt.OnExit = EP
                iTxt.t_Txt.OnGotFocus = EP
                iTxt.t_Txt.OnLostFocus = EP
        
                j = j + 1
                ReDim Preserve TxtArray(1 To j)
                Set TxtArray(j) = iTxt          'Save it in Object Array
                Set iTxt = Nothing              'Erase temp Instance

              Case "UnitPrice"

When we detect the Quantity TextBox Control in Ctl, then we instantiate the WrapTextBox Object in the local iTxt object. Remember, the TextBox Class is wrapped in the WrapTextBox Class. After creating an Instance of the WrapTextBox Class, we can access its TextBox and Form Properties through the Public Property Procedures. Through these Property Procedures, assign the Form object iFrm and Quantity TextBox Control Reference in Ctl, and they are passed on to their corresponding Properties in the WrapTextBox Class through its own Public Property Procedures:

            Select Case ctl.Name
                Case "Quantity"
        
                Set iTxt = New WrapTextBox 'Create an Instance of WrapTextBox Class
                Set iTxt.tx_Frm = ifrm     'Pass the Form Object to WrapTextBox Property
                Set iTxt.t_Txt = ctl       'Pass the TextBox Control 'Quantity' Reference

The next step in this Module is to enable the required Event Properties of the Quantity TextBox to fire the Events so that they can be captured in the WrapTextBox Class Instance and execute the related Event Subroutines for the required Task. We need to enable three Events: OnExit, OnGotFocus, and OnLostFocus. The VBA Code is given below.

                iTxt.t_Txt.OnExit = EP     'Enable Event "[Event Procedure]"
                iTxt.t_Txt.OnGotFocus = EP             "
                iTxt.t_Txt.OnLostFocus = EP            "

Remember, we are working on the first instance of the WrapTextBox Class Object. In last week's trial run, we created the TextBox instances manually with the names: Qty, Uprice, and Total for the TextBox Controls Quantity, UnitPrice, and TotalPrice on the Form. Earlier, we wrote the Event Procedure in the WrapObjects_Init Class (Class3_1 Class) Module itself. That has changed now; a separate WrapTextBox Class Module with TextBox and Form Object Properties takes care of the event-handling task.

The TextBox Class Object Instance is declared with the WithEvents keyword to capture its built-in Events and execute the Event Subroutine in the WrapTextBox Class. This declaration draws a parallel to dragging and dropping a TextBox object on the Form.

Pay close attention to the next few lines of Code and their usage in the programme.

Our WrapTextBox instance was created in the WrapObject_Init Class, and the active Form object instance is assigned to the Frm Property and also assigned to the Quantity TextBox instance. The required events for the Quantity TextBox on the Form are enabled. This particular WrapTextBox instance is created now for the Quantity TextBox only.

We will create two more instances of the WrapTextBox Class for the UnitPrice and TotalPrice TextBoxes on the Form using the same iTxt WrapTextBox.

Before that, the current Instance iTxt must be saved in the TxtArray() in memory, and set the iTxt instance to nothing for reuse.

We declared a WrapTextBox Array with the name TxtArray(). The exact number of array elements is not known beforehand, so the array is re-dimensioned dynamically as needed. Now, the Quantity TextBox is current and enabled with the required Events OnExit, OnGotFocus, and OnLostFocus (but only one of these Events will be fired at one time), and is ready to save the instance after Redimensioning the Array by incrementing its array elements count J by 1. The Code segment is given below.

                J = J + 1
                ReDim Preserve TxtArray(1 To J) 'preserve the existing element value
                Set TxtArray(J) = iTxt          'Save it in Object Array
                Set iTxt = Nothing              'Erase temp Instance

The Array index Variable is incremented by 1. The TextArray() of WrapTextBox is Redimensioned for 1 to 1 elements, and the keyword Preserve ensures that the earlier elements' data, if any, are preserved. The statement Set TxtArray(j) = iTxt saves the WrapTextBox object instance iTxt in the first Array element of the TxtArray() object collection. The next statement, Set iTxt = Nothing, releases the memory occupied by this temporary WrapTextBox Instance for reuse.

When we instantiate an object in memory, it occupies a certain area of the Computer's memory with a specific reference address. When this object instance is saved in the Array, its address is copied into the array element and held there. This enables us to release the temporary TextBox instance and reuse it for other TextBoxes on the Form.

Therefore, the statement Set iTxt = Nothing releases its memory Reference. Since TxtArray is holding the WrapTextBox Instance reference so long as all the objects: the Form, WrapObjects_Init, and WrapTextBox Classes are active, the saved Instance of Object in the Array will be safe and active too.

When we assign another TextBox Instance to the same temporary object, iTxt, it will look for free space in memory and will not overwrite existing objects held in memory.

When the UnitPrice and TotalPrice Text Boxes are detected on the Form, the same process is repeated, and their corresponding Events will be enabled. The Array is redimensioned, and the object instances are saved in the Array Elements.

When events are fired from these TextBoxes, they are captured in their corresponding Array element-based Event Subroutines and executed.

Now, let us design our Demo Form.

Make a Copy of the last episode Form, with the name Form1_RaiseEvent_4, and rename it as frmMultiple_TextBoxes.

Right-click to open Large Image in New Tab/Window.
Open the frmMultiple_TextBoxes Form in Design View and change the Heading Label Caption to 'Events of Multiple Text Boxes on Form'.
Display the Form Module.

Copy and paste the following VBA Code into the Form Module, overwriting the existing Code:

Option Compare Database
Option Explicit

Private C As WrapObjects_Init

Private Sub Form_Load()
  Set C = New WrapObjects_Init
  Set C.m_Frm = Me
End Sub

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

Select Compile from the Debug Menu, and compile the VBA Code to ensure it is error-free. Save the Form with the Code.
Open the Form in Normal View.
Enter a Value of 10 or less in the Quantity TextBox and press the Enter Key. It will display a message displaying the entered value from the Quantity TextBox.
Enter some numerical value in the Unit Price TextBox and press the Enter Key. It should display the entered value in a MsgBox.

When the Total Value Textbox receives Focus, the GotFocus Event fires and displays the result of the (Quantity * UnitPrice) Value in the TotalPrice TextBox.

Try entering a value larger than 10 in the Quantity TextBox and see what happens.

Demo Database Download.

The Demo Database Download Link is given below:

Streamline6.zip