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 and incorporate 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 examine 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.zipAfter 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 use last week’s sample Form (shown in the image below) along with its Form Module VBA code, also reproduced below for your reference.
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.
- Create a new Derived Class Module with the name ClsTxtArray1_3Coll.
- 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.
- Open it in Design View and display the Form's Code Module.
- 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.
Links to WithEvents ...Tutorials.
- WithEvents MS-Access Class Module Tutorial
- WithEvents and Defining Your Own Events
- withevents Button Combo List TextBox Tab
- Access Form Control Arrays and Event Capturing
- Access Form Control Arrays and Event-2
- Access Form Control Arrays and Event-3
- WithEvents in Class Module for Sub-Form TextBox Events
- WithEvents in Class Module and Data Entry
- WithEvents and Access Report Event Sink
- WithEvents and Report Lines Hiding
- WithEvents and Report Lines Highlighting
- Withevents TextBox and Command Button Arrays
- Withevents TextBox CommandButton Dictionary
- Withevents and all Form Control Types
