WithEvents in Class Module for Sub-Form Text Box Events

Introduction.

So far, we have demonstrated the use of WithEvents and built-in event capturing using a single form with TextBox controls, executing subroutines in Class Object arrays or Collection objects. But how can we handle TextBox controls on Subforms of a main form—capturing their built-in events and processing them in class module objects?

For reference, the image of the sample form below displays two sub-forms and multiple TextBoxes across all forms.

Right-click to open Large Image in New Tab/Window.

We will continue using the familiar AfterUpdate, OnGotFocus, and OnLostFocus events for TextBoxes on both the main form and its subforms. However, let us first focus on one or two aspects that we haven’t addressed so far.

1. Introduction of the 'Access.Form' property in the Class Module for TextBox controls.

2. How to reference TextBox controls on a subform to trigger built-in events and capture them in a Class Module.

3. How to move the insertion point from the last TextBox on the first subform to the first TextBox on the second subform.

4. How to move the insertion point from the last TextBox on the second subform to the only TextBox on the main form.

5. How to use references to other controls to read from or write to form controls from within the current event procedure in the Class Module.

Why is Form Property Required in the 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, which triggered the Event, there are situations where we need the value from other text box 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 directly into the retirement date text box.

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 them.  To read/write the value in the other text box, we can reach it only through the Form Object. To do that, we need an 'Access.Form' Object as a 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 as a WithEvents object named txt1, with public scope. Although this breaks the usual rule of encapsulation by exposing the property publicly, it is acceptable here for learning purposes.

Later, once you are comfortable with the concept, you can declare this property as Private and introduce Get and Set property procedures to access the txt1 object indirectly through these procedures, ensuring proper encapsulation.

The Form object frm2 is assigned to the Main Form (Form_frmMain)—which contains the sub-forms—so that we can read values from, or write values to, text box controls on the sub-forms.

The txt1_AfterUpdate() Event procedure in the ClsSubForm class module doesn’t include anything new, as we have already seen its functionality earlier. The BirthDate, Email, and Mobile text box controls are located on both sub-forms within the main form, and the AfterUpdate Events triggered on these sub-form controls are handled within this same subroutine.

The txt1_GotFocus() event procedure, used for the RetireDate text box, is also handled here. To calculate and insert the employee’s retirement date, we must first read the Date of Birth from the BirthDate text box on the first sub-form. To do this, we use the frm2 main form object property to directly reference that text box control.

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, moving on to the txt1_LostFocus() event procedure.
The key functionality implemented here is the automatic transfer of focus (insertion point) between text boxes across different forms.

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 produce any error messages when executed, but it will not work as intended.

The important point to understand here is that a sub-form resides within a sub-form container control (which typically has the same name as the sub-form itself).

To set focus on a control within a sub-form, you must first set focus on its container control.

Once the sub-form container receives focus, the control within the sub-form that has Tab Index = 0 will automatically become active and receive focus.

In the first example, we set the focus using the reference of the sub-form container control on the Main Form:

frm2.frmSubForm2.SetFocus

Here, frmSubForm2 is the name of the sub-form container control.

If you append '.Form' to this reference (like frm2.frmSubForm2.Form.SetFocus), it becomes a reference to the sub-form’s Form object. The system will simply ignore the SetFocus call, because SetFocus applies only to controls, not to Form objects directly.

As an alternative, you can explicitly refer to it as a control on the Main Form using:

frm2.Controls("frmSubForm2").SetFocus

Setting focus on a control on the Main Form from within the sub-form is more straightforward, for example:

frm2.EmpName.SetFocus

or

frm2.Controls("EmpName").SetFocus

Building the Derived Class Module: ClsSubFormHeader.

With sufficient groundwork laid for modifying the ClsSubForm Class Module, we can now proceed to build the Header Class (Derived Class ClsSubFormHeader). This class will handle adding the TextBox controls from both the main form and its sub-forms into a Collection object.

We will also include the necessary built-in event procedures to confirm that these events are being triggered and handled by the class. These event-triggered messages will serve as visual indicators that the class module is indeed capturing and executing the assigned tasks—especially since all the action now takes place behind the scenes, rather than in the form’s own class module.

Once you’ve verified that everything is working as intended, you can remove these temporary message displays that show values from the TextBox controls.

Separate Class Object Instance for each Form Control

Keep in mind that a separate instance of the ClsSubForm class object is created for each TextBox control across all the forms, and each instance is stored as an individual item in the Collection object.

Before adding a TextBox to the collection, its required built-in event handlers are enabled within its corresponding class object instance.

Although the form’s own code module remains empty, the class object instances stored in the Collection are actively monitoring for their assigned events.

When a particular event occurs on a specific TextBox, the corresponding ClsSubForm class object instance handles it—executing its relevant event procedure (for example, txt1_AfterUpdate()) and then either displaying the result as a message in the application window or updating the value of another TextBox 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 subforms have their ‘HasModule’ property set to ‘Yes’. However, their form modules do not contain 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 the object T. In the Form_Load() event, an instance of the ClsSubFormHeader class is created, and the current form object is passed to its T.mFrm property procedure.

If you look at the ClsSubFormHeader class module code, you’ll see that the form object is passed to the frm2 property of ClsSubForm through the Property Set procedure sFrm, using the statement Set T.sFrm = frm, for each instance of the ClsSubForm class created for every text box added as an item in the Collection object.

Summary.

The Subform control references are added to the Class Module instances just like any other control on the main form. This ensures that when the built-in events are triggered, they remain synchronized with the correct Class Object instance and execute the appropriate event procedures.

You can download a demo database containing all the forms and VBA code to explore and study the implementation in detail.

Feel free to share your observations, suggestions, and comments in the Comments 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