WithEvents TexBox and CommandButton Control Arrays

Introduction.

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

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

1. WithEvents and Access Report Event Sink
2. WithEvents and Report Lines Hiding
3. WithEvents and Report Lines Highlighting

So far, we have worked exclusively with TextBox arrays to capture built-in events from the form within a Class Module, executing the corresponding subroutines to perform validation checks or other required functions.

The TextBox Events Capturing Route Map.

1. Create a Base Class Module that defines Text Box Control and Form properties.

2. Add AfterUpdate() and LostFocus() subroutines to this class to handle these built-in events from the text boxes on the form. Inside each subroutine, identify the text box that triggered the event and execute the corresponding code based on its name.

3. Next, create a Derived Class Module using this Text Box class as the base class.

4. Within the derived class, create an array of base-class objects so that each element can handle the required events for a specific text box. This approach eliminates the need to keep empty AfterUpdate() and LostFocus() subroutine stubs on the form’s class module, which are otherwise required just to raise these events.

5. Because the base class contains only a single txt property (which can hold a reference to only one text box at a time), having multiple class objects—one per text box—is essential.

6. Instead of using an array of class objects, a Collection object can be used to store all the individual text box class instances. This method has proven to be a more flexible and reliable way to manage multiple text box class objects.

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

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

One important point to remember:

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

The TextBox and CommandButton Arrays.

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

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

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

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

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

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

Class Module for TextBox: ClsText.

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

Option Compare Database
Option Explicit

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

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

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

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

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

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

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

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

End Sub

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

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

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

Exit Sub

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

End Sub
 

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

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

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

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

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

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

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

'Read value from mytxt
    mytxtvalue = mytxt.Value

'Write value to mytxt
    mytxt = 25

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

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

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

Class Module of Command Button: ClsCmdButton.

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

Option Compare Database
Option Explicit

Private WithEvents Btn As Access.CommandButton

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

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

Private Sub Btn_Click()
Dim BtnName As String

BtnName = Btn.Name

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

End Sub

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

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

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

• Command8: Opens the form Form1.

• Command9: Displays a message.

The Derived Class Module: ClsTxtBtn_Derived

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

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

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

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

Option Compare Database
Option Explicit

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

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

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

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

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

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

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

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

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

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

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

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

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

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

ReDim Preserve T(1 To tcount)

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

ReDim Preserve Qty(1 To cnt) As Single

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

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

Set T(tcount).p_Frm = m_Frm

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

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

Set T(tcount).p_txt = ctl

At this stage, we check the TextBox name to determine which built-in Events should be enabled. Different TextBoxes may require different Events depending on the purpose of that data field.

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

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

' Example statements to enable Events

T(tCount).p_txt.AfterUpdate = Evented

T(tCount).p_txt.OnLostFocus = Evented

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

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

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

Set B(bcount).p_Btn = ctl

Next, the Command Button name is checked to determine which actions or events should be enabled. For demonstration purposes, the Click event is enabled for each Command Button.

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

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

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

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

The Form frmClassArray Class Module Code.

Option Compare Database
Option Explicit

Private D As New ClsTxtBtn_Derived

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

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

Downloads

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

Download TextandButton2007.zip

Download TextandButton2003.zip

Links to WithEvents ...Tutorials.

1. WithEvents MS-Access Class Module Tutorial
2. WithEvents and Defining Your Own Events
3. withevents Button Combo List TextBox Tab
4. Access Form Control Arrays and Event Capturing
5. Access Form Control Arrays and Event-2
6. Access Form Control Arrays and Event-3
7. WithEvents in Class Module for Sub-Form TextBox Events
8. WithEvents in Class Module and Data Entry
9. WithEvents and Access Report Event Sink
10. WithEvents and Report Lines Hiding
11. WithEvents and Report Lines Highlighting
12. Withevents TextBox and Command Button Arrays
13. Withevents TextBox CommandButton Dictionary
14. Withevents and all Form Control Types