Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

WithEvents in Class Module and Data Entry Form

1. Introduction.

So far we have used Unbound Text Boxes on Form for Demo runs, and for built-in Event capturing in Class Module Object.  Since, Text Boxes are the main control used on Forms, for data entry/display/view of information, it was the first preferred choice for demonstration of the most frequently used built-in Events: AfterUpdate, OnGotFocus, and OnLostFocus.

That doesn’t mean that we have ignored Command Buttons, Combo Boxes, List Boxes, and others.   All these controls mostly use the Click Event, to open Form/Report or run macros/subroutines/functions,  select an item from  Combo box or List Box or Option Button and they are much easier to handle in their Event Procedures in the Class Module.  We will take those controls also along with TextBoxes

Once you are familiar with handling the Text Box-based Events alone and understand how it works, the other controls will be a simple addition to the whole scheme of things.

Adding Class Module Object instance, one instance for each Text Box on the Form, into the Array element, enabled with required built-in Events itself is somewhat confusing, but not complicated.

If you have not touched on the topic of programming the stand-alone Class Module I suggest you better start from there.  It will help you a lot and much easier to understand its usage in WithEvents, Event, and RaiseEvent programming.  Go through the following Articles, to get a general idea of Class Module Programming.

  1. MS-Access Class Module and VBA
  2. MS-Access VBA Class Object Arrays
  3. MS-Access Base Class and Derived Objects
  4. VBA Base Class and Derived Object-2
  5. Base Class and Derived Object Variants
  6. MS-Access Recordset and Class Module
  7. Access Class Module and Wrapper Classes
  8. Wrapper Class Functionality Transformation

2. Brief Review of what is covered so far.

In the last few weeks, we have started with the topic of Class Module and Form Controls Event Programming with Microsoft Access and learned how to create a Class Module Object Array element for each Text Box on Form.

The preparations for creating a Class Module for Text Box object are something like the following steps.

  1. Creates a Class Module with the Text Box (Access.TextBox) Property with Object (or Variable) name like txt.
  2. If the Property is declared as Private then Get/Set Property Procedures are added to manage the txt Object.
  3. Event Handling sub-routines for AfterUpdate, GotFocus, and LostFocus are created in the Class Module.

When an instance of the above Class Module is created in memory it can handle only a single Text Box’s Event (AfterUpdate, GotFocus, and LostFocus) on the Form.

If there are more Text Boxes, say three TextBoxes, on the Form then we must create three instances of the same Class Module, one for each Text Box on the Form. 

To keep track of all the three instances of the Class Module they should be organized in an Array or add them as Collection Object Items, one after the other.

When the same Class Module instance is used for more than one Text Box the Event Procedures (sub-routines) need changes in the Class Module.  We must identify the Text Box that Raised the built-in Event on the Form.  Based on that information run the validation checks or any other action on that particular Text Box within the same sub-routine.

Example: The Code given below is taken from the AfterUpdate() Event Procedure. The FirstName and Designation TextBox’s AfterUpdate Event doesn't have any executable Code under them, in the Select Case . . . End Select structure, indicating that these TextBoxes exist on the Form but the AfterUpdate Event is not enabled or used for them.  If there is no executable code in them it is not necessary to add the Text Box name there.  But, adding those fields as part of the testing sequence reminds us of their presence on the Form.

But these TextBoxes may be enabled with any other Event like OnLostFocus and handled in the LostFocus Event Procedure.

txtName = Txts.Name

Select Case txtName
    Case "LastName" 'TextBox Name
        txtval = Trim(Nz(Txts.Value, ""))

        If Len(txtval) > 15 Then
           msg = "LastName Max 15 chars only."
           MsgBox msg, vbInformation, txtName & "_AfterUpdate()"
           Txts.Value = Left(Nz(txtval, ""), 15)
        End If
    Case "FirstName"
        '
    Case "Designation"
        '
    Case "BirthDate"
         db = CDate(Nz(Txts.Value, 0))
        
         If db > Date Then
           msg = "Future Date: " & db & " Invalid."
           MsgBox msg, vbInformation, txtName & "_AfterUpdate()"
           Txts.Value = Null
           efrm!Age = Null

         ElseIf db > 0 Then
           dbage = Int((Date - db) / 365)
           efrm!Age = dbage
         End If
    Case "Age"
         Dim xage As Integer
         db = CDate(Nz(efrm!BirthDate, 0))
         xage = Nz(Txts.Value, 0)
         
         If (db > 0) And (xage > 0) Then
            dbage = Int((Date - db) / 365)
            If xage <> dbage Then
                msg = "Correct Age as per DB = " & dbage
                MsgBox msg, vbInformation, txtName & "_AfterUpdate()"
                Txts.Value = dbage
            End If
         ElseIf (xage = 0) And (db > 0) Then
            dbage = Int((Date - db) / 365)
            Txts.Value = dbage
         End If
    Case "JoinDate"
       Dim jd As Date
       
       db = CDate(Nz(efrm!BirthDate, 0))
       jd = CDate(Nz(Txts.Value, 0))
       
       If (db > 0) And (jd < db) Then
          msg = "JoinDate < Birth Date Invalid!"
          MsgBox msg, vbInformation, txtName & "_AfterUpdate()"
          Txts.Value = Null
       End If
End Select

Note: If you need a Read/Write value from/to some other Text Box, other than the Event trapped Text Box, then you need Access.Form Property also in the Text Box Class Module: ClsTxtEmployees, to reference the other Text Box to read/write values.

This Class Module can be a Template for Other Form’s TextBoxes and subroutine customization is required depending on that particular Form's TextBox control's requirement.

The links to all the earlier Articles on this topic are given below if you are new to the WithEvents, Events, and Raise Events usage in User-Defined Events or built-in Event Programming with Microsoft Access, for easy access to those articles.

  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

