Access Form Control Arrays and Event-3

Introduction.

This article builds on last week’s topic, focusing on how to capture TextBox AfterUpdate and LostFocus events and validate their Values through a class module array.

In the previous session, we stopped short of discussing how to move all the VBA code from the Form_Load() event procedure into a separate class module, leaving the form module almost free of event procedures. The VBA code in this new structure will define the TextBox control class module array and handle the required built-in events within their respective array elements. This approach will leave only three or four lines of code in the form module, while shifting all the logic into a derived class module object.

Earlier, we had created derived class objects by using a class module as a base class and extending its functionality. We will apply the same concept here as well.

We are using TextBox controls first—rather than other controls on the form—for these array-based examples because they are the most commonly used controls. A TextBox supports several events, including BeforeUpdate, AfterUpdate, LostFocus, Enter, Exit, KeyDown, KeyUp, and OnKey. Depending on the requirements, we can choose to invoke one or more of these events from within the derived class object.

We can define a set of standard event procedures in the TextBox class module to handle commonly used events such as BeforeUpdate, AfterUpdate, Enter, or Exit. However, only the required event handlers need to be activated for each TextBox control. This can be done during the array element initialization by assigning:

obj.txt.EventName = "[Event Procedure]"

This approach enables the selective activation of event procedures for individual TextBox instances.

Since each Form may require different validation rules or processing logic, the code inside these class event procedures often needs customization. An effective way to manage this is to create a TextBox Class Module Template, incorporating the most frequently used event procedures. For a new form, simply copy this template and modify it to suit the specific requirements of the TextBox controls on that form.

Other control types on a form—such as Command Buttons, Combo Boxes, and List Boxes—generally rely on fewer events, most commonly Click or DblClick. We will address managing these other control types in arrays later.

Eventually, we will also explore whether there are more effective approaches than arrays for managing multiple instances of different types of controls on the same form.

Moving Form's Class Module Code to Derived Class Module

Returning to today’s topic—moving the Form Module code into a separate Class Module—we will create a new Derived Class Module Object based on the existing ClsTxtArray1_2 Class Module as the Base Class. The code currently in the Form_Load() event procedure of the Form Module will be relocated into this new Derived Class.

If you haven’t already downloaded last week’s demo database, please do so using the link provided before proceeding. We will make copies of the relevant Modules and Forms to modify the code, ensuring that both the original and the updated versions of the code and forms are available within the same database. After making these changes, you can immediately run the forms to observe how the new implementation works.

Download TxtArray2_2007.zip

Download TxtArray2_2003.zip

After downloading the database, open it in Microsoft Access. You can then open the Form Module and review its code.

Next, copy the ClsTxtArray1_2 Class Module into a new Class Module named ClsTxtArray1_3, without making any changes to its code. Similarly, make a copy of the existing form and rename it TxtArray1_3Header. Any modifications will be done on these new copies, ensuring that the original Form and Class Module remain intact and unaltered.

We will be using last week’s sample form (shown in the image below) along with its Form Module VBA code, which is also reproduced below for your reference.

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

Option Compare Database
Option Explicit

Private Ta() As New ClsTxtArray1_2

Private Sub Form_Load()
Dim cnt As Integer
Dim ctl As Control

For Each ctl In Me.Controls
  If TypeName(ctl) = "TextBox" Then
     cnt = cnt + 1
     ReDim Preserve Ta(1 To cnt)
     Set Ta(cnt).Txt = ctl
     
     If ctl.Name = "Text8" Then
       Ta(cnt).Txt.OnLostFocus = "[Event Procedure]"
     Else
       Ta(cnt).Txt.AfterUpdate = "[Event Procedure]"
     End If
     
  End If
Next
End Sub

Make a Copy of the above Form and name it as frmTxtArray1_3Header.

Create a new Class Module with the name ClsTxtArray1_3.  Copy the VBA Code from the ClsTxtArray1_2 Class Module and paste it into the new Module.

Last week’s Class Module ClsTxtArray1_2  Code is reproduced below for reference.

Option Compare Database
Option Explicit

Private WithEvents Txt As Access.TextBox

Public Property Get mTxt() As Access.TextBox
  Set mTxt = Txt
End Property

