Reusing Form Module VBA Code for New Projects. ~ LEARN MS-ACCESS TIPS AND TRICKS

Streamlining Form Module Event Procedures in a Standalone Class Module.

The Existing Form Module Coding Approach.

Access Forms include a wide range of controls—such as TextBoxes, CommandButtons, and ComboBoxes—each designed for specific tasks. Typically, we write event procedures within the Form’s Class Module to handle these tasks.

However, challenges often arise when a single control, such as a TextBox, requires multiple event subroutines. The related code becomes scattered across different event procedures, intermingled with code for other controls within the same form module.

This lack of structure can make the development process cumbersome. Developers frequently switch between Form Design View (to adjust the user interface or event properties) and the Form’s Class Module (to locate or refine event code). Over time, this repeated navigation slows down workflow and makes maintaining event code more difficult.

The Streamlining of Class Module Code.

Before You Dive In

This topic is nuanced. Please go through the examples carefully and try them yourself before moving on. Hands-on practice will enhance your understanding.

If you’re new to Microsoft Access Class Modules and building custom Class Objects, start with the introductory posts in the MS Access Class Module and VBA series. They’re written for beginners and walk through the basics. (Links are provided at the end of this page.)

How This Series Works

We’ll explore the core ideas over several installments, using practical, incremental examples. Along the way, you’ll get:

External Class-module VBA samples you can paste and run
Event-flow diagrams to visualize what’s happening under the hood
Downloadable demo databases with ready-to-run examples

Follow this topic, experiment as you go, and you’ll build a solid foundation before tackling the more advanced patterns.

Access Control's Event Procedures on a Form

Let’s take a closer look at something we often take for granted in Access development—writing event procedures

Suppose we want to validate data entered into a TextBox control; we might use the OnExit or BeforeUpdate Event. Access gives us three main ways to handle such events:

Macro – Enter the name of a macro in the event property, so the macro runs when the event (such as BeforeUpdate) occurs.
Public Function – Call a public function from the event property to execute the required code.
Event Procedure – Write VBA code directly in the form’s module, such as the BeforeUpdate() procedure, which runs when the event is triggered.

For the first two options, the form does not require a Class Module. However, the third option [Event Procedure] selection adds a Class Module to the Form.

What’s interesting is how Access manages this internally: an Event is triggered by a Control, Access captures it within the Form’s Class Module, and executes the VBA code written specifically for that Event.

All Access objects and controls—such as TextBoxes, ComboBoxes, ListBoxes, and others—are Objects defined in standalone Class Modules. Each of these objects comes with built-in properties (to determine appearance, formatting, colors, etc.) and Events to respond to user actions.

For example, when you place a TextBox control on a Form, Access creates it with a default name like Text0. You can, of course, rename it to something more meaningful. Internally, this TextBox is simply an instance of the 'Access.TextBox' Class. Besides that, the TextBox instance is qualified with the keyword 'WithEvents'. So the TextBox Class Object's definition on the Form is as WithEvents Text0 As Access.TextBox.

When you select [Event Procedure] in its BeforeUpdate property, Access wires up that event (essentially using a RaiseEvent call under the hood) and generates a blank Event Procedure stub in the Form’s Class Module for you to fill in. The Subroutine name always follows the same pattern, with the control’s name prefixed to the event, for example:

 Sub Quantity_BeforeUpdate()
    
 End Sub

In Microsoft Access, objects implemented through standalone Class Modules inherently come with their own built-in Events. Although the internal mechanics of how Access manages these events within Form controls are not fully visible to developers, we can observe the process by looking at how the On LostFocus event property is configured.

When this property (a string data type) is set to [Event Procedure], a sequence is set in motion:

The object’s event announcer activates and raises the event (RaiseEvent).
The object module listener—declared with WithEvents—captures this raised event.
Access automatically generates an empty event subroutine stub in the parent Form’s Class Module.

For example, for a control named Quantity, the resulting subroutine stub would look like this:


Private Sub Quantity_LostFocus()
    ' Place your VBA code here
End Sub

This mechanism provides a consistent way to integrate VBA code in the Event Procedure stub, ensuring that it executes precisely when the LostFocus event occurs.

Have you ever wondered how Microsoft Access defines its built-in events, and what actually happens behind the scenes to fire an Event Procedure that executes a small block of VBA code?

To start, let’s walk through a simple example that shows how object events are defined, raised, and captured, allowing us to write code in the Form Module and execute the assigned task.

In the coming weeks, we’ll dive deeper into this subject—exploring event firing and capturing in different scenarios. The objective is to introduce a streamlined approach to VBA coding within the independent standalone class modules, making the coding process faster, cleaner, and more intuitive.

The organized, structured coding method has the scope for exporting and reusing Form Module Code across multiple projects, saving development time and simplifying database maintenance.

User-defined Custom Events.

Keywords: Event, RaiseEvent, and WithEvents.

Let us examine how to define our own User-defined Event, trigger the Event, capture it, and run the custom event's procedure code.

Open your Database.
Create a new Form.

