Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Access Form Control Arrays and Event-2

Introduction

Last week we have learned how to create the Class Object Array (the Class Object that defines only a single Access.TextBox Control as it's Property) of TextBox Controls from MS-Access Form. The built-in AfterUpdate or LostFocus Events Raised from Text Boxes 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() sub-routine is executed in there, instead of running the Code on the Form's Module, as we do normally. Similarly other Text Boxes 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 Code we have written earlier in the class module were of common nature and suits for all text boxes on the Form.  They were test sub-routines to monitor the events that 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 Text Boxes 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 need 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 value range 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 number 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. Next text box is a date field and date greater than today is invalid.
  5. The last text box accepts only a 10 digit Number. 

Class Module Changes

We will write VBA Code for the above simple validation checks in the class module.

The earlier Version of 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 in to the same sub-routine 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 contents.

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 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()  sub-routine of class module.  If the Text Box 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 into 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 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 Class Module, that is why the name change 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 on the Form’s Class Module, to the Class Module and all the actions in there is totally hidden from the User.  But, still looking at the above Form Module there is plenty of actions taking place on the Form_Load() Event Procedure.

We will pull some trick to move the entire action from the Form Module to a Class Module, leaving three or four lines of code on the form.  These and more in the coming weeks.

In the meantime you may download the Demo Database from the Links given below, try out and study the Code.

  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
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 are subject to moderation before publishing.

Translate



PageRank
Subscribe in a reader
Your email address:

Delivered by FeedBurner

http://clicky.com/101199826
Blog Directory Visit blogadda.com to discover Indian blogs

Search

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Follow by Email

Labels

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

Featured Post

WithEvents and All Form Control Types

Introduction So far we have worked extensively with only one type of Control: the TextBoxes on Form, enabled with Events to Raise and captu...

Labels

Blog Archive

Recent Posts