Public Property Set mTxt(ByRef txtNewValue As Access.TextBox)
  Set Txt = txtNewValue
End Property

Private Sub Txt_AfterUpdate()
Dim txtName As String, varVal As Variant
Dim msg As String

txtName = Txt.Name
msg = ""
Select Case txtName
    Case "Text0"
        'Valid value range 1 to 5 only
        varVal = Nz(Txt.Value, 0)
        If varVal < 1 Or varVal > 5 Then
           msg = "Valid Value Range 1-5 only: " & varVal
        End If
    Case "Text8"
        'validates in LostFocus Event
    Case "Text10"
        'valid value 10 characters or less
        'Removes extra characters, if entered
        varVal = Nz(Txt.Value, "")
        If Len(varVal) > 10 Then
           msg = "Max 10 Characters Only. " & varVal
           Txt.Value = Left(varVal, 10)
        End If
    Case "Text12"
        'Date must be <= today
        'Future date will be replaced with Today's date
        varVal = DateValue(Txt.Value)
        If varVal > Date Then
          msg = "Future Date Invalid. " & varVal & vbCr & "Corrected to Today's Date."
          Txt.Value = Date
        End If
    Case "Text14"
        'A 10 digit number only valid
        varVal = Trim(Str(Nz(Txt.Value, 0)))
        If Len(varVal) <> 10 Then
          msg = "Invalid Mobile Number: " & varVal
        End If
End Select

If Len(msg) > 0 Then
    MsgBox msg, vbInformation, Txt.Name
End If

End Sub

Private Sub Txt_LostFocus()
Dim tbx As Variant, msg As String

tbx = Nz(Txt.Value, "")

msg = ""
If Len(tbx) = 0 Then
  msg = Txt.Name & " cannot be left Empty."
  Txt.Value = "XXXXXXXXXX"
End If

If Len(msg) > 0 Then
   MsgBox msg, vbInformation, Txt.Name
End If

End Sub

The Derived Class: ClsTxtArray1_3Header

The ClsTxtArray1_3 Class Module will be used as the Base Class for our new Derived Class Module. We will name it ClsTxtArray1_3Header, with extended functionality.

Create a new Class Module with the name ClsTxtArray1_3Header. The Derived Class Module, with its Properties and Property Procedures, is given below:

Option Compare Database
Option Explicit

Private Ta() As New ClsTxtArray1_3
Private frm As Access.Form

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

Public Property Set mFrm(vFrm As Access.Form)
  Set frm = vFrm
  Call Class_Init
End Property

Private Sub Class_Init()
 'Form Module Code goes here
End Sub

Copy and paste the above code into the new Header Class Module you have created.

Check the first two Property declarations.  First Property ClsTxtArray1_3 Class Object is instantiated as an undefined Array: Ta() – Ta stands for TextBox-Array.

The next property, frm, is introduced to give this Class Module access to the Form from which we plan to transfer the existing VBA code. All actions that were previously handled in the Form Module will now be managed here.

We will create Get and Set Property procedures to handle references to the Form. It will be a Set property (not a Let property) because we are passing a Form object, not a simple value, to it.

Immediately after the Form’s reference is received in the Set Property Procedure, we call the Class_Init() (this is not the same as Class_Initialize(), which runs automatically when a Class Object is instantiated) sub-routine to run the same code moved here from the Form’s Module.

Now, we will transfer the following Code from the Form_Load() Event Procedure into the Class_Init() sub-routine and make changes in the Form Module.

Copy and paste the following lines of code from the Form Module into the Class_init() sub-routine, replacing the Comment line:

Dim cnt As Integer
Dim ctl As Control

For Each ctl In frm.Controls
  If TypeName(ctl) = "TextBox" Then
     cnt = cnt + 1
     ReDim Preserve Ta(1 To cnt)
     Set Ta(cnt).Txt = ctl
     
     Select Case ctl.Name
        Case "Text8"
            'Only LostFocus Event
            Ta(cnt).Txt.OnLostFocus = "[Event Procedure]"
     Case Else
            'All other text Boxes wiil trigger AfterUpdate Event
            'i.e. entering/editing value in textbox
            Ta(cnt).Txt.AfterUpdate = "[Event Procedure]"
     End Select
     
  End If