Right-click to open the large image in a New Tab or Window.
Insert a Textbox.
Display the Property Sheet of the Textbox.
Change the Name Property value to Msg.
Select the On Change Event Property and select [Event Procedure] from the drop-down list.
Click on the Build (...) Button to open the Form Module.
Copy and Paste the following VBA Code into the Form Module:
```
'Define user-defined Event Message
Public Event Message(txt As String)

Private Sub Msg_Change()
'Announce/Transmit the Event

    RaiseEvent Message(Me!Msg.Text)
End Sub
```
In the VBA code shown earlier, the initial declaration statement defines a user-defined Event named 'Message', which includes a single String parameter to be passed when the event is invoked. The Event must be declared with Public scope, followed by the Event keyword, the event name (note that it must not contain an underscore—so names like txt_Message are invalid), and an optional parameter list enclosed in parentheses.
Within the Change event procedure of the Msg TextBox control, the user-defined event is raised using the statement:
```
RaiseEvent Message(Me.Msg.Text)
```
By placing this statement inside the Change event, the event Message is triggered every time a character is typed in the TextBox. We will capture this event in another Form module and display the TextBox’s contents there, allowing us to verify that our user-defined event is being raised and captured correctly.
Change the TextBox's child Label Caption value to Msg:.
Save the Form named Form1 and close the Form.
Create a new Form named Form2 and open it in Design View.
Change the Form size to match Form1 dimensions.
Insert a Label control on the Form, and enter some text in the label's Caption to prevent Access from removing the Label control from the Form.
Change the Form's Popup property value to Yes.
Select the Form Load Event Property, select [Event Procedure], and click on the Build (...) Button to open the Form Module.
Copy and paste the following VBA Code into the Form2 Module, overwriting the existing Code Lines:
```
Option Compare Database
Option Explicit

'Declare the listener Form1 Class Object with the name frm.
Private WithEvents frm As Form_Form1  

Private Sub Form_Load()
On Error Resume Next
    Set frm = Forms("Form1") 'assign open Form Form1 object
End Sub

'Execute (Form1's) frm_Message Event, with Listener frm object as prefix
Private Sub frm_Message(str As String) 
    Me.Label0.Caption = str
End Sub
 
```
Right-click to open the Large Image in a New Tab or Window.

The declaration line containing the WithEvents keyword establishes a Form object named frm and assigns it a reference to Form1’s Class Module (Form_Form1), which is internally prefixed as Form_ (e.g., Form_Form1). In VBA, you cannot reference a Form in this manner unless the Form has an associated Class Module.

The WithEvents keyword functions as an Event listener—similar to a radio receiver—capturing events that occur on Form1.

However, simply declaring a form object with WithEvents (like a Dim statement) is not enough. The frm Object variable must be explicitly initialised with a reference to the active Form1 instance (the RaiseEvent “transmitter”) currently loaded in memory.

This is achieved in the Form_Load() event procedure using the statement:
```
Set frm = Forms("Form1") 
```
If Form2 is opened before Form1, this statement will cause an error. To handle such cases, we include an error-handling line to bypass the error and allow the program to continue executing the code.
If you are sure that Form1 has a Class Module, then you can use this statement:
```
Set frm = Form_Form1 'The Form will open in memory but not visible in Application Window. 
'You Need another statement.

frm.Visible = True
```
The next subroutine contains the actual action code for our user-defined Event.
Each time we type a character in the TextBox on Form1, the text will instantly appear in the Label control on Form2.
The user-defined event Message() is fired whenever a character is typed in the TextBox on Form1. This event is then captured in Form2 and displays the typed character in its Label control.
Note: At this stage, the Form_Form1 Module has evolved into a fully functional object—similar to a TextBox—equipped with all three mechanisms required for event handling:
1. Event – the declaration of the event.
2. RaiseEvent – the trigger that fires the event.
3. WithEvents – the listener that captures the event.
When Form1 is instantiated in the Form2 Class Module, it gains event-listening capability. However, the corresponding event procedure code must always be written in the parent module (in this case, the Form2 module) of the instantiated Form1 object.
Save and Close Form2. Close Form1 if it is kept open. Let us test our user-defined Event Message.
Open Form1 in Normal View.
Open Form2 in Normal View and drag it away from Form1.
Type Hello World or anything you like in the TextBox on Form1. The typed text should appear in the Label control on Form2, each character as you type in the TextBox.

Right-click to open the large image in a New Tab or Window.

Hopefully, you now have a clear understanding of how an event is:

Defined in Form1,
Invoked using RaiseEvent, and
Captured in Form2, where the related subroutine in the Form2 module executes the required task.

Note: On Form2, we continuously monitor Form1 by establishing a reference to it in the frm object declared with the WithEvents keyword. When the Message event is triggered (RaiseEvent) on Form1, the frm object in Form2 (an instantiated replica of the Form1 module object) immediately captures it. This, in turn, runs the corresponding event procedure—automatically prefixed with frm_ (e.g., Private Sub frm_Message())—in the Form2 module.

You can try this example, or experiment with two other forms, to better understand the relationship and logic behind Event, RaiseEvent, and WithEvents in capturing and executing event-driven VBA code.

In the next article, we will explore how a predefined TextBox event (such as LostFocus) is dynamically enabled at runtime.

Download the Demo Database.

UserDefinedEvents.zip