WithEvents Textbox CommandButton Dictionary

Continued from Last Week's Post

So far, we have used the Collection object to hold all the Form controls' Class Object Instances as an alternative to using class object Arrays.

If you have landed directly on this page, please go through the earlier post, “WithEvents TextBox and CommandButton Control Arrays”,  before continuing.

In that earlier post, we used class object arrays of TextBox and Command Button controls on the Form. The main challenge with this approach was the need to maintain separate array indexes for each control type’s class object instances, as well as repeatedly re-dimensioning the arrays. When several different types of form controls are involved—such as ComboBoxes, ListBoxes, OptionGroups, TabControls, and others—this method quickly becomes cumbersome and complex.

We overcame this complexity by using a Collection object as a container for all the control class object instances.

We have already explored a few examples using the Collection object in some of our earlier posts. Links to all our posts on Form and Report control event capturing are provided at the end of this article for your reference.

Usage of Dictionary Object, Replacing Array

Here, we will replace the Collection Object with the Dictionary Object to hold all the Form Controls' Class Object Instances.  Let us see what difference it makes to implement the Dictionary method.

Here, I will bring in last week's Derived Class Object (ClsTxtBtn_Derived)  Code without any change.  The full VBA Code of last week's Class Module Derived Object 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 ClsText and ClsCmdButton classes are defined as array properties—T() and B()—with an initially undefined number of elements. The Form property (m_Frm) is declared next, followed by its Get and Set property procedures for assigning and retrieving the active Form object.

After the Form object is assigned in the Set property procedure, the Class_Init() subroutine is called to enable the required event procedures for the form’s controls.

In this example, three text boxes (Text2, Text4, Text6) are enabled only for the AfterUpdate() and LostFocus() events, since the ClsText class module currently contains subroutines only for these events.

Note: Even if the ClsText class module contains subroutines for other events (such as BeforeUpdate, GotFocus, KeyDown, KeyUp, etc.), not all of them need to be enabled for every project. Only the events required for a specific form are activated in the derived class module, while the other event subroutines remain dormant until needed for a particular form or report.

The Form and TextBox control references are passed from the Derived Object to the ClsText Class Object Properties through the following statements:

Set T(tCount).p_frm = m_Frm
Set T(tCount).p_txt = ctl

Command Button Control reference is passed to the ClsCmdButton Class Module.  The Command Button Click Event is enabled.

This procedure is repeated for each Control on the Form in the Derived Class Object Module: ClsTxtBtn_Derived.

Logical Error in VBA Code

The ClsTxtBtn_Derived Class Module Code works perfectly for our earlier example.  But, there is a logical error in the code, and we will correct it in the later versions of the Code. 

The placement of the following lines of Code under the Case "TextBox is not in the correct location because there is a chance that the Class Module Object will occupy more memory space.

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

The issue with the current code placement is that if there are extra text boxes on the form without any events enabled for them, the code will still instantiate class objects for those controls and add them to the array, unnecessarily consuming memory. This happens silently without causing any functional errors, but it is logically incorrect.

To correct this, the lines that instantiate and add the class object must be placed inside the corresponding Case blocks—immediately below each Case "Text2", Case "Text4", and Case "Text6" statement. This ensures that only the intended controls with events enabled will get their class object instances created.

A similar change is required for the Command Button Case statements.

However, if all the TextBoxes and Command Buttons on the form are intended to have the specified events enabled, then no code change is necessary.

In our demo forms, we only used two or three text boxes and a few command buttons, and we enabled events for all of them. In such situations, the current code placement is acceptable and intentional, as it keeps the logic simple and avoids repeating the instantiation code inside each Case statement.

The Dictionary Object.

If you have not come across the Dictionary Object and its usage so far, then please visit the following links, or at least the first two:

1. Dictionary Object Basics
2. Dictionary-object-basics-2
3. Sorting Dictionary Object Keys and Items
4. Display Records from Dictionary to Form
5. Add Class Objects as Dictionary Items
6. Update Class Object Dictionary Item

When comparing the Collection and Dictionary objects, both support the Add method and accept two parameters—Key and Item—but their usage differs:

Dictionary object syntax:

Object.Add ItemKey, Item

Both parameters are mandatory.

Collection object syntax:

Object.Add Item, ItemKey

The ItemKey is the second parameter and is optional.

Unlike the Dictionary object, the ItemKey in a Collection is optional. However, since all control names on a form are unique, they can serve as ItemKey values when using a Dictionary.

The Dictionary object is part of the Windows Scripting Runtime library and can be created in VBA with the following statement:

