Introduction.
Last week we learned how to create the Class Object Array (the Class Object that defines only a single Access.TextBox Control as its Property) of TextBox Controls from the MS-Access Form. The built-in AfterUpdate or LostFocus Events Raised from TextBoxes on the Form are captured in their respective Class Object Array element and executes the AfterUpdate() or LostFocus() sub-routine from there.
The AfterUpdate event of the first TextBox is captured in the first element of the Class Object Array and the AfterUpdate() subroutine is executed there, instead of running the code on the Form's Module, as we do normally. Similarly, other TextBoxes Events are also handled in their respective Class Object Array element.
If you are a first-timer on this topic you may visit the earlier pages, from the following links, for continuity and to know the transition of code from one stage to the next.
The AfterUpdate and LostFocus Event handler Codes we have written earlier in the class module were of a common nature, and suit all text boxes on the Form. They tested sub-routines to monitor whether the events triggered from all text boxes on the form are getting captured in their respective class module array element or not.
Data Validation Checks
Now, it is time to define some basic data entry rules, for each text box on the form, and ensure that the User is made aware of the rule when it is violated.
I have added some labels above all TextBoxes on the form showing how the text box value is validated in the class module array on AfterUpdate and LostFocus Events.
The image of the form, with text boxes and their validation rules on labels, is given below.
Note: Since this is only for demonstration purposes the validation rules are not strictly enforced. The second text box needs only some value text or number etc. The mobile number field checks the length of the text box contents. The Mobile Number Text Box’s Input Mask is set to accept only digits.
- The first text box accepts the value range from 1 to 5 only. Any value outside this range triggers an Error Message.
- The second text box is validated OnLostFocus Event for the presence of some value in it, if it is left empty an error message will pop up and a sample string is inserted.
- The third text box accepts text or numbers up to 10 characters long, not more. It removes the extra characters at the end and updates the field. If it is left blank, no error is displayed.
- The next text box is a date field and the date greater than today is invalid.
- The last text box accepts only a 10-digit number.
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 above Txt_AfterUpdate() Event Procedure, this Event from all text boxes on the Form comes into the same subroutine in the class module. The txt.Name property will have the text box name, from where the event is coming from, and the txt.Value property will give us the value entered into the text box. With these two values, we can write code for validating each text box content.
The text box validation sample vba code of 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 not valid an appropriate message is displayed.
On the Form_Load() Event Procedure we have added OnLostFocus() Event only for TextBox8 on the form. When the insertion point leaves out of 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 the statement Txt.Value = "XXXXXXXXXX" that writes back the string in the same text box, from where the event is captured. What do we do if we have to refer to some other control on the Form to read/write something there? For that, we have to introduce the Form Object Property in the Class Module.
We will do that along with the Code change, as we plan 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 doesn't have any change from last week’s example, except that the Class Module Name is changed to ClstxtArray1_2.
I preserve the Class Module Code used in earlier Articles in a separate Version of the Class Module, that is why the name changed here.
I have made a small change in the Form module Code for TextBox8 Control to Raise only the LostFocus Event. Earlier Code invokes both 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 moved all the event handling code, normally written in the Form’s Class Module, to the Class Module and all the actions there are totally hidden from the User. But, still looking at the above Form Module there is plenty of action taking place on the Form_Load() Event Procedure.
We will pull some tricks to move the entire action from the Form Module to a Class Module, leaving three or four lines of code in the form. These and more in the coming weeks.
In the meantime, you may download the Demo Database from the Links given below, try it out and study the Code.
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