3. What is New in this Post?

Here, we have a sample Employee Data Entry Form bound to a Table.  If you have gone through the earlier examples with TextBoxes on unbound Form you will not find any difference in this Demo.  The only idea here is to demonstrate the normal usage of Event handling in Class Module Object with the form bound to a table and the data fields as a control source to the TextBox.

The sample image of the Data Entry Form is given below.  An error message shown on the image is popped up (this is manually moved to the bottom right corner) when an invalid Join Date (less than birth date) is entered into the field.

The Command Button Click on the form is handled normally on the Form Module itself in the first Employee Form Demo.

In the Employee Form demo the Form’s Class Module and a Class Module with two Private Properties, Txts for Text Box Object, and efrm for Access.Form Objects are used.

4.  New Class Module Code.

Insert a new Class Module and change its Name Property value from Class1 to ClsTextEmployees.

Copy and Paste the following VBA Code into the Class Module and save the Code:

Option Compare Database
Option Explicit

Private efrm As Access.Form
Private WithEvents Txts As Access.TextBox

Public Property Get pfrm() As Access.Form
  Set pfrm = efrm
End Property

Public Property Set pfrm(ByRef vNewValue As Access.Form)
  Set efrm = vNewValue
End Property

Public Property Get pTxts() As Access.TextBox
  Set pTxts = Txts
End Property

Public Property Set pTxts(ByRef vNewValue As Access.TextBox)
  Set Txts = vNewValue
End Property

Private Sub Txts_AfterUpdate()
Dim txtName As String, txt As String
Dim msg As String, txtval As Variant
Dim db As Date, dbage As Integer

txtName = Txts.Name

Select Case txtName
    Case "LastName"
        txtval = Trim(Nz(Txts.Value, ""))

        If Len(txtval) > 15 Then
           msg = "LastName Max 15 chars only."
           MsgBox msg, vbInformation, txtName & "_AfterUpdate()"
           Txts.Value = Left(Nz(txtval, ""), 15)
        End If
    Case "FirstName"
        '
    Case "Designation"
        '
    Case "BirthDate"
         db = CDate(Nz(Txts.Value, 0))
        
         If db > Date Then
           msg = "Future Date: " & db & " Invalid."
           MsgBox msg, vbInformation, txtName & "_AfterUpdate()"
           Txts.Value = Null
           efrm!Age = Null

         ElseIf db > 0 Then
           dbage = Int((Date - db) / 365)
           efrm!Age = dbage
         End If
    Case "Age"
         Dim xage As Integer
         db = CDate(Nz(efrm!BirthDate, 0))
         xage = Nz(Txts.Value, 0)
         
         If (db > 0) And (xage > 0) Then
            dbage = Int((Date - db) / 365)
            If xage <> dbage Then
                msg = "Correct Age as per DB = " & dbage
                MsgBox msg, vbInformation, txtName & "_AfterUpdate()"
                Txts.Value = dbage
            End If
         ElseIf (xage = 0) And (db > 0) Then
            dbage = Int((Date - db) / 365)
            Txts.Value = dbage
         End If
    Case "JoinDate"
       Dim jd As Date
       
       db = CDate(Nz(efrm!BirthDate, 0))
       jd = CDate(Nz(Txts.Value, 0))
       
       If (db > 0) And (jd < db) Then
          msg = "JoinDate < Birth Date Invalid!"
          MsgBox msg, vbInformation, txtName & "_AfterUpdate()"
          Txts.Value = Null
       End If
End Select
End Sub

Private Sub Txts_LostFocus()
Dim txtName As String
Dim msg As String, txtval As Variant

txtName = Txts.Name
txtval = Trim(Nz(Txts.Value, ""))

Select Case txtName
    Case "LastName"
        '
    Case "FirstName"
        If Len(txtval) = 0 Then
           msg = "FirstName should not be Blank."
           MsgBox msg, vbInformation, txtName & "_LostFocus()"
           Txts.Value = "XXXXXXXXXX"
        End If
    Case "Designation"
        If Len(txtval) = 0 Then
           msg = "Designation Field is Empty."
           MsgBox msg, vbInformation, txtName & "_LostFocus()"
           Txts.Value = "XXXXXXXXXX"
        End If
    Case "BirthDate"
        '
    Case "Age"
        '
    Case "JoinDate"
        '
End Select

End Sub

5. Class Module Properties and Sub-Routines.

On the Global area of the module an Access.Form Property efrm declared as Private Property.

Next, an Access.TextBox control is declared, with WithEvents keyword and object name Txts as Private PropertyThe WithEvents keyword enables the Txts Object to capture the programmed Events when triggered on the Form. 

When Class Module Properties are declared as Private, it is accessible only within the Class Module, preventing direct access to the Objects from outside, there must be Get/Set Property Procedures with Public Scope in the Class Module to assign/retrieve values to/from it.

The first Get/Set Property Procedure pairs are for the efrm Access.Form Object.

The next set of Get/Set Property Procedure pairs retrieves/assign TextBox Control in Txts Object.

Any validation checks on the Set Property Procedure parameter can be performed before attempting to assign the value to the Property efrm or Txts.

In the Class Module, we are handling only two types of built-in Events, AfterUpdate and LostFocus Events from TextBoxes on the Form.

The AfterUpdate() Event Procedures validates the Values of LastName, BirthDate, Age, and JoinDate Fields and displays an appropriate message, mainly to monitor whether the programmed Event is getting captured in the Class Module instance or not.

The LostFocus() Event Procedure validates the FirstName and Designation field values and assigns a default text String “XXXXXXXXXX” to the field if it is left empty during the Lost Focus Event.