Dim D as Object
Set D = CreateObject("Scripting.Dictionary")

Alternatively, you can enable the Microsoft Scripting Runtime library in your project by selecting it from Tools → References in the VBA editor. After adding this reference, you can declare a Dictionary object using early binding, as shown below:

Dim D As Dictionary

Using early binding offers the added benefit of IntelliSense support—when you type the declared object name followed by a dot (for example, D.), the VBA editor will display a list of all available properties and methods.

The new derived Class Module ClsTxtBtn_Dictionary (shown below) demonstrates this approach. Until now, we have used a Collection object as an alternative to arrays of class object instances. In this section, we will explore how to use the Dictionary object as a container for class object instances (specifically, for TextBox and CommandButton controls).

Option Compare Database
Option Explicit

Private T As ClsText
Private B As ClsCmdButton
Private m_Frm As Access.Form
Private D As Object

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
   Const Evented = "[Event Procedure]"
Set D = CreateObject("Scripting.Dictionary")
For Each ctl In m_Frm.Controls
   Select Case TypeName(ctl) 'Type name TextBox or CommandButton ?
     Case "TextBox"
         Select Case ctl.Name
            Case "Text2"
                Set T = New ClsText 'create new instance of Class Module
                Set T.p_frm = m_Frm 'pass Form Object
                Set T.p_txt = ctl 'pass the TextBox control

                    T.p_txt.AfterUpdate = Evented ' enable AfterUpdate Event
                    T.p_txt.OnLostFocus = Evented ' enable OnLostFocus Event
                
          'Add ClsTxt Class Object as Dictionary Object Item
                    D.Add ctl.Name, T 'TextBox Name as ItemKey, Class Instance as Item
                Set T = Nothing 'erase ClsText instance
                
            Case "Text4"
                Set T = New ClsText
                Set T.p_frm = m_Frm
                Set T.p_txt = ctl

                    T.p_txt.AfterUpdate = Evented
                    T.p_txt.OnLostFocus = Evented
                    
          'Add ClsTxt Class Object as Dictionary Object Item
                    D.Add ctl.Name, T
                Set T = Nothing
                
            Case "Text6"
                Set T = New ClsText
                Set T.p_frm = m_Frm
                Set T.p_txt = ctl

                    T.p_txt.AfterUpdate = Evented
                    T.p_txt.OnLostFocus = Evented
                
          'Add ClsTxt Class Object as Dictionary Object Item
                    D.Add ctl.Name, T
                Set T = Nothing
        End Select
                
      Case "CommandButton"
        
        Select Case ctl.Name
            Case "Command8"
                Set B = New ClsCmdButton 'create new instance of ClsCmdButton
                Set B.p_Btn = ctl 'pass CommandButton control to Class Module
           
                    B.p_Btn.OnClick = Evented 'Enable Click Event
                    
            'Add ClsCmdButton Class Instance as Dictionary Item
                    D.Add ctl.Name, B ' Command Button Name as ItemKey, Class Instance as Item
                Set B = Nothing 'erase ClsCmdBtn instance
                
            Case "Command9"
                Set B = New ClsCmdButton
                Set B.p_Btn = ctl
           
                    B.p_Btn.OnClick = Evented
                    
            'Add ClsCmdButton Class Instance as Dictionary Item
                    D.Add ctl.Name, B
                Set B = Nothing
        End Select
      End Select
    Next
End Sub

Preparing for a Trial Run.

1. Steps to Implement the ClsTxtBtn_Dictionary Class Module

   1. Create the Class Module

      • In the demo database you downloaded from last week’s post, open the VBA editor (Alt + F11).

      • From the Insert menu, select Class Module.

      • In the Properties window, set its (Name) property to:
        ClsTxtBtn_Dictionary.

   2. Add the Code

      • Copy the ClsTxtBtn_Dictionary class code provided above.

      • Paste it into the newly created class module.

      • From the Debug menu, select Compile <your database name> to recompile the project and confirm that no errors occur.

   3. Create a New Form

      • In the Navigation Pane, right-click the existing form frmClassArray and select Copy.

      • Right-click again and select Paste.

      • In the prompt, give the new form the name:
        frmClass_Dictionary.

   4. Update the Form’s Code

      • Open frmClass_Dictionary in Design View.

      • Open its Code Module window (right-click the form’s title bar → Build Event → Code Builder, or press F7).

      • Replace its existing code with the new code provided below.

   Option Compare Database
   Option Explicit
   
   Private A As New ClsTxtBtn_Dictionary
   
   Private Sub Form_Load()
      Set A.mfrm = Me
   End Sub
   
   

