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

Wednesday, April 12, 2023

Reusing Form Module VBA Code for New Projects.

Streamlining Form Module Event Procedures.

The Existing Form Module Coding Procedure.

Access Forms incorporate a variety of controls, including Textboxes and Command Buttons, each serving specific functions. Typically, we create numerous Event Procedures in the Form's Class Module to execute various tasks related to these controls. However, a common challenge arises when handling multiple Event Subroutines for a single TextBox. The code tends to be dispersed, lacking organization, and intermixed with Event Procedure Codes for other controls in the Form Module.

This often results in a less-than-optimal development experience, requiring us to repeatedly navigate to the Form in design view. Whether making user interface design adjustments or refining code in the Form Module, we find ourselves frequently accessing the Form to locate and modify specific Event Procedure Code through its associated Event related Property.

The Streamlining of Class Module Code.

This topic is intricate, demanding your careful attention to the series of examples provided on these pages. It’s essential to grasp the concept and procedures thoroughly by experimenting with them firsthand before advancing to the next level.

For those unfamiliar with Microsoft Access Class Module and constructing Access Class Objects, it’s recommended to review the earlier blog posts, starting from the MS Access Class Module and VBA series of articles. These initial articles were designed for beginners to grasp the fundamentals of building Class Module Objects through tutorials. Links to these earlier articles are provided at the end of this page for your convenience.

This topic will be explored through a series of articles distributed over several weeks, aiming to elucidate the fundamentals of this intricate concept through practical examples. Readers can expect to find external Class Module VBA code samples, event flow diagrams, and downloadable demo databases with ready-to-run examples in the upcoming weeks.

Control's Event Procedures on the Form.

But, let us try to understand a few things we take for granted, like writing Event Procedures for controls on the Form. When we want to do some task, like check the validity of the data in the Text Box we take the OnExit or BeforeUpdate Event Property and use one of the three following choices:

1. Gives the name of a Macro in the Event Property to run the Macro Code when the Event (like BeforeUpdate) takes place.

2. Call a Public Function from the Event Property to run.

3. Or write an Event Procedure in the Form Module, like BeforeUpdate(), to execute the Code when the Event is fired.

If one of the first two options is used, then the Form doesn't need a Class Module. But, when the [Event Procedure] option is selected, the MS Access System automatically adds a Class Module to the Form.

It’s fascinating to understand how the system triggers the event action from the form’s controls, captures it within the form’s Class Module, and executes the VBA code tailored for that control’s specific task.

All Access objects or controls, such as TextBoxes, ComboBoxes, ListBoxes, and others, are structured as objects within standalone Class Modules. Each of these objects possesses properties that determine their appearance, colors, and inherent events.

When we select a TextBox control from the menu and position it as desired, Access generates it with a default name, such as Text0. However, we have the option to replace this default name with a more descriptive one. The TextBox we’ve added to the form is essentially a copy (instance) of the Access.TextBox Class. By selecting the [Event Procedure] option in the BeforeUpdate Event Property, we trigger the event (using the RaiseEvent action) and subsequently write the event subroutine code within the empty event subroutine stub automatically provided by the Access System. Notably, the control’s name is consistently prefixed in the event procedure subroutine name, like so:

 Sub Quantity_BeforeUpdate()
    
 End Sub

In the realm of Access Objects designed with standalone Class Modules, they inherently possess their own set of built-in Events. While the inner workings of how Microsoft Access manages these events for controls on a Form may not be explicitly transparent, a notable observation lies in the configuration of the On LostFocus Event Property.

When this String Data Type property is set with the text [Event Procedure], a sequence unfolds: the Object's Event Announcer activates, announcing the event (RaiseEvent). Simultaneously, the Object Module Listener (utilizing WithEvents) captures this announcement. Consequently, an empty subroutine stub emerges in the Parent Form Module, resembling 'Sub Quantity_LostFocus()', awaiting the VBA Code to be seamlessly integrated within this empty procedural framework. This methodology facilitates a structured approach to incorporating code within the designated Event Procedure stub, triggered by the LostFocus event.

