Access Form Control Arrays and Event Capturing ~ LEARN MS-ACCESS TIPS AND TRICKS

Introduction

Last week, we explored how to capture both built-in and user-defined events raised from various controls on the Form, and how to implement event-driven programming within class modules. We used one instance each of the following control types on the form: Text Box, Command Button, Combo Box, List Box, and Tab Control.

You can revisit the earlier pages for a step-by-step walkthrough by clicking the following links:

WithEvents MS-Access Class Module Tutorial
WithEvents and Defining Your Own Events
WithEvents Button Combo List TextBox Tab

Forms will have several objects of the same type. For example, there will be several TextBoxes on the Form. It is not possible to declare separate TextBox Properties in a Class Module to capture Events from each one of them.

The easiest technique is something like the following steps:

Create a Class Module with a single Text Box object property.
Define separate subroutines in the Class Module—such as AfterUpdate, LostFocus, and others—to handle the respective events triggered by Text Box controls on the Form.
In the Form’s module (or in another dedicated Class Module), create an array of these Class Module objects, assigning one class object instance to each Text Box control on the Form.
When a built-in event is raised from a particular Text Box on the Form, it will be captured by the corresponding class object in the array and executed through its event-handling subroutine.

Sample Demo Project

Let us start with a simple example.

Create a new Class Module and change its Name Property Value to clsTxtArray1.
Copy and paste the following VBA Code into the Class Module and save it:
```
Option Compare Database
Option Explicit

Public WithEvents Txt As Access.TextBox

Private Sub Txt_AfterUpdate()
Dim txtName As String, sngval As Single
Dim msg As String

txtName = Txt.Name
sngval = Nz(Txt.Value, 0)

msg = txtName & " _AfterUpdate. :" & sngval
MsgBox msg, vbInformation, Txt.Name

End Sub
```
Declared the Text Box Object Txt with Public scope to avoid the Get and Set Property Procedure for the time being, to keep the Code in the Class Module simple.
The AfterUpdate() Event Procedure will execute when that Event of the Text Box fires on the Form.
Create a new Form, insert a single Text Box on the Form, and save the Form with the name frmTxtArray1.

An image of the sample form is given below.

Right-click to open Large Image in New Tab/Window.
Open the Form in Design View and display the form's Code Module.
Copy and Paste the following VBA Code into the Form Module:
```
Option Compare Database
Option Explicit

Private ta() As New ClsTxtArray1

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
     ta(cnt).Txt.AfterUpdate = "[Event Procedure]"
  End If
Next
End Sub
```
The VBA Code Line by Line
In the global declaration area, ClsTxtArray1 Class is instantiated as an empty Array Object.
The real action is in the Form_Load() Event Procedure.
Two Variables, Count (cnt) and Control (ctl), are declared.
The For Each ... Next Loop is set to scan the Form for all Control Types on the Form, and identify the required control type: TextBox.
If the Control TypeName is TextBox, then the cnt Variable is incremented by one. The ta Array is re-dimensioned for 1 to cnt of items, preserving the earlier elements of data, if any.

The statement 'Set ta(cnt).Txt = ctl' assigns the current Class Object Array element’s txt Property with the TextBox Control.
The next line 'ta(cnt).Txt.AfterUpdate = "[Event Procedure]"' enables the TextBox’s AfterUpdate Event, so that it can be captured in the Private Sub txt_AfterUpdate() sub-routine of ta(cnt) instance of ClsTxtArray1 Array element.
If you are through with the above lines of Code, then save and close the Form.
Sample Data Entry
Open the Form in normal view.
Enter some numeric Value into the Text Box and press the Tab Key.
A message box displays the entered value. A sample test run image is given below.
Right-click to open Large Image in New Tab/Window.
Check the MsgBox image with event-related info in there.
The TexBox Name is shown in the Title area, from where the AfterUpdate Event is captured. The message line indicates that it is run from the AfterUpdate subroutine, and the value entered into the TextBox is displayed at the end of the line.

Adding More Controls on Form
Close the Form and Open it in Design View.
Add a few more TextBoxes, anywhere you like on the Form.
Save and Close the Form.
A sample Image of the changed Form is given below.
Right-click to open Large Image in New Tab/Window.
Open the Form in Normal View.
Enter some numeric value in any newly added text box and press the Tab Key.
When you do this, the MsgBox will pop up, displaying messages like the earlier one. It will contain the TextBox Name and the number entered into the Text Box.
Try out other TextBoxes in this way. Add more TextBoxes, if you like, and try out whether the newly added TextBoxes also respond to the AfterUpdate event.

The After Update Event fires only when you enter a value or edit an existing value and leave the Text Box.

Adding the LostFocus Event

But what if a particular Text Box must not be left blank when the cursor moves away from it (on the LostFocus event) without entering any value?

In such a case, when the insertion point leaves that Text Box, the Form should trigger its LostFocus event. This event must then be captured in the Class Module, and a message should be displayed if the Text Box is empty.

To implement this behavior, we need to make corresponding changes in both the Form Module and the Class Module.

In the Form_Load() Event Procedure, checks whether TextBox8 (on my Form, the second TextBox at the left) is the current control, then adds ta(cnt).Txt.OnLostFocus = "[Event Procedure]" to trigger the LostFocus() Event on TextBox8.

Add the following lines to the Form_Load() Event Procedure, replacing Text8 with the Text Box name from your Form.

If ctl.Name = "Text8" Then
   ta(cnt).Txt.OnLostFocus = "[Event Procedure]"
End If

The current control's name is Text8 then ta(cnt).Txt.OnLostFocus Event is also set to invoke this Event. So Text8 will fire both AfterUpdate and LostFocus Events.

The changed Form_Load() Event Procedure Code is given below:

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
     ta(cnt).Txt.AfterUpdate = "[Event Procedure]"
     
     If ctl.Name = "Text8" Then
       ta(cnt).Txt.OnLostFocus = "[Event Procedure]"
     End If
     
  End If
Next
End Sub

A Subroutine for LostFocus Event is required in the ClstxtArray1 Class Module to capture the Event from the Text8 Text Box.

The Subroutine code to handle the LostFocus Event is given below:

Private Sub Txt_LostFocus()
Dim tbx As Variant
tbx = Nz(Txt.Value, "")

If Len(tbx) = 0 Then
  MsgBox Txt.Name & " is Empty.", vbInformation, Txt.Name
End If
End Sub

If some value is entered into the Text8 TextBox, then the MsgBox will not appear for the LostFocus Event. If the TextBox8 is empty, then the LostFocus Event will fire. The full Class Module Code is given below:

Option Compare Database
Option Explicit

Public WithEvents Txt As Access.TextBox

Private Sub Txt_AfterUpdate()
Dim txtName As String, sngval As Single
Dim msg As String

txtName = Txt.Name
sngval = Nz(Txt.Value, 0)

msg = txtName & " _AfterUpdate. :" & sngval
MsgBox msg, vbInformation, Txt.Name

End Sub

Private Sub Txt_LostFocus()
Dim tbx As Variant
tbx = Nz(Txt.Value, "")

If Len(tbx) = 0 Then
  MsgBox Txt.Name & " is Empty.", vbInformation, Txt.Name
End If
End Sub

Moving to the Next Stage

Each TextBox on a Form may accept different types of information, each with its own validation criteria. The data entered in every TextBox must be individually validated to ensure it meets the specified requirements, and appropriate action should be taken if any entry fails to comply.

The above code displays a generalized message for all TextBoxes from the subroutine. That may not be sufficient for real applications. We need to write a specific Code for each TextBox when the above Event fires from each TextBox.

We will continue this discussion next week for more details on this topic.