WithEvents in Class Module and Data Entry Form ~ LEARN MS-ACCESS TIPS AND TRICKS

1. Introduction.

So far, we have used unbound text boxes on the form for demo runs and for capturing built-in events within the Class Module object. Since text boxes are the primary controls used on forms for data entry, display, and viewing of information, they were chosen first to demonstrate the most commonly used built-in events: AfterUpdate, OnGotFocus, and OnLostFocus.

However, this does not mean that we have ignored other controls such as command buttons, combo boxes, list boxes, and option buttons. Most of these controls primarily use the Click event to open forms or reports, run macros, call subroutines/functions, or select items from a list or combo box. Their event procedures are generally simpler to handle within the Class Module, and we will include them along with text boxes in our approach.

Once you are comfortable handling text box–based events and understand how they work, adding support for other controls will be a straightforward extension of the same concept.

Creating a Class Module object instance—one instance for each text box on the form—and adding them to an array, each enabled with the required built-in events, may seem confusing at first, but it is not as complicated as it appears.

If you are new to programming with stand-alone Class Modules, it is highly recommended to start there. Understanding the basics will make it much easier to grasp their usage with WithEvents, Event, and RaiseEvent programming.

Refer to the following articles to get a general understanding of Class Module programming.

2. Brief Review of what is covered so far.

In the past few weeks, we have begun exploring Class Module and Form Controls Event Programming in Microsoft Access. As part of this journey, we have learned how to create a Class Module object array element for each text box on a form.

The basic steps for preparing a Class Module to handle events for a text box are as follows:

Create a new Class Module and declare a property to hold a Text Box (Access.TextBox) object, using a variable name such as txt.
If the property is declared as Private, then create corresponding Property Get and Property Set procedures to manage access to the txt object.
In the Class Module, create event-handling procedures for the AfterUpdate, GotFocus, and LostFocus events of the text box.

When an instance of the above Class Module is created in memory, it can handle the events (AfterUpdate, GotFocus, and LostFocus) of only one text box on the form.

If there are multiple text boxes—for example, three text boxes—on the form, then we must create three separate instances of the same Class Module, one for each text box.

To keep track of all these instances, they should be stored either in an array or added as items in a Collection object, so that each text box has its own dedicated Class Module instance.

If, instead, the same Class Module instance is shared among multiple text boxes, then the event procedures inside the Class Module would have to be modified. In that scenario, each event procedure must identify which text box triggered the event and then run the appropriate validation checks or other actions for that specific text box within the same subroutine.

Example:
The code shown below is taken from the AfterUpdate() event procedure.

In this example, the FirstName and Designation text boxes are listed in the Select Case ... End Select structure, but they don’t have any executable code under their respective Case blocks. This indicates that:

These text boxes exist on the form,
but their AfterUpdate event is not currently enabled or used.

Since no logic is implemented for them in the AfterUpdate() event, it is not strictly necessary to include their names in this procedure.

However, keeping them in the list can be useful during testing because it serves as a reminder that these text boxes exist on the form and can be enabled later if needed.

It’s also possible that these text boxes are intended to use a different event, such as OnLostFocus(), and their logic might be implemented in the corresponding LostFocus event procedure instead.

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 to read or write values from or to any other text box (other than the event-triggered text box), you must include the Property 'Access.Form' in the text box class module (ClsTxtEmployees). This property allows you to reference other text boxes on the same form for reading or updating their values as needed.

This class module can also serve as a template for handling text boxes on other forms. However, you will need to customize the subroutines according to the specific requirements of each form’s text box controls.

For your convenience, links to all the earlier articles in this series are provided below, especially if you are new to working with WithEvents, Event, and RaiseEvent in user-defined or built-in event programming with Microsoft Access.

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 that used unbound text boxes on a form, you will notice no functional difference in this demo. The purpose here is simply to demonstrate how event handling in a class module object works when the form is bound to a table and the text boxes use table fields as their control sources.

The sample image of the data entry form is shown below. An error message (manually positioned at the bottom-right corner in the image) appears when an invalid Join Date—earlier than the Birth Date—is entered in the field.

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

