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.
- MS-Access Class Module and VBA
- MS-Access VBA Class Object Arrays
- MS-Access Base Class and Derived Objects
- VBA Base Class and Derived Object-2
- Base Class and Derived Object Variants
- MS-Access Recordset and Class Module
- Access Class Module and Wrapper Classes
- Wrapper Class Functionality Transformation
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 astxt. -
If the property is declared as Private, then create corresponding Property Get and Property Set procedures to manage access to the
txtobject. -
In the Class Module, create event-handling procedures for the
AfterUpdate,GotFocus, andLostFocusevents 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
AfterUpdateevent 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.
- WithEvents MS-Access Class Module Tutorial
- WithEvents and Defining Your Own Events
- withevents Button Combo List TextBox Tab
- Access Form Control Arrays and Event Capturing
- Access Form Control Arrays and Event-2
- Access Form Control Arrays and Event-3
- 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 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.
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.
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.
Downloads.
Download ClsEmployees2007.zipLinks to WithEvents ...Tutorials.
- WithEvents MS-Access Class Module Tutorial
- WithEvents and Defining Your Own Events
- withevents Button Combo List TextBox Tab
- Access Form Control Arrays and Event Capturing
- Access Form Control Arrays and Event-2
- Access Form Control Arrays and Event-3
- WithEvents in Class Module for Sub-Form TextBox Events
- WithEvents in Class Module and Data Entry
- WithEvents and Access Report Event Sink
- WithEvents and Report Lines Hiding
- WithEvents and Report Lines Highlighting
- Withevents TextBox and Command Button Arrays
- Withevents TextBox CommandButton Dictionary
- Withevents and all Form Control Types
