WithEvents Textbox CommandButton Dictionary

Continued from Last Week's Post

So far we have used the Collection Object, to hold all the Class Object instances of the Form Controls as an alternative to Class Object Arrays.

If you have directly landed on this page, then please go through last Week's Post: WithEvents TextBox and CommandButton Control Arrays and then continue on this page.

Last week we have used Class Object Arrays of Text Box and Command Button Controls on the Form.

The difficulty of this method was to maintain separate Array Indexes for each type of control's Class Object instances, besides re-dimensioning each Array element every time.  When there are several types of Form Controls, like Combo Boxes, List Boxes, Option Groups, Tab-Control, and others this approach will grow into a very complex situation.  Earlier, we were able to alleviate this complexity by using Collection Object, as the container of all the controls Class Object Instances.

We have tried a few examples with Collection Object in some of our earlier posts.  Links to all the Posts, on Form and Report Control's Event Capturing topic, are given at the end of this post for your reference.

Usage of Dictionary Object, Replacing Array

Here, we are going to use the Dictionary Object, instead of Collection Object, to hold all the Form Controls' Class Object Instances.  Let us see how it works and what it takes 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 Objects T() & B() Properties, with an undeclared number of array elements.  The Form Property m_Frm is declared as the third item followed by the Get/Set Property Procedures for the Form Object. 

After assigning the Form Object in the Set Property Procedure the Class_Init() Sub-Routine is called to enable the required Form controls' Event Procedures.

Three Text Boxes (Text2, Text4, Text6) are enabled with the AfterUpdate() and LostFocus() Events only because we didn't set up any other Sub-Routine in the Class Module ClsText to capture other Events from the TextBoxes.

Note: Even if we have sub-routine code for, say BeforeUpdate,  GotFocus, KeyDown, KeyUp, etc., in the ClsText Class Module we may not use all of them for a particular Project.  Whatever Event we need to capture in a particular Form will only be enabled in the Derived Class Module.  Other sub-routines will remain in the Class Module till we encounter a need for them in 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 to place, 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 above code placement is that if there are some extra text boxes on the Form, without any Event enabled on them, even then the Class Object will be instantiated for those cases also and added to the Array,  occupying extra memory space.  It will happen silently without any side effects.

The above code lines must be placed immediately below all the Case statements Case "Text2", Case "Text4", and Case "Text6" structures for the correction of the logical error.  This change is required for the Command Button Case statements also.  If all the TextBoxes and Command Buttons on the Form are enabled with some Event then no need for any change.

In all our demo Forms we have introduced two or three TextBoxes or a few Command Buttons and enabled some Events on all of them.  In those situations the above Code placement is correct and intentionally made that logical position to keep the code simplified, avoiding duplication of code, under 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

By Comparing the Collection Object with the Dictionary, both have the Add method and take two parameter values: ItemKey and Item.

Dictionary Object Syntax: Object.Add ItemKey, Item – both values are mandatory.

Collection Object  Syntax:  Object.Add Item, ItemKey – second parameter  optional.

Unlike Dictionary Object the ItemKey is the second parameter in Collection Object and is optional. Since all the control names on the Form are unique it can be used as Dictionary Object ItemKey.

Dictionary Object is part of Windows Scripting Language and needs to create an Object in VBA with the following statement:

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

Alternatively, you can select and add the Microsoft Scripting Runtime Library File to your Project from Tools - - > References Library List.  After that you can declare Dictionary Object as shown below:

Dim D as Dictionary

If you do this then it has an added advantage of displaying its list of Properties and Methods, when you type the declared Object name followed by a dot ( say D.)

The new derived Class Module ClsTxtBtn_Dictionary VBA Code is given below.  So far we have used Collection Object as an alternative to Class Object Instance Arrays.  Here we will learn the usage of Dictionary Object as a container of Class Object Instances (of TextBox and Command Button).

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. Create a new Class Module in the Demo database, you have downloaded from last week's post, with the name ClsTxtBtn_Dictionary.
2. Copy and Paste the above Code into the Class Module and save.
3. Select Compile from the Debug Menu to recompile the database and ensure that no errors are encountered.
4. Make a copy of the Form frmClassArray with a new name frmClass_Dictionary.
5. Open the Form frmClass_Dictionary in the design view.
6. Display the Form's Code Module and change the Code to match the Code lines given below:

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

7. Save the Form with the changed code.
8. Open the Form in Normal View and try the TextBoxes and Command Buttons to test the LostFocus, After Update, and Command Button Clicks work as before.

Revised Code Segments.