Next

Form's Class Module Code

Open the Form frmTxtArray1_3Header in the design view. Display the Code Module. Copy and paste the following Code into the Form's Module, overwriting the existing Code:

Option Compare Database
Option Explicit

Private T As New ClsTxtArray1_3Header

Private Sub Form_Load()
  Set T.mFrm = Me
End Sub

We have instantiated the Derived Class ClsTxtArray1_3Header in Object Variable T. With the statement Set T.mFrm = Me, the active form's reference is passed to the T.mFrm() Set Property Procedure.

Immediately after this action, on the form_Load() Event procedure, the Class_Init() sub-routine runs in the ClsTxtArray1_3Header Class, and the txtArray1_3 Class Object array elements are created by invoking Events for each Text Box on the Form.  Hope you are clear with the Code above.

If you are ready to modify the Form Module, compile the database to ensure that everything is in order.

Save and close the Form, open it in Normal View, and try out each TextBox, and ensure that its Event sub-routines are performing as expected.

Replacing Class Object Array with Collection Object Items

The TextBox Class Object Array method works well for handling multiple TextBoxes. However, creating an array requires a counter variable, resizing the array for each new element while preserving the existing elements, and incrementing the counter for the next TextBox on the form, and so on.

When a form contains multiple controls of other types—such as Command Buttons, ComboBoxes, or ListBoxes—we would need to create separate arrays for each control type, each with its own counter and resizing logic in the class module. We will explore this approach in a future example.

A more efficient way to handle such complex scenarios is to use a Collection object instead of arrays. We will demonstrate this approach here, with TextBoxes, so you can get a practical feel for managing multiple controls using collections.

1. Create a new Derived Class Module with the name ClsTxtArray1_3Coll.
2. Copy and Paste the following Code into the Class Module:

Option Compare Database
Option Explicit

Private C As New Collection
Private Ta As ClsTxtArray1_3
Private frm As Access.Form

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

Public Property Set mFrm(vFrm As Access.Form)
  Set frm = vFrm
  Call Class_Init
End Property

Private Sub Class_Init()
'-----------------------------
'Usage of Collection Object, replacing Arrays
'-----------------------------
Dim ctl As Control

For Each ctl In frm.Controls
  If TypeName(ctl) = "TextBox" Then
     
     Set Ta = New ClsTxtArray1_3  'instantiate TextBox Class
     Set Ta.Txt = ctl 'pass control to Public Class Property
     
     Select Case ctl.Name
        Case "Text8"
            'Only LostFocus Event
            Ta.Txt.OnLostFocus = "[Event Procedure]"
     Case Else
            'All other text Boxes wiil trigger AfterUpdate Event
            'i.e. entering/editing value in textbox
            Ta.Txt.AfterUpdate = "[Event Procedure]"
     End Select
     C.Add Ta 'add to Collection Object
  End If
Next

End Sub

A Collection Object Property is declared and instantiated at the beginning. 

The TextBox Class Module is defined, not instantiated, in the Object Variable Ta.

The TextBox Class Ta Object is instantiated within the Control Type Test condition.  A new Ta Object instance is created for each TextBox on the Form.

After enabling the Events, the Ta Class Object is added to the Collection Object as its Item.

This method is repeated by adding a new instance of the TextBox class Object for each TextBox on the Form, with its required Events enabled, as a new Item to the Collection Object.  The Code is cleaner than the Array method.

Make a copy of the Form frmTxtArray1_3Header with the name frmTxtArray1_3Coll. 

1. Open it in Design View and display the Form's Code Module.
2. Copy and paste the Following Code into the Form Module, replacing the existing Code.

Option Compare Database
Option Explicit

Private Ta As New ClsTxtArray1_3Coll

Private Sub Form_Load()
  Set Ta.mFrm = Me
End Sub

The only change here is the name of the derived object, which has been updated to ClstxtArray1_3Coll. After making this change, recompile the database.

Save the Form, Open it in Normal View. Test the TextBoxes as before.

It should work as before.

Downloads

You can download the database, which includes all the modules and forms with the suggested changes applied.

Download TxtArrayHeader_2007.zip

Download TxtArrayHeader_2003.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