Have you ever thought of finding out how the inbuilt Events are defined and what it does to fire an Event to execute a small block of VBA Code to do some task?

Let's delve into a straightforward example to grasp how object events are defined, raised, and captured to write code in the form module and execute the assigned task. Over the upcoming weeks, we'll extensively explore this topic of event firing and capturing in various contexts. Our goal is to introduce a fresh approach to VBA coding within the standalone class module, enabling swift and seamless coding. Furthermore, this approach facilitates the export and reuse of VBA code segments for other projects, thereby reducing database development time.

User-defined Custom Events.

Key Words: Event, RaiseEvent, and WithEvents.

Open your Database.
Create a new Form.
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
```
The initial declaration statement in the VBA code above introduces a user-defined event named Message(), accompanied by a single parameter of String type to be passed when invoked. The event must be defined with a public scope, followed by the keyword Event, and then the event name (not containing the underscore character, such as txt_Message), along with any parameter list enclosed in parentheses.
In the Change Event Procedure of the Msg TextBox, we trigger the Message Event Procedure using the statement RaiseEvent Message(Me.Msg.Text). By running the Event within the Change Event Procedure, each time a character is typed in the TextBox on the form, the Event is triggered. We’ll capture this action in another Form Module and display the TextBox contents there to ensure that our User-defined Event is functioning 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
 
```
Check the line with the WithEvents keyword. This declaration line establishes a Form object named frm and assigns a reference to Form1’s Class Module, with the prefix Form_ (Form_Form1). In VBA or elsewhere, you cannot reference a form in this manner if the form lacks a Class Module.
The WithEvents keyword is known as a Listener (like a Radio Receiver) of Events that takes place on Form1.
Merely defining a form object with the WithEvents keyword, similar to a Dim statement, is not sufficient. We must initialise the frm object variable with the active Form1 (the RaiseEvent - Transmitter) object reference in memory.
In the Form_Load() Event Procedure, we accomplish this with the statement Set frm = Forms(“Form1”). However, if Form2 is opened before Form1, it will result in an error. To circumvent this issue, we’ve included an error trap line to ignore the error and proceed with the subsequent line of code.
The next Subroutine is our actual user-defined event's action-packed Code.
Every time we type a character in the text box on Form1; this will immediately appear on the Label Control on Form2.
The user-defined Event Message() will be fired every time we type a character in the Text box on Form1 and will be captured in Form2 and displayed in the Label Control.
Note: Now, the Form_Form1 Module is a complete Object like a TextBox, with all the three Event Firing and Capturing mechanisms: Event, RaiseEvent, and the WithEvents (Event Capturing ability) added when Instatiated in Form2 Class Module. The Event Procedure Code must be written on the parent module (Form2 Module) of the Form1 Object Instance.
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.

Hope you understood the concept of how an Event is defined in Form1 and how it is invoked and captured in Form2 and executes the related Subroutine Code in Form2 Module to do some task.

Note: On Form2, we monitor Form1 by establishing a reference to it in the frm object using the WithEvents keyword. When the Message Event is triggered (RaiseEvent) on Form1, the frm object associated with Form2 (a replica of the Form1 Module object) promptly captures it, and the corresponding subroutine prefixed with frm_ (Private Sub frm_Message()) is executed promptly.

You may try this or similar procedure with two different Forms to understand the relationship and logic of declaring Event, RaiseEvent, and WithEvents for capturing and Executing Event Procedure VBA Code.

Next week we will try how the RaiseEvent predefined Event of TextBox, like LostFocus is enabled at Run-time.

Download the Demo Database.

UserDefinedEvents.zip

MS Access Class Module Object Lessons for Beginners.

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

Streamlining Form Module Code in Standalone Class Module.

1 comment:

XeniOctober 2, 2023 at 7:01 PM
Hi, aprpillai, Thanks a lot for sharing this information. I didn't know nothing about this user defined events and how to use it. I'm going to follow all you have written about it in your block. ;-)
ReplyDelete
Replies