Since, all the 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 also can 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 create a new Class Module, Copy and Paste the above Code and save it.  Change the Form Module Code to incorporate the new name of this Module and try out the Form controls, to test whether all of them work as before.

We have tried only two types of controls, the Text Box and Command Button class Modules here.  How it will be when we have to deal with all types of controls  Combo, List, Tab Control, Option Group on the Form.  All of them need separate Class Modules, like ClsText and ClsCmdButton.  We will conclude this topic with one or two posts with almost all types of controls on the Form. 

If you have progressed through the earlier Posts (the links: No. 1 to 12) presented below, then you will not find any difficulty in defining and setting up Class Modules for those controls mentioned above in a similar way, with Click Event enabled for each one of them yet to be tested form controls.

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.

Hope that you have gone through the last three Posts on Report-based Event Trapping in Class Module and modifying Report controls in real-time.  The links to those Posts are given below for your ready 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 with only Text Box Arrays to capture built-in Events from the Form in Class Module and execute the Sub-Routines, which handle the required validation checks or other needed functions.

The TextBox Events Capturing Route Map.

1. Creates a Class Module with a Text Box Control and Form Properties.
2. Add Sub-Routine(s) for AfterUpdate() and LostFocus(), to handle these built-in Events from Text Box on the Form.  The Text Box name that triggered the event is identified in the Sub-routine and the Code is executed under the Text Box Name.
3. Creates a Derived Class Module using the Text Box Class Module as Base Class. 
4. Creates an Array of the Base Class enabling the required Text Box Events in each Array element, within the Derived Class Object. 
5. This will eliminate the need for keeping the AfterUpdate() and LostFocus() empty Sub-routine stubs on the Form Module, to Raise those Events on the Form Module, when it occurs.
6. An Array of Class Module with Text Box Property txt, one array element for each Text Box on the Form, is required because there is only one txt Property in the Base Class Module.   It can hold only one Text Box reference at a time.
7. Instead of Class Object Arrays,  adding Class Object instances into Collection Object Items is proved as a better option to hold all the Text Box Class instances.

In brief, our technique is to create a Class Module, with required Event Procedures, for each type of control, Text Box, Combo Box, List Box, Tab Control, and Options Group separately.  We insert the required Class Module Objects as Base class Properties in the Derived Class Object, depending on the control types on the Form.

Another point to remember is that, if you need to refer to a different control of the same type, other than the one that triggered the event, you must add an Access.Form Object as Property in the Base Class Module and assign the Form Object to that Property through the Derived Class Object.

The TextBox and CommandButton Arrays.

So far we have worked with Arrays of Class Module with TextBox Property only.  We will create a few TextBoxes and Command Buttons on a Form and learn how their references are being assigned to instances of  TextBox and Command Button Base Class Module Arrays, in the Derived Class Module.

A particular Class Object Array element captures the built-in Events of a particular Text Box or Command Button and executes the Event Procedure from within the Array Element.

Sample Image of the Form, with the name frmClassArray in Design View, is given below:

There are three Text Box Controls on the Form, with the names Text2, Text4, Text6 (need three singly dimensioned arrays with three elements), and two Command Buttons with the name Command8 and Command9 (an Array of two elements) of their respective Class Modules.

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 Access.Form Property variable frm is declared at the beginning of the Module followed by the Access.TextBox Property txt in the next line. 

The txt Property is declared with the keyword WithEvents, which enables capturing the Events (User-Defined or Built-in Events) taking place on the Form.

The frm Object Property is required to refer to a TextBox control, other than the Event-Triggered one if needed.  Both Properties are declared with Private Scope preventing direct access to the Properties from outside the Class Module.

The Get and Set Property Procedures p_Frm()   for frm Property and p_txt() for txt Property are the next steps. The Active Form Object is assigned to the frm Property, through the Form_Load() Event Procedure, not directly but through the Derived Class Module ClsTxtBtn_Derived.

The p_txt() Property Procedures are called from the Derived Class Module (ClsTxtBtn_Derived – the VBA Code is given after the Command Button Class Module ClsCmdButton Code) and the TextBox controls are assigned to each array element of the ClsTxt Class Module.

Once the Form Object is assigned to the frm Property, we can set a reference to any control on the Form to read or write values. 

Through the Event capturing Sub-Routines you can set a reference to a different TextBox control on the Form to Read/Write the value in the following manner:

'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 Text2, Text4, and Text6 control Events in their respective array elements separately.  You may write validation checks on values in the textbox and display an appropriate message or replace a different value etc.  In this sample subroutine, a common message is displayed with the Text Box name, to know that the Sub-Routine is executed on Event Triggering on the Form.

