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

Streamlining Form Module Event Procedures.

The Existing Form Module Coding Approach

Access forms include a wide range of controls—such as text boxes, command buttons, and combo boxes—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 text box, 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 work through the examples carefully and try them yourself before moving on. Hands-on practice will do more for your understanding than reading alone.

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 step-by-step. (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 the sequence, experiment as you go, and you’ll build a solid foundation before tackling the more advanced patterns.

Control Event Procedures on a Form

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

Suppose we want to validate data entered into a TextBox control. For this, 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.

If either of the first two options is used, the form does not require a Class Module. However, when the [Event Procedure] option is selected, Microsoft Access automatically adds a Class Module to the form.

What’s interesting is how Access manages this internally: when an event is triggered from 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 implemented as 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.

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, raising 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 stub would look like this:


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

This mechanism provides a structured and consistent way to integrate VBA code directly within the event procedure stub, ensuring it is executed 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 that executes a small block of VBA code?

To begin understanding this, let’s walk through a simple example that illustrates 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 standalone class modules, making the coding process faster, cleaner, and more intuitive.

This method also opens the door to exporting and reusing VBA code segments across multiple projects, ultimately cutting down on development time and simplifying database maintenance.

User-defined Custom Events.

Key Words: Event, RaiseEvent, and WithEvents.

Open your Database.
Create a new Form.

Right-click to open Large Image in New Tab/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 handled correctly.
Change the Text Box's child Label Caption value to Msg:.
Save the Form with the name Form1 and close the Form.
Create a new Form with the Name Form2 and open it in Design View.
Change the size of the form to as small as Form1.
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 Popup property value of the Form to Yes
Select the Form Load Event Property and 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 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 Large Image in New Tab/Window.

The declaration line containing the WithEvents keyword establishes a Form object named frm and assigns it a reference to Form1’s Class Module, 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 subsequent code.
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 displayed 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 them in the Text box.

Right-click to open Large Image in New Tab/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) can be enabled dynamically at runtime.

Download the Demo Database.

UserDefinedEvents.zip