All the Text Box names are listed in the Select Case . . . End Select structure for clarity, some without any executable code in it.  In the LostFocus Event Procedure, we have written code under the FirstName and Designation fields.  The lost Focus event is enabled on the Form only for these two data fields.  Other fields are included for clarity or for future use in case they are needed.

The first Text Box on the Form is an Auto-number field, that increments its contents automatically and we have no intention to trap any events on this particular control.  Hence, it is not at all taken in the Select Case . . . End Select structure, or enabled any Event on that control either.

6.  Employees Form Module Code.

The VBA Code behind the Employees Form's Class Module is given below:

Option Compare Database
Option Explicit

Dim tc As ClstxtEmployee
Dim C As Collection

Private Sub cmdClose_Click()
'Command Button Click Event is handled
'on the Form Module itself, the Event is
'not programmed in Class Module: ClsTxtEmployee
DoCmd.Close
End Sub

Private Sub Form_Load()
Dim ctl As Control

Set C = New Collection

For Each ctl In Me.Controls
  If TypeName(ctl) = "TextBox" Then
  
     Set tc = New ClstxtEmployee
     
     'Form Object is required to read/write values
     'from other TextbOX, if needed.
    Set tc.pfrm = Me
    'assign TextBox control to the Class Module instance's Property
    Set tc.pTxts = ctl
    
    Select Case ctl.Name
          Case "FirstName", "Designation"
          'enable LostFocus Event for FirstName and Designation
               tc.pTxts.OnLostFocus = "[Event Procedure]"
          Case Else
           'enable AfterUpdate for all other Text Boxes
               tc.pTxts.AfterUpdate = "[Event Procedure]"
    End Select
  End If
  C.Add tc 'add ClstxtEmployee instance as Collection Object Item
Next

End Sub

Private Sub Form_Unload(Cancel As Integer)
'when the form is closed erase Collection Object from memory
Set C = Nothing
End Sub

The Class Module ClsTxtEmployee is declared as tc Object.

A Collection Object is declared in Object C.

On the Form_Load() Event Procedure the Collection Object is instantiated.

Within the For Each . . Next loop the Employees Form Text Box controls are picked and enabled for the required built-in Events.

For each text box on the Form, Employee, a new instance of the Class Module ClsTxtEmployee is created and the Form Object and Text Control Property Values are passed to the Class Module Object.

7.  Derived Class Module to Replace Form Module Code.

As we did in the earlier cases we will create a Derived Class Object (ClsTxtEmployeeHeader), using ClsTxtEmployee Class as Base Class and transfer the above Form Module Code into the new Class Module and leave the Form's Class Module with the bare minimum of a few lines of code.

We will also transfer the Command Button Click Event handling into the Derived Class Module Object.

The Derived Class Module (ClsTxtEmployeeHeader) VBA Code is given below:

Option Compare Database
Option Explicit

Private tc As ClstxtEmployee
Private Col As New Collection

Private fm As Access.Form
Private WithEvents btn As Access.CommandButton


Public Property Get oFrm() As Access.Form
  Set oFrm = fm
End Property

Public Property Set oFrm(ByRef vNewValue As Access.Form)
  Set fm = vNewValue
  Call Class_Init
End Property

Private Sub Class_Init()
Dim ctl As Control

For Each ctl In fm.Controls
  Select Case TypeName(ctl)
  'If TypeName(ctl) = "TextBox" Then
    Case "TextBox"
        'create a new instance of Class Module Object
        Set tc = New ClstxtEmployee
        'assign common property values
        Set tc.pfrm = fm 'pass Form Employyes object to the new instance
        Set tc.pTxts = ctl  'pass text control
            
            'enable required event procedures for Text Boxes
            Select Case ctl.Name
                   'lostfocus event controls
                Case "FirstName", "Designation"
                    tc.pTxts.OnLostFocus = "[Event Procedure]"
                Case Else
                    'after Update Event
                    tc.pTxts.AfterUpdate = "[Event Procedure]"
            End Select
            Col.Add tc 'add to the collection object
    Case "CommandButton"
        Set btn = ctl
        btn.OnClick = "[Event Procedure]"
    End Select
Next

End Sub

Private Sub btn_Click()
   If MsgBox("Close the Form?", vbYesNo, btn.Name & "_Click()") = vbYes Then
    DoCmd.Close acForm, fm.Name
   End If
End Sub

In the above-derived Class Module, the first two object declarations for Text Box Class Module and Collection Object we have done on the Employees Form's Class Module are brought down to this new Class Module.

Next, an Access.Form object fm is declared to hold the Employees form object. This is required to read the date of birth (the BirthDate) Text Box and calculate the Age of the employee and update the value in the Age Text Box on the Form.

Next, a Command Button control Object is declared within the derived Class Module Object with the WithEvents keyword to take control of the Command Button click on the Form.

Once the Form Object reference is received in the Public Property Set oFrm() as a parameter and after assigning it to fm Object the Class_Init() Subroutine is called to run the Code to enable the AfterUpdate and LostFocus Event, earlier we run from the Form_Load() Event Procedure.

Now the only Code we run from the Form Class Module, on the Form_Load() Event Procedure, is to pass the reference of the current Form Object (Me) to the oFrm() Set Procedure in the derived Class Module Object ClsTxtemployeeHeader.  This Form reference is passed to the ClsTxtEmployee Class Module instance, through the statement  Set tc.pfrm = fm.

The Command Button Click Event Procedure is enabled and captured in the btn_Click() Event Procedure in the Derived Class Module ClsTxtEmployeeHeader itself.

8.  New Form using Derived Cass Module: ClsTxtemployeeHeader

The Image of the second sample form, after transferring all its Form Module Code into the Derived Class Module ClsTxtEmployeeHeader.

The EmployeeHeader (a copy of the Employees Form) Form's Class Module VBA Code is given below.