The txt_LostFocus() Event Procedure inserts the text msaccesstips.com, if the text box is left empty otherwise it will not show any message.

Note: When you try the demo run of the attached database tab through the text fields to insert the default text automatically, then edit them and press Tab or Enter Key to 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 keyword WithEvents to capture Events when they fire from the Command Button on the Form.

The Get/Set Property Procedures (p_Btn()) retrieves/assigns the Command Button Object to the Property.

In the Btn_Click() Event Procedure, we check the name of the Command Button to determine what to do if a particular Command Button is clicked.

Here, the Command8 button click will open a form: Form1.

Command9 button click will only display a message.

The Derived Class Module: ClsTxtBtn_Derived

.We plan to monitor and capture the enabled built-in Events from TextBoxes and Command Buttons on the Form and execute the Event Procedures in the Class Module Object instance. 

We have a Class Module for TextBox controls and another Class Module for Command Buttons.  We will use both Base Classes for the Derived Object ClsTxtBtn_Derived.

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 Object with an undeclared number of elements.  Likewise, the ClsCmdButton Class Module is also declared in the second line.  The Access.Form Object m_frm is declared to receive the active Form Object passed from the Form_Load() Event Procedure and acts as an intermediary to pass the reference to each array element of the ClsText Class Object.

Next the Get/Set Property Procedures mFrm() for the m_frm Form Object controls the Form object retrieval and assignment actions.

In the Set Property Procedure, the Class_Init() (this is not the Class_Initialize() sub-routine) subroutine is called to create separate Class Object Arrays for each TextBox Object and Command Button.

One Control (ctl) Object and two counter variables tcount and bcount, for TextBox and Command Button respectively.  They are used as Class Objects Array indexes during the re-dimensioning of ClsText and ClsCmdButton Class Object Arrays.

The Constant Evented Variable is assigned with the string "[Event Procedure]".

Next, both counter variables are initialized to zero.

The For Each . . . Next loop takes each control on the Form, and checks its control type name, whether it is TextBox or Command Button control.  The TypeName(ctl) Function gives the type name of the control.

If the control is a text box, its counter variable tcount is incremented by one.  The TextBox Class Module Object T is re-dimensioned for 1 to tcount elements, preserving the earlier array element's data, with the statement: ReDim Preserve T(1 to tcount). 

Note: In the re-dimension statement, it never says ReDim Preserve T(1 to tcount) As ClsText, as we normally do for normal variables,  like Re-Dim Preserve Qty(1 to cnt) as Single 

Next, the reference of the Form Object is copied into the Text Box Class  Module Property in the statement Set T(tcount).p_Frm = m_Frm to the tcount^th  Array element object instance.

Next, Set T(tcount).p_txt = ctl passes the Text Box reference to the same object element.

In the next step, we check for the TextBox name to determine which Event to be enabled.  Each Text Box may need different Events to run depending on the requirement of that particular data Field.  Here, we apply a blanket rule of enabling the AfterUpdate and OnLostFocus Events for all TextBoxes for demo purposes.

Hence, the following statements are added for all TextBox controls:

T(tCount).p_txt.AfterUpdate = Evented
T(tCount).p_txt.OnLostFocus = Evented

When the Command Button is encountered, its counter variable bcount is incremented by one and its Class Object B instance is re-dimensioned for 1 to bcount elements, preserving the earlier elements containing data, if any.

The Command Button Class Module doesn't have the Form Property.

The current Command Button control reference is passed to the B Object bcount^th element through the statement Set B(bCount).p_Btn = ctl.

Next, we check for the Command Button Name and enable the Click Event for each Command Button. 

Note: If all the command Buttons need only the Click Event then we don't need to check for their individual names but the statement B(bCount).p_Btn.OnClick = "[Event Procedure]" immediately after the statement Set B(bCount).p_Btn = ctl is enough.  But, the detailed Code for each Command Button is presented for clarity.

This process is repeated for all TextBoxes and Command Buttons on the Form. Other controls like Labels or any other control on the Form 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

The Form_Load() Event Procedure passes the Form Object to the D.mfrm() Property Procedure of the Derived Class Object ClsTxtBtn_Derived.  The Form Object reference is copied to each  ClsText Class Object Array Element, from the derived class object.

Downloads

Download the Demo Database from the link(s) below and try out and study the Code.  You may run the Code in Debug Mode by pressing the F8 key to step through each executable line of code.

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