Introduction.
Last week, we learned how to create a Class Object Array, where each Class Object defines a single Access. The TextBox control has its properties to manage multiple TextBox controls on an MS Access Form. The built-in AfterUpdate and LostFocus events raised from the TextBoxes on the Form are captured by their corresponding elements in the Class Object Array. Each element then executes its own AfterUpdate() or LostFocus() subroutine, instead of running the code within the Form’s Class Module as we normally would.
For example, the AfterUpdate event of the first TextBox is captured by the first element of the Class Object Array, and its afterUpdate() subroutine is executed there. In the same way, events from other TextBoxes are also handled by their respective Class Object Array elements.
If you are new to this topic, please refer to the earlier pages using the links below to understand the step-by-step transition of code from one stage to the next.
The AfterUpdate and LostFocus event handler code we wrote earlier in the Class Module was generic in nature and applied to all TextBox controls on the Form. These were test subroutines created solely to verify whether the events triggered from each TextBox on the Form were being correctly captured by their respective Class Module array elements.
Data Validation Checks
Now, it’s time to define specific data entry rules for each TextBox on the form and ensure that the user is notified whenever a rule is violated.
To make sure these rules are clear, I’ve placed descriptive labels above each Text Box on the form. These labels indicate how the values entered in the TextBoxes will be validated within the Class Module array through their AfterUpdate and LostFocus events.
An image of the form, showing the TextBoxes along with their corresponding validation rule labels, is provided below.
The first TextBox accepts only values between 1 and 5. Any value outside this range triggers an error message.
-
The second TextBox is validated in the LostFocus event to ensure it is not left blank. If it is empty, an error message is displayed, and a sample string is inserted automatically.
-
The third TextBox accepts text or numbers up to 10 characters long. Any extra characters beyond this limit are removed, and the field is updated accordingly. If left blank, no error is shown.
-
The fourth TextBox is a date field. Any date later than today is considered invalid.
-
The last TextBox accepts only a 10-digit number.
Note: This setup is intended purely for demonstration purposes, so the validation rules are not strictly enforced. The second TextBox accepts any type of input—whether text, numbers, or other characters. The Mobile Number field, however, checks only the length of the entered value. Additionally, the Mobile Number TextBox has an Input Mask applied to restrict input to digits only.
The Class Module Changes.
We will write the VBA code for the above simple validation checks in the class module.
The earlier version of the VBA code in the class module ClstxtArray1 is given below for reference.
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
The Event-based Sub-Routine
Check the Txt_AfterUpdate() event procedure. This single event handler in the class module receives the AfterUpdate event from all the text boxes on the form. The txt.Name property identifies which text box triggered the event, while the txt.Value property provides the value entered in that text box. Using these two properties, you can write specific validation logic for the contents of each text box.
The text box validation sample VBA code of the txt_AfterUpdate() Event Sub-routine is given below.
Private Sub Txt_AfterUpdate()
Dim txtName As String, varVal As Variant
Dim msg As String
txtName = Txt.Name
msg = ""
Select Case txtName
Case "Text0"
varVal = Nz(Txt.Value, 0)
If varVal < 1 Or varVal > 5 Then
msg = "Valid Value Range 1-5 only: " & varVal
End If
Case "Text8"
' validation in OnLostFocus Event
Case "Text10"
varVal = Nz(Txt.Value, "")
If Len(varVal) > 10 Then
msg = "Max 10 Characters Only. " & varVal
Txt.Value = Left(varVal, 10)
End If
Case "Text12"
varVal = DateValue(Txt.Value)
If varVal > Date Then
msg = "Future Date Invalid. " & varVal
End If
Case "Text14"
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
The text box name (Txt.Name) received from the AfterUpdate Event is checked in the Select Case. . . End Select structure. Depending on the text box name and the text box value (Txt.Value), the validation check is performed; if Invalid, an appropriate message is displayed.
On the Form_Load() Event Procedure, we have added the OnLostFocus() Event only for TextBox8 on the form. When the insertion point leaves this text box, the LostFocus Event fires and captures it in the Private Sub txt_LostFocus() subroutine of the class module. If the TextBox is empty, the sample text string “XXXXXXXXXX” is inserted into TextBox8, followed by an error message.
The LostFocus sub-routine is given below:
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 leave it Empty." Txt.Value = "XXXXXXXXXX" End If If Len(msg) > 0 Then MsgBox msg, vbInformation, Txt.Name End If End Sub
Here, we are not testing for TextBox8, as we did in the AfterUpdate() event procedure, because we have not added the LostFocus Event for any other TextBox.
Did you notice that the statement Txt.value = "XXXXXXXXXX" writes the string back to the same TextBox from which the event was captured? But what if we need to access another control on the form to read or write data there?
To achieve this, we must introduce a Form object property in the class module. We will implement this along with the upcoming code changes, as part of our future plan is to move all actions from the form module to the class module.
The full VBA Code of the Class Module: ClsTxtArray1_2 is given below:
Option Compare Database
Option Explicit
Public WithEvents Txt As Access.TextBox
Private Sub Txt_AfterUpdate()
Dim txtName As String, varVal As Variant
Dim msg As String
txtName = Txt.Name
msg = ""
Select Case txtName
Case "Text0"
varVal = Nz(Txt.Value, 0)
If varVal < 1 Or varVal > 5 Then
msg = "Valid Value Range 1-5 only: " & varVal
End If
Case "Text8"
' validation in OnLostFocus Event
Case "Text10"
varVal = Nz(Txt.Value, "")
If Len(varVal) > 10 Then
msg = "Max 10 Characters Only. " & varVal
Txt.Value = Left(varVal, 10)
End If
Case "Text12"
varVal = DateValue(Txt.Value)
If varVal > Date Then
msg = "Future Date Invalid. " & varVal
End If
Case "Text14"
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 leave it Empty."
Txt.Value = "XXXXXXXXXX"
End If
If Len(msg) > 0 Then
MsgBox msg, vbInformation, Txt.Name
End If
End Sub
The Form Module VBA Code
The form module code remains unchanged from last week’s example, except that the class module name has been updated to ClstxtArray1_2.
I maintain the class module code from earlier articles as separate versioned copies, which is why the class module name has changed here.
Additionally, I made a minor change in the form module code for the TextBox8 control — it now raises only the LostFocus event. In the earlier version, the code triggered both the AfterUpdate and LostFocus events.
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
Downloads
We have now moved all the event-handling code—normally written in the form’s class module—into a separate class module, keeping all the underlying actions completely hidden from the user.
However, if you look at the current Form_Load() event procedure, you’ll notice that there’s still quite a bit of code left in the form module.
In the coming weeks, we’ll explore some techniques to shift almost all this remaining code into the class module, leaving only three or four lines in the form module.
In the meantime, you can download the demo database from the links below, try it out, and study the code to understand how it works.
Download TxtArray2_2007.zipLinks 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
This is the only tutorial available that goes in depth in Access classes; not even Access Developer's Handbook had such a broad perspective and it was published almost twenty years ago. Congrats A.P.R. :-)
ReplyDeleteThank you.
ReplyDelete