Option Compare Database
Option Explicit

Dim T As New ClsTxtEmployeeHeader

Private Sub Form_Load()
  Set T.oFrm = Me
End Sub

The Dim statement instantiates the derived object of ClsTxtEmployeeHeader in memory.

The Form_Load() Event Procedure passes the current form object to the class object T.oFrm() Property Procedure as a parameter.

9. Summary

The Derived Class Module holds the entire Form's Class Module Code, except for a few lines on the Form's Code Module.

The entire Code will be written, otherwise in the Form's Class Module, is kept away and safe in the Base Class Module ClsTxtEmployees and in the Derived Class Module ClsTxtEmployeeHeader. 

When you create another Form with features of similar nature, in this Project, or in your other Projects you can easily customize both the Class Modules.  This will keep things well organized and easy to implement form rather than writing fresh code on all Form's Class modules.

You may download the Demo Database attached at the end of this page with the sample Forms and Class Modules and try out them.

After going through them you may try something similar on your own, using the demo database as a reference point so that you can be sure of what you have learned so far.

Downloads.




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:

WithEvents in Class Module for Sub-Form Text Box Events

Introduction.

So far we have used only a single Form and Text Box Controls on it to demonstrate the usage of WithEvents and built-in Event capturing, running subroutines in Class Object Arrays, or in Collection Object items.  But, how do we write code for Sub-Form(s) based TextBox controls, of the Main Form, to invoke built-in Events and to capture them in Class Module Objects?

The sample form image with two Sub-Forms and a few TextBoxes on all the Forms are given below for reference.

We will be using the familiar AfterUpdate or  OnGotFocus or OnLostFocus Events on TextBoxes on Main Form and Sub-Forms.  But let us concentrate on one or two points we have not yet dealt with so far.

  1. Introduction of the Access.Form Property in the Class Module of Text Box Control.
  2. How do we address the TextBox Controls on the Sub-Form, to invoke the built-in Events and capture them in the Class Module?
  3. How to move the insertion point from the last Text Box on the first Sub-Form in the first Text Box on the second sub-form?
  4. How to move the insertion point from the last Text Box on the second sub-form to the only text box on the Main Form?
  5. How to use the reference of other controls to read/write on the Form control, from within the current Event Procedure in the Class Module?

Why Form Property Required in Class Module.

So far we have worked with a single TextBox Property in the Class Module.  The Event Procedures also mostly deal with the Text Box Name and its Value alone. While working with the value of the current text box, that triggered the Event, there are situations where we need the value from other TextBox controls on the Form.

For example, if we update the employee’s date of birth on one text box, we need to find the employee’s retirement date, at the age of 56 years and update it in another Text Box.

Option 1: At the after-update event procedure of the date-of-birth text box, calculate the retirement date, and update it into the retirement date text box directly.

Option 2: After updating the date-of-birth Text Box the cursor jumps to the Retirement Date control.  About the OnGotFocus Event on this text box read the date-of-birth value from the Birth-Date text box, calculate the retirement date, and insert it into the current text box.

In either case, you have to work with two text boxes to read or write the value in it,  To read/write the value in the other text box we can reach it only through the Form Object. To do that, we need Access.Form Object as Property in the Class Module, besides the Access.TextBox control Object.

The new Class Module (ClsSubForm) Code is given below:

Option Compare Database
Option Explicit

Public WithEvents txt1 As Access.TextBox
Private frm2 As Access.Form

Public Property Get sFrm() As Access.Form
  Set sFrm = frm2
End Property

Public Property Set sFrm(ByRef frmValue As Access.Form)
  Set frm2 = frmValue
End Property

Private Sub txt1_AfterUpdate()
Dim ctl As Control, ctlName As String
Dim bd As Date

ctlName = txt1.Name
Select Case ctlName
    Case "EmpName"
      If Len(Nz(txt1.Value, "")) > 10 Then
         txt1.Value = Left(txt1.Value, 10)
         MsgBox "Maximum 10 characters only allowed.", vbInformation, ctlName & "_AfterUpdate()"
      End If
    Case "BirthDate"
      bd = CDate(Nz(txt1.Value, 0))
      If bd > 0 And bd < Date Then
         MsgBox "BirthDate Valid:" & txt1.Value, vbInformation, ctlName & "_AfterUpdate()"
      End If
    Case "Email"
       MsgBox "Email Address: " & txt1.Value, vbInformation, ctlName & "_AfterUpdate()"
    Case "Mobile"
       MsgBox "Mobile No. " & txt1.Value, vbInformation, ctlName & "_AfterUpdate()"
End Select

End Sub

Private Sub txt1_GotFocus()
'Got Focus Event is set for only
'Retirement Date field
'Testing for the field name is not required
Dim vDate As Variant, rdate As Date
Dim tmpRef As Access.Form

   Set tmpRef = frm2.frmSubForm1.Form
   'vDate = frm2.frmSubForm1.Form!BirthDate
   'vDate = frm2.frmSubForm1.Form.Controls("BirthDate").Value
   vDate = tmpRef!BirthDate
   
If vDate > 0 Then
    vDate = CDate(vDate)
    'calculate retirement Age/Date
    rdate = vDate + Int(56 * 365.25)
   txt1.Value = rdate
End If
MsgBox "Retire Date: " & txt1.Value, vbInformation, txt1.Name & txt1.Name & "_GotFocus()"
End Sub

