Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Access Form Control Arrays and Event-2

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.

  1. withevents Button Combo List TextBox Tab
  2. Access Form Control Arrays and Event Capturing

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.

  1. The first text box accepts the value range from 1 to 5 only.  Any value outside this range triggers an Error Message.
  2. 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.
  3. 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.
  4. The next text box is a date field and the date greater than today is invalid.
  5. 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.


Links to WithEvents ...Tutorials.

  1. WithEvents Ms-Access Class Module Tutorial
  2. WithEvents and Defining Your Own Events
  3. withevents Button Combo List TextBox Tab
  4. Access Form Control Arrays and Event Capturing
  5. Access Form Control Arrays and Event-2
  6. Access Form Control Arrays and Event-3
  7. WithEvents in Class Module for Sub-Form TextBox Events
  8. WithEvents in Class Module and Data Entry
  9. WithEvents and Access Report Event Sink
  10. WithEvents and Report Lines Hiding
  11. WithEvents and Report Lines Highlighting
  12. Withevents TextBox and Command Button Arrays
  13. Withevents TextBox CommandButton Dictionary
  14. Withevents and all Form Control Types

Share:

2 comments:

  1. 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. :-)

    ReplyDelete

Comments subject to moderation before publishing.

PRESENTATION: ACCESS USER GROUPS (EUROPE)

Translate

PageRank

Post Feed


Search

Popular Posts

Blog Archive

Powered by Blogger.

Labels

Forms Functions How Tos MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Class Module External Links Queries Array msaccess reports Accesstips WithEvents msaccess tips Downloads Objects Menus and Toolbars Collection Object MsaccessLinks Process Controls Art Work Property msaccess How Tos Combo Boxes Dictionary Object ListView Control Query VBA msaccessQuery Calculation Event Graph Charts ImageList Control List Boxes TreeView Control Command Buttons Controls Data Emails and Alerts Form Custom Functions Custom Wizards DOS Commands Data Type Key Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Command Button Report msaccess menus msaccessprocess security advanced Access Security Add Auto-Number Field Type Form Instances ImageList Item Macros Menus Nodes RaiseEvent Recordset Top Values Variables Wrapper Classes msaccess email progressmeter Access2007 Copy Excel Export Expression Fields Join Methods Microsoft Numbering System Records Security Split SubForm Table Tables Time Difference Utility WScript Workgroup database function msaccess wizards tutorial Access Emails and Alerts Access Fields Access How Tos Access Mail Merge Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Common Controls Conditional Formatting Data Filtering Database Records Defining Pages Desktop Shortcuts Diagram Disk Dynamic Lookup Error Handler External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Message Box Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar updating upload vba code