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

Sunday, May 5, 2019

Access Form Control Arrays and Event Capturing

Introduction

Last week we learned how to capture Events, both built-in and User-Defined types, Raised from several Form based Controls, and to run Event-driven Programs in Class modules. We have used Text Box, Command Button, Combo Box, List Box, and Tab Control, one control of each type, on the Form. You may visit the earlier pages by clicking on the following Links:

Forms will have several objects of the same type. For example Text Boxes, there will be several of them on the Form. It is not possible to declare separate TextBox Properties in Class Module, for each Text Box on the Form, 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.
Create separate Sub-Routines for AfterUpdate, LostFocus, etc. in the Class Module to handle these Events from Text Box(s) on the Form.
Create an Array of this Class Module Object in the Form Module, or in a separate Class Module, with one Class Object element for each Text Box Control on the Form.
The built-in Event Raised from a particular Text Box on the Form captures that Event by its corresponding Class Object Array-Element and runs the sub-routine Code from there.

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 Property Procedure Get/Set for the time being to keep the Code in 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.
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 then save and close the Form.
Sample Data Entry
Open the Form in a normal view.
Enter some numeric Value into the Text Box and press Tab Key.
A message box pops up with useful information in it. A sample test run image is given below.
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 sub-routine 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 four or five more TextBoxes on the Form, anywhere you like on the Form.
Save and Close the Form.
A sample Image of the changed Form is given below.
Open the Form in Normal View.
Enter some numeric value in any newly added text box and press 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 also in this way.
You may add more TextBoxes and try out whether newly added TextBoxes also respond to the AfterUpdate event.

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

Adding the LostFocus Event

But, what if a particular Text Box cannot be left blank when the insertion point leaves out of the Text Box (LostFocus Event) without keying in some value. When the insertion point leaves out of that Text Box, the Form should fire the LostFocus Event. The Event must be captured in the Class Module and display a message if it is empty. To do that we must make changes in the Form Module and in 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 into 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 Sub-routine for LostFocus Event is required in the ClstxtArray1 Class Module, to capture the Event from the Text8 Text Box.

The Sub-routine 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 information and their validity Criterion may be different. The validity of the data, of each Text Box, must be checked individually to ascertain its acceptability, and need to take action upon it if it doesn’t conform to the requirements.

The above code displays only a generalized message, for all TextBoxes, from the sub-routine. 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 Text Box.

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

LEARN MS-ACCESS TIPS AND TRICKS

Sunday, May 5, 2019