Private Sub txt1_LostFocus()
Dim txtname As String
txtname = txt1.Name
Select Case txtname
    Case "RetireDate"
         'frm2.Controls("frmSubForm2").SetFocus
          frm2.frmSubForm2.SetFocus
    
    Case "Email"
          MsgBox "Email Address: " & txt1.Value, vbInformation, txtname & "_LostFocus()"
    Case "Mobile"
          MsgBox "Mobile Number: " & txt1.Value, vbInformation, txtname & "_LostFocus()"
          'frm2.Controls("EmpName").SetFocus
            frm2.EmpName.SetFocus
    Case Else
        If CDate(Nz(txt1.Value, 0)) = 0 Then
            MsgBox "Birth Date is Empty!", vbInformation, txtname & "_LostFocus()"
        End If
End Select
End Sub

Class Module Properties and Sub-Routines.

The TextBox Property is declared, with the WithEvents keyword, as txt1 Object, with Public Scope. Even though we are breaking the rule of encapsulation, by declaring this property as Public, it is justified because we are on the learning curve.  Later on, you can declare it as a Private Property and add the Get/Set Property Procedures and change the Code to address the txt1 Object Property through the Property Procedures.

The Form Object frm2 will be assigned to the Main Form (Form_frmMain with its sub-forms) Object so that we can retrieve values from sub-form-based text box controls, or write values into them too.

The txt1_AfterUpdate() Event Procedure in the ClsSubForm Class Module doesn't have anything new in it, because we saw its performance before.  The BirthDate, Email, and Mobile  Text Box controls are from both sub-forms on the Main Form.  The AfterUpdate Events triggered on the Sub-Form controls also come into this Sub-Routine.

The txt1.GotFocus() Event Procedure of RetireDate Text Box runs in this Sub-Routine.  To insert the employee’s retirement age/date we need to read the Date of Birth of the employee from the BirthDate Text Box.  To read the value from there we need the direct reference of this Text Box on the first sub-form, using the frm2 Main Form Object Property.

Referencing Sub-Form Text Box from Class Module.

Different versions of the BirthDate Text Box references are given below:

vdate = frm2.frmSubForm1.Form!BirthDate
vDate = frm2.frmSubForm1.Form.Controls("BirthDate").Value

Or take the long route:

Dim tmpRef As Access.Form
Set tmpRef = frm2.frmSubForm1.Form
vDate = tmpRef!BirthDate

Now, stepping into the txt1.LostFocus() Event Procedure the notable actions we have included here are transferring control (or setting the insertion point) from frmSubForm1 last Text Box to frmSubform2 first Text Box and frmSubform2 last Text Box to the only Text Box on the frmMain form.

  1. Moving the insertion point from the last Text Box on frmSubForm1 to the first Text Box on  frmSubForm2.
    frm2.frmSubForm2.SetFocus
    OR
    frm2.Controls("frmSubForm2").SetFocus
    
  2. Moving the insertion point from the last Text Box on Sub-form2 to the EmpName Text Box on the Main Form.
    frm2.EmpName.SetFocus
    OR
    frm2.Controls("EmpName").SetFocus
    

Common Pitfalls in Setting Focus on Sub-Form Control.

The pitfall that we often encounter in attempting to set the focus on a TextBox inside the sub-form directly is something like the following example:

frm2.frmSubForm2.Form.Email.SetFocus

The above statement is logically correct and will not give any error messages when run, but it will not work.  The point to note here is that a sub-form sits within a sub-Form Container control (with the same name as the Sub-Form) and setting focus on this control is enough.   When we do that the Text Box or any other Control on the sub-form with Tab Index 0 will automatically become active or will receive focus.

This is what we did in the first example, the reference of the sub-form container control on the Main Form (frm2.frmSubForm2.SetFocus) is used to set the focus.  When you add the .Form to the sub-form container-control, then it becomes the reference of a Form and the system simply ignores it.

The second option frm2.Control(“frmSubForm2”).Setfocus explicitly addresses it as a Control of the Main Form: frm2.

Setting Focus on control on Main Form from the sub-form is straightforward frm2.EmpName.SetFocus or frm2.Controls(“EmpName”).Setfocus.

Building the Derived Class Module: ClsSubFormHeader.

Armed with enough groundwork information on changes in the ClsSubForm Class Module we will proceed to build the Header Class (or Derived Class ClsSubFormHeader) to add the Form/Sub-Form Text Box Controls to the Collection Object.  We will add appropriate built-in Event statements required to announce the Event Procedure execution on the TextBoxes.  Without that, we cannot be sure that the Class Module actually does the job we assigned to it, since the actions are happening behind the curtain, not on the Class Module of the Form.  Once you make sure that everything works fine you can remove the unwanted messages, showing the values from the TextBox.