In the first Employee Form demo, the Command Button click event is handled directly in the Form’s own Class Module.

In this demo, the Form’s Class Module works together with a separate Class Module that includes two Private properties:

The Property 'Txts' for the TextBox object, and
efrm for the Access.Form object.

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.

In the global section of the Class Module, a Private Property named efrm is declared to hold the 'Access.Form' object.

Next, an 'Access.TextBox' control is declared as a Private Property named Txts using the WithEvents keyword. The WithEvents keyword enables the Txts object to capture and respond to programmed Events triggered on the Form.

Because these Class Module properties are declared as Private, they are accessible only within the Class Module itself, preventing direct external access. To allow values to be assigned or retrieved from outside, the module must define Public Get and Set Property Procedures.

The first pair of Get/Set Property procedures manages the efrm (Access.Form) object.

The second pair of Get/Set Property procedures manages the Txts (Access.TextBox) object.

Any validation checks on the Set Property Procedure parameter can be performed before attempting to assign the value to the Property efrm or the Property 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 validate the values entered in the LastName, BirthDate, Age, and JoinDate fields, and display appropriate messages—mainly to confirm that the programmed events are being correctly captured by the Class Module instances.

LostFocus() The event procedure validates the FirstName and Designation field values. If either of these fields is left empty when it loses focus, a default text string “XXXXXXXXXX” is assigned to the field.

All the TextBox control names are listed within the Select Case … End Select structure for clarity, though some of them have no executable code yet. In the LostFocus event procedure, actual code has been written only for the FirstName and Designation fields. The LostFocus event is enabled on the form only for these two fields, while the others are included for clarity and possible future use.

The first TextBox on the form is bound to an AutoNumber field, which generates values automatically. Since no events are intended to be trapped for this control, it is excluded from the Select Case … End Select structure and no events are enabled for it.

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 a 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 in the earlier examples, we will now create a derived class object (ClsTxtEmployeeHeader) using the ClsTxtEmployee class as its base. We will then move the existing Form Module code into this new Class Module, leaving only a minimal set of essential lines in the form’s own Class Module.

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 newly created derived Class Module, the first two object declarations—one for the TextBox Class Module and the other for the Collection object—previously placed in the Employees Form’s Class Module are now moved into this Class Module.

Additionally, an 'Access.Form' object named fm is declared to hold a reference to the Employees form. This reference is needed to read the value from the BirthDate TextBox, calculate the employee’s age, and update the Age TextBox on the form accordingly.

Next, a Command Button control object is declared within the derived Class Module using the WithEvents keyword to capture the Command Button’s Click event on the form.

Once the form object reference is received as a parameter in the Public Property Set oFrm() procedure and assigned to the fm object, the Class_Init() subroutine is called. This subroutine enables the AfterUpdate and LostFocus event handling, which were previously initialized from the Form_Load() event procedure.

Now, the only code remaining in the form’s Class Module is a few lines in the Form_Load() event that pass the current form object (Me) to the oFrm() property procedure of the derived Class Module ClsTxtEmployeeHeader.

This form reference is then passed on to each instance of the ClsTxtEmployee Class Module through the statement:

Set tc.pfrm = fm.

The Command Button’s Click event is also enabled and captured within the derived Class Module ClsTxtEmployeeHeader itself, through the btn_Click() event procedure.

8. New Form using Derived Class Module: ClsTxtemployeeHeader

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

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

The EmployeeHeader 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.

All the code that would otherwise be written directly in the Form’s Class Module is now safely organized within the Base Class Module ClsTxtEmployees and the Derived Class Module ClsTxtEmployeeHeader.

When you need to create another form with similar functionality—either in this project or in other projects—you can simply reuse and customize these two Class Modules instead of writing new code in each form’s Class Module. This approach keeps your code well-organized, modular, and easier to maintain.

You can download the demo database provided at the end of this page, which includes the sample forms and Class Modules, and experiment with it.

Once you have explored the demo, try creating something similar on your own, using the demo database as a reference point, so you can reinforce and validate what you have learned so far.