2. Save the Form with the changed code.

3. Open the Form in Normal View and try the TextBoxes and Command Buttons to test that the LostFocus, After Update, and Command Button Clicks work as before.

Revised Code Segments.

Since all three TextBoxes (Text2, Text4, and Text6) are enabled by the same set of Events (AfterUpdate & LostFocus), the Case statements can be clubbed into one line and can avoid duplication of Code as given below:

Select Case ctl.Name
            Case "Text2", "Text4", "Text6"
                Set T = New ClsText 'create new instance of Class Module
                Set T.p_frm = m_Frm 'pass Form Object
                Set T.p_txt = ctl 'pass the TextBox control

                    T.p_txt.AfterUpdate = Evented ' enable AfterUpdate Event
                    T.p_txt.OnLostFocus = Evented ' enable OnLostFocus Event
                
          'Add ClsTxt Class Object as Dictionary Object Item
                    D.Add ctl.Name, T 'TextBox Name as ItemKey, Class Instance as Item
                Set T = Nothing 'erase ClsText instance
        End Select

Similarly, both the Command buttons have only one common Event, the Click Event.  Hence, their Code can also be combined into a single step, like the following code segment:

Select Case ctl.Name
            Case "Command8", "Command9"
                Set B = New ClsCmdButton 'create new instance of ClsCmdButton
                Set B.p_Btn = ctl 'pass CommandButton control to Class Module
           
                    B.p_Btn.OnClick = Evented 'Enable Click Event
            'Add ClsCmdButton Class Instance as Dictionary Item
                    D.Add ctl.Name, B ' Command Button Name as ItemKey, Class Instance as Item
                Set B = Nothing 'erase ClsCmdBtn instance
        End Select

The Revised ClsTxtBtn_Dictionary Code.

The full Class Module Code with the above change is given below:

Option Compare Database
Option Explicit

Private T As ClsText
Private B As ClsCmdButton
Private m_Frm As Access.Form
Private D As Object

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
   Const Evented = "[Event Procedure]"
Set D = CreateObject("Scripting.Dictionary")
For Each ctl In m_Frm.Controls
   Select Case TypeName(ctl) 'Type name TextBox or CommandButton ?
     Case "TextBox"
         Select Case ctl.Name
             Case "Text2", "Text4", "Text6"
                Set T = New ClsText 'create new instance of Class Module
                Set T.p_frm = m_Frm 'pass Form Object
                Set T.p_txt = ctl 'pass the TextBox control
                
                    T.p_txt.AfterUpdate = Evented ' enable AfterUpdate Event
                    T.p_txt.OnLostFocus = Evented ' enable OnLostFocus Event
               
               'Add ClsTxt Class Object as Dictionary Object Item
                    D.Add ctl.Name, T 'TextBox Name as ItemKey, Class Instance as Item
                Set T = Nothing 'erase ClsText instance
        
        End Select
                
      Case "CommandButton"
        
        Select Case ctl.Name
        'Both Command Buttons have only the same Click Event
            Case "Command8", "Command9"
                Set B = New ClsCmdButton 'create new instance of ClsCmdButton
                Set B.p_Btn = ctl 'pass CommandButton control to Class Module
           
                    B.p_Btn.OnClick = Evented 'Enable Click Event
                    
            'Add ClsCmdButton Class Instance as Dictionary Item
                    D.Add ctl.Name, B ' Command Button Name as ItemKey, Class Instance as Item
                Set B = Nothing 'erase ClsCmdBtn instance
        End Select
      End Select
    Next
End Sub

You may now create a new Class Module, paste the code provided earlier, and save it. Next, update the Form Module code to reference this new module name, and test the form controls to verify that they function as before.

So far, we have worked with only two types of controls—TextBox and CommandButton—using their respective class modules (ClsText and ClsCmdButton). But what if we need to handle all the types of controls on a form, such as ComboBoxes, ListBoxes, Tab Controls, and Option Groups? Each of these would require its own dedicated Class Module, similar to ClsText and ClsCmdButton.

We will conclude this topic with one or two more posts, where we will incorporate nearly all the common control types on a form and demonstrate how to handle their events through class modules.

If you have followed the earlier posts (links numbered 1 to 12 listed below), you should have no difficulty defining and configuring class modules for these additional controls in a similar way. Their Click events can then be enabled, making them ready for testing on the form.

Links to WithEventsTutorials.

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

Read More

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

Read More