Last week we have learned how to capture Events, built-in and User-Defined, Raised from several type of Form-Controls and to run Event driven Programs in Class Module. 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:
- 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 Text Boxes, there will be several of them on the Form. It is not possible to declare separate Text Box Property 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 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 it's corresponding Class Object Array-Element and runs the sub-routine Code from there.
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 SubDeclared 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.
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 SubIn 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 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.
Open the Form in normal view.
Enter some numeric Value into the Text Box and press Tab Key.
A message box pops up with useful information in it. 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 AfterUpdate sub-routine and the value entered into the TextBox is displayed at the end of the line..
- Close the Form and Open it in Design View.
- Add four or five more Text Boxes on the Form, anywhere you like on the Form.
- Save and Close the Form.
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 message like the earlier one. It will contain the TextBox Name and the number entered into the Text Box.
- Try out other Text Boxes also in this way.
You may add more Text Boxes and try out whether newly added Text Boxes also respond to the AfterUpdate Event.
The After Update Event fires only when you enter some value or edit existing value and leave out of the Text Box.
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 Class Module and display a message, if it is empty. To do that we must make changes in 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
Each TextBox on a Form may accept different information and their validity Criterion may be different. The validity of data, of each Text Box, must be checked individually to ascertain it’s 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 Text Boxes, from the sub-routine. That may not be sufficient for real applications. We need to write 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.
- 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
