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