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.

Download EventSubForm2007.zip

Download EventSubForm2003.zip

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