The point to note here is how to address (setting the reference to the TextBox controls on the sub-form(s) while adding it to the Collection Item.

Separate Class Object Instance for each Form Control

Remember this, a separate instance of the Class Object ClsSubForm is added, for each Text Box on all the Forms, as a separate Item in the Collection Object.  The Text Box’s required built-in Event triggering mechanism is also enabled in the Class Object, before adding it to the Collection Object.  Even though the Form’s Code Module is empty our Class Module Object is continuously monitored, for the enabled built-in Event to happen, from the Collection Object storage.  When it happens on a particular Text Box the related ClsSubForm Class Object instance-based sub-routine, like txt1_AfterUpdate(), executes and displays its result in the form of a message in the Application Window, or updates the value in a Text Box on the Form. 

The ClsSubFormHeader Class Object Code is given below:

Option Compare Database
Option Explicit

Private frm As Access.Form
Private T As ClsSubForm
Private C As New Collection

Public Property Get mFrm() As Access.Form
  Set mFrm = frm
End Property

Public Property Set mFrm(frmVal As Access.Form)
  Set frm = frmVal
  Call Class_Init
End Property

Private Sub Class_Init()
Dim cnt As Integer
Dim ctl As Control

'Scan for TextBoxes on the Main Form
For Each ctl In frm.Controls
   If TypeName(ctl) = "TextBox" Then
   
       Set T = New ClsSubForm ‘instantiate Class
       Set T.txt1 = ctl
       
       Select Case ctl.Name
           Case "EmpName"
               T.txt1.AfterUpdate = "[Event Procedure]"
       End Select
       C.Add T ‘add to collection object
   End If
Next

'Scan for TextBoxes on the Sub-Form
For Each ctl In frm.frmSubForm1.Form.Controls
   If TypeName(ctl) = "TextBox" Then
   
      Set T = New ClsSubForm ‘instantiate Class
      Set T.txt1 = ctl
      Set T.sFrm = frm
      
       Select Case ctl.Name
           Case "BirthDate"
                  T.txt1.AfterUpdate = "[Event Procedure]"
                  T.txt1.OnLostFocus = "[Event Procedure]"
           Case "RetireDate"
                  T.txt1.OnGotFocus = "[Event Procedure]"
                  T.txt1.OnLostFocus = "[Event Procedure]"
       End Select
       C.Add T 'add to collection Object
   End If
Next

'Scan for TextBoxes on the Sub-Form
For Each ctl In frm.frmSubForm2.Form.Controls
   If TypeName(ctl) = "TextBox" Then
   
      Set T = New ClsSubForm
      Set T.txt1 = ctl
      Set T.sFrm = frm
      
       Select Case ctl.Name
           Case "EMail"
               T.txt1.AfterUpdate = "[Event Procedure]"
           Case "Mobile"
               T.txt1.OnLostFocus = "[Event Procedure]"
       End Select
       C.Add T 'add to collection Object
   End If
Next

End Sub

The change is in the For Each . . . Next statement where we set the reference to the Sub-Form Controls to pick the Text Box Control from there and add it to the Collection Object, after enabling the required built-in Events.

On the first sub-form the For. . . Next Loop uses the following reference:

For Each ctl In frm.frmSubForm1.Form.Controls
.
.
.
Next

The ctl control carries the reference (address) of the sub-form Text Box while it is added to the Class Module ClsSubForm Txt1 Property and goes to the Collection Object as its Item.

In the same way, the second Sub-form Text Box references are used in the For . . .Next Loop.

For Each ctl In frm.frmSubForm2.Form.Controls
.
.
.
Next

Both the sub-Forms are set to their Has Module Property Value to Yes. Both sub-Form Class Module does not have any VBA Code.

The Main Form (frmMain) Module Code is given below:

Option Compare Database
Option Explicit

Private T As ClsSubFormHeader

Private Sub Form_Load()
  Set T = New ClsSubFormHeader
  Set T.mFrm = Me
End Sub

The ClsSubFormHeader derived Class is declared as Object T.  At the Form_Load() Event the  ClsSubFormHeader Class Object is instantiated.  The current Form Object is passed to the Property Procedure T.mFrm of ClsSubFormHeader

If you check the ClsSubFormHeader Class Object Code you can see that this Form Object is passed to the frm2 Property of ClsSubForm, through the Property Set Procedure sFrm, in the statement Set T.sFrm = frm along with each instance of the Class Module ClsSubForm, for each Text Box in Collection Object Item.

Summary.

The Sub-Form Control references are added to the Class Module Instance Property like any other control on the Main Form.  This is required when the enabled built-in Event triggers in sync with the correct Class Object instance and executes the Event Procedure.

You may download a Demo Database with all the Forms and VBA Code to try out and study the Code.

You may leave your observations, suggestions, and comments on the Comment Section of this Page.

Downloads.



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:

Access Form Control Arrays and Event-3

Introduction

This is the continuation of last Week’s Topic on Text Box AfterUpdate and LostFocus Event capturing and performing Validation Checks on values, in the Class Module Array.

Last week we stopped short of discussing how to move all the VBA codes from the Form_Load() Event Procedure into another Class Module and leave the Form Module almost empty of all Event Procedures.  The VBA code defines the Text Box Control Class Module Array and invokes the required built-in Events in their respective Array elements.  This method will need only three or four lines of Code on the Form Module and will take care of all the actions in a Derived Class Module Object.

Earlier we created Derived Class Objects, using Class Module as Base Class to extend the functionality of the Base Class.  We will do that here too.

We have taken the Text Box Control first, instead of any other Control on the Form, for the Array-based examples because TextBoxes are the main controls used on Form.  The TextBox has several Events, like BeforeUpdate, AfterUpdate, OnLostFocus, OnEnter, OnExit, KeyDown, KeyUp, OnKey, and several others, and depending on the requirements we can invoke one or more of the above Events in the Derived Class Object. 

We can write some standard Event Procedures in the Class Module for all of the above sample Events in the Text Box’s Class Module.  But, invoke only those required ones with the statement  obj.txt.EventName = “[Event Procedure]” during the Array element declaration time. 

The Class Module Event Sub-Routines may require customization of Code for different Form’s Text Box control values.  The best approach is to create a TextBox Class Module Template with the most frequently used Event Procedures.  Create a copy and customize it for a specific requirement.

Other Controls on the Form, like Command Buttons, Combo Boxes, List Boxes, and others, mostly use the Click or Double-Click Events only. We will take up this topic of managing different types of control Arrays on the same Form.

We will explore the possibility of better ways than Arrays to manage instances of different types of  Controls on the Form.

Moving Form's Class Module Code to Derived Class Module

Coming back to today’s topic, moving the Form Module Code to a separate Class Module, we will create a Derived Class Module Object using the ClsTxtArray1_2 Class Module as Base Class.  Move the Form Module Form_Load() Event Procedure Code into the new Class Object.

If you have not downloaded last week’s Demo Database then download it from the following link, before continuing.  We will make copies of the Modules and Forms to modify the Code so that you will have both versions of the Code and Form on the same database.  After making changes to the Code and Form Module you can instantly run and see how it works.

After downloading the database Open it in Ms-Access.  You may open the Form Module and check the Code.

We have to copy the Class Module (ClsTxtArray1_2) Code into a new Class Module with a different name ClsTxtArray1_3 but without any change in the code.  The form also must be copied with a different name frmTxtArray1_3Header.  The changes, if any, will be done in the new Copy leaving the original safe and unaltered.  This way the earlier Form and Class module will remain safe.

We will use last week’s sample Form (the image is given below) and the Form Module VBA Code also reproduced below for reference.

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

Make a Copy of the above Form and name it as frmTxtArray1_3Header.

Create a new Class Module with the name ClsTxtArray1_3.  Copy the VBA Code from ClsTxtArray1_2 Class Module and Paste it into the new Module.

Last week’s Class Module ClsTxtArray1_2  Code is reproduced below for reference.

Option Compare Database
Option Explicit

Private WithEvents Txt As Access.TextBox

Public Property Get mTxt() As Access.TextBox
  Set mTxt = Txt
End Property

Public Property Set mTxt(ByRef txtNewValue As Access.TextBox)
  Set Txt = txtNewValue
End Property

Private Sub Txt_AfterUpdate()
Dim txtName As String, varVal As Variant
Dim msg As String

txtName = Txt.Name
msg = ""
Select Case txtName
    Case "Text0"
        'Valid value range 1 to 5 only
        varVal = Nz(Txt.Value, 0)
        If varVal < 1 Or varVal > 5 Then
           msg = "Valid Value Range 1-5 only: " & varVal
        End If
    Case "Text8"
        'validates in LostFocus Event
    Case "Text10"
        'valid value 10 characters or less
        'Removes extra characters, if entered
        varVal = Nz(Txt.Value, "")
        If Len(varVal) > 10 Then
           msg = "Max 10 Characters Only. " & varVal
           Txt.Value = Left(varVal, 10)
        End If
    Case "Text12"
        'Date must be <= today
        'Future date will be replaced with Today's date
        varVal = DateValue(Txt.Value)
        If varVal > Date Then
          msg = "Future Date Invalid. " & varVal & vbCr & "Corrected to Today's Date."
          Txt.Value = Date
        End If
    Case "Text14"
        'A 10 digit number only valid
        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 be left Empty."
  Txt.Value = "XXXXXXXXXX"
End If

If Len(msg) > 0 Then
   MsgBox msg, vbInformation, Txt.Name
End If

End Sub

The Derived Class: ClsTxtArray1_3Header

The ClsTxtArray1_3 Class Module will be used as the Base Class for our new Derived Class Module, we will name it as ClsTxtArray1_3Header, with extended functionality.

Create a new Class Module with the name ClsTxtArray1_3Header. The Derived Class Module, with its Properties and Property Procedures, is given below:

Option Compare Database
Option Explicit

Private Ta() As New ClsTxtArray1_3
Private frm As Access.Form

Public Property Get mFrm() As Access.Form
  Set mFrm = frm
End Property

Public Property Set mFrm(vFrm As Access.Form)
  Set frm = vFrm
  Call Class_Init
End Property

Private Sub Class_Init()
 'Form Module Code goes here
End Sub

Copy and paste the above code into the new Header Class Module you have created.

Check the first two Property declarations.  First Property ClsTxtArray1_3 Class Object is instantiated as an undefined Array: Ta() – Ta stands for TextBox-Array.

The next Property frm is to take control of the Form, from where we plan to transfer the VBA Code here and whatever we did there will be done here. 

Next Get/Set Property Procedure will take care of the Form’s references.  It is  Set Property Procedure not Let, because we are passing a Form Object to it.

Immediately after the Form’s reference is received in the Set Property Procedure we call the Class_Init() (this is not the same as Class_Initialize() that runs automatically when a Class Object is instantiated) sub-routine to run the same code moved here from the Form’s Module.

Now, we will transfer the following Code from the Form_Load() Event Procedure into the Class_Init() sub-routine and make changes in the Form Module.

Copy and Paste the following lines of code from the Form Module into the Class_init() sub-routine, replacing the Comment line:

Dim cnt As Integer
Dim ctl As Control

For Each ctl In frm.Controls
  If TypeName(ctl) = "TextBox" Then
     cnt = cnt + 1
     ReDim Preserve Ta(1 To cnt)
     Set Ta(cnt).Txt = ctl
     
     Select Case ctl.Name
        Case "Text8"
            'Only LostFocus Event
            Ta(cnt).Txt.OnLostFocus = "[Event Procedure]"
     Case Else
            'All other text Boxes wiil trigger AfterUpdate Event
            'i.e. entering/editing value in textbox
            Ta(cnt).Txt.AfterUpdate = "[Event Procedure]"
     End Select
     
  End If
Next

Form's Class Module Code

Open the Form frmTxtArray1_3Header in the design view. Display the Code Module. Copy and Paste the following Code into the Form's Module, overwriting the existing Code:

Option Compare Database
Option Explicit

Private T As New ClsTxtArray1_3Header

Private Sub Form_Load()
  Set T.mFrm = Me
End Sub

We have instantiated the Derived Class ClsTxtArray1_3Header in Object Variable T,. With the statement Set T.mFrm = Me the active form's reference is passed to the T.mFrm() Set Property Procedure.

Immediately after this action, on the form_Load() Event procedure, the Class_Init() sub-routine runs in the ClsTxtArray1_3Header Class and the txtArray1_3 Class Object array elements are created by invoking Events for each Text Box on the Form.  Hope you are clear with the Code above.

If you are ready with modifying the Form Module, Compile the database to ensure that everything is in order.

Save and close the Form, Open it in Normal View and try out each TextBox and ensure that their Event sub-routines are performing as expected.

Replacing Class Object Array with Collection Object Items

The TextBox Class Object Array method works fine for several TextBoxes.  But, to create an Array, it should have a counter variable, Re-dimension the array for a new element preserving the data in earlier array elements.  Increment the counter variable for the next Text Box on the Form and so on. 

When there are several controls of the other types also on the Form (like Command Buttons, CommboBoxes, etc.)  we need to create Arrays of that Type’s Class Objects separately, with separate counter and re-dimension those controls Array too in the Class Module.  We will try these sometime later, to learn how to do it.

But, a better way to manage these complex situations is to use the Collection Object, in place of the Array.  We will run a demo here itself with the TextBoxes to get a feel for this method.

  1. Create a new Derived Class Module with the name ClsTxtArray1_3Coll.
  2. Copy and Paste the following Code into the Class Module:
Option Compare Database
Option Explicit

Private C As New Collection
Private Ta As ClsTxtArray1_3
Private frm As Access.Form

Public Property Get mFrm() As Access.Form
  Set mFrm = frm
End Property

Public Property Set mFrm(vFrm As Access.Form)
  Set frm = vFrm
  Call Class_Init
End Property

Private Sub Class_Init()
'-----------------------------
'Usage of Collection Object, replacing Arrays
'-----------------------------
Dim ctl As Control

For Each ctl In frm.Controls
  If TypeName(ctl) = "TextBox" Then
     
     Set Ta = New ClsTxtArray1_3  'instantiate TextBox Class
     Set Ta.Txt = ctl 'pass control to Public Class Property
     
     Select Case ctl.Name
        Case "Text8"
            'Only LostFocus Event
            Ta.Txt.OnLostFocus = "[Event Procedure]"
     Case Else
            'All other text Boxes wiil trigger AfterUpdate Event
            'i.e. entering/editing value in textbox
            Ta.Txt.AfterUpdate = "[Event Procedure]"
     End Select
     C.Add Ta 'add to Collection Object
  End If
Next

End Sub

A Collection Object Property is declared and instantiated at the beginning. 

The TextBox Class Module is defined, not instantiated, in Object Variable Ta.

The TextBox Class Ta Object is instantiated within the Control Type Test condition.  A new Ta Object instance is created for each TextBox on the Form.

After enabling the Events the Ta Class Object is added to the Collection Object as its Item.

This method is repeated by adding a new instance of the TextBox class Object for each Text Box on the Form, with its required Events enabled, as a new Item to the Collection Object.  The Code is cleaner than the Array method.

Make a copy of the Form frmTxtArray1_3Header with the name frmTxtArray1_3Coll

  1. Open it in Design View and display the Form's Code Module.
  2. Copy and Paste the Following Code into the Form Module, replacing the existing Code.
Option Compare Database
Option Explicit

Private Ta As New ClsTxtArray1_3Coll

Private Sub Form_Load()
  Set Ta.mFrm = Me
End Sub

The only change here is the Derived Object's name change to ClstxtArray1_3Coll. Compile the database again.

Save the Form, Open it in normal View. Test the TextBoxes as before.

It should work as before.

Downloads

You may download the database with all the Modules and Forms with all the suggested changes.



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:

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:

Access Form Control Arrays and Event Capturing

Introduction

Last week we learned how to capture Events, both built-in and User-Defined types, Raised from several Form based Controls, and to run Event-driven Programs in Class modules.  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:

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 TextBox Properties 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:

  1. Create a Class Module, with a single Text Box Object Property.
  2. Create separate Sub-Routines for  AfterUpdate, LostFocus, etc. in the Class Module to handle these Events from Text Box(s) on the Form. 
  3. Create an Array of this Class Module Object in the Form Module, or in a separate Class Module, with one Class Object element for each Text Box Control on the Form.
  4. The built-in Event Raised from a particular Text Box on the Form captures that Event by its corresponding Class Object Array-Element and runs the sub-routine Code from there. 

Sample Demo Project

Let us start with a simple example.

  1. Create a new Class Module and change its Name Property Value to clsTxtArray1.
  2. 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 Sub
    

    Declared 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.

  3. Create a new Form, insert a single Text Box on the Form and save the Form with the name frmTxtArray1.

    An image of the sample form is given below.

  4. Open the Form in Design View and display the form's Code Module.
  5. 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 Sub

    The VBA Code Line by Line

    In 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 the 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.

  6. If you are through with the above lines then save and close the Form.

  7. Sample Data Entry

    Open the Form in a normal view.

  8. Enter some numeric Value into the Text Box and press Tab Key. 

    A message box pops up with useful information in it.  A 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 the AfterUpdate sub-routine and the value entered into the TextBox is displayed at the end of the line.

    Adding More Controls on Form

  9. Close the Form and Open it in Design View.
  10. Add four or five more TextBoxes on the Form, anywhere you like on the Form.
  11. Save and Close the Form.

    A sample Image of the changed Form is given below.

  12. Open the Form in Normal View.
  13. Enter some numeric value in any newly added text box and press Tab Key.

    When you do this the MsgBox will pop up, displaying messages like the earlier one. It will contain the TextBox Name and the number entered into the Text Box.

  14. Try out other TextBoxes also in this way.

    You may add more TextBoxes and try out whether newly added TextBoxes also respond to the AfterUpdate event.

The After Update Event fires only when you enter some value or edit an existing value and leave it out of the Text Box.

Adding the LostFocus Event

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 the Class Module and display a message if it is empty.  To do that we must make changes in the 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

Moving to the Next Stage

Each TextBox on a Form may accept different information and their validity Criterion may be different.  The validity of the data, of each Text Box, must be checked individually to ascertain its 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 TextBoxes, from the sub-routine.  That may not be sufficient for real applications.  We need to write a 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.

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:

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