January 2024 ~ LEARN MS-ACCESS TIPS AND TRICKS

Streamlining Event Subroutine Code in Standalone Class Module.

How Does the Event Firing Mechanism Work within Access Objects?

This topic was briefly touched on during the Presentation of Streamlining Form Module Code in the Standalone Class Module for Access User Groups (Europe) Chapter.

The Event-related Key Words: Event, RaiseEvent, and WithEvents.

Event - used to Define an Event.
RaiseEvent - to Invoke the Event.
WithEvents - to Capture the fired Event and execute the Event Subroutine Code.

In the preceding articles, we gained insights into the utilization of Event-related Keywords and crafted Event Subroutines within Standalone Class Modules rather than within the Form Module. Notably, the Event and WithEvents keywords were prominently featured in the Object Browser, as illustrated below:

However, the RaiseEvent serves as an internal event-firing mechanism that accommodates multiple options within a dedicated event-related property. This Event is initiated from within the Class Object, functioning as a system program. It assesses the specified option in the event property and executes the selected choice, be it a macro, function (user-defined or built-in), or the text [Event Procedure]. This, in turn, triggers the RaiseEvent, like the functionality of the Call Statement in VBA.

The following Link gives the details about the RaiseEvent Statement: https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/raiseevent-statement

The authentic Event-firing mechanism is an internal system program intricately linked to each event of an object within the Access System. For instance, the AfterUpdate Event encompasses an AfterUpdateMacro, typically concealed from the Object Browser Window. To reveal it, one can simply right-click and select the option 'Show Hidden Members'. Analogously, other Object Events, such as CommandButton Click, feature the OnClick Event property to specify the execution option. Furthermore, there's the OnClickMacro, which evaluates the provided value in the OnClick Property, facilitating the execution of the designated option.

Assumptions Based on Observation.

Upon scrutinizing the execution pattern of the specified option within the Event Property, I found it worthwhile to attempt to create a straightforward subroutine that emulates the methodology employed by the Event mechanism. This endeavor aims to replicate the process by which the event mechanism executes the designated option specified in the Property Sheet.

An interesting observation emerges: when a macro name or function name is specified in the Event Property, it triggers the execution of the designated macro or built-in function, as well as user-defined functions in a standard module. Notably, for these two options, there is no requisite attachment of a Class Module to the Form, and the 'Has Module' Property of the Form can be set to False.

However, opting for the [Event Procedure] choice in the Event Property triggers an automatic attachment of a Class Module to the Form. Simultaneously, an empty Event Subroutine Stub is seamlessly added to the Form Module, serving as a canvas to inscribe the necessary subroutine code within this newly associated Class Module.

The observed behavior of the Event running mechanism implies that when a value is entered into the Event Property in the Property Sheet, the system intelligently identifies it. Each of the three options—Macro, Expression, or the text [Event Procedure]—bears distinctive markers, enabling the system to recognize and execute the corresponding event-related action accordingly. This nuanced recognition ensures that the system interprets and responds to the specified option accurately.

Expression: Should start with an equal symbol (=) character followed by the Function Name and Parameters, if any, like =MsgBox("Hello World") or a User-Defined Function in the Standard Module.

Building upon these assumptions, I've crafted a subroutine designed to emulate this behavior when one of the three aforementioned options—Macro, Expression, or [Event Procedure]—is provided in a TextBox Control.

The Event Running Form Image is given below:

The above Form is divided into two Parts:

The upper portion of the form, delineated by the thick horizontal black line in the middle, serves as our experimental ground for exploring the event execution method. Here, we endeavor to unravel the intricacies of the actual event running mechanism within the Access System.
In the Section below the horizontal line, we will run the same Event Options as we normally do in the AfterUpdate Property of a TextBox.

Within the initial section, a ListBox offers a range of options that can be swiftly selected by a simple click. Upon selection, the chosen option promptly populates the Text2 TextBox Control above. Analogous to the AfterUpdate Event Property in TextBox's Property Sheet, the specified option in the TextBox Control instantaneously executes within our Event Subroutine AfterUpdateMac().

The Event running Subroutine AfterUpdateMac() is written within the Standalone Class Module ClsAfterUpdateMacro.

The ClsAfterUpdateMacro Class Module VBA Code.

The Class Module with the Subroutine AfterUpdateMac() VBA Code is given below:

Option Compare Database
Option Explicit

'User-Defined Event
Public Event AfterUpdat(ByVal txt As String)
Private After_Update As String
'Options: Macro,Function,"[Event Procedure]"

'------------------------------------------------------
'Streamlining Form Module Code
'in Stand-alone Class Modules
'------------------------------------------------------
'AfterUpdateMac() Event Processing Subroutine
'Author: a.p.r. pillai
'Date  : 19/01/2024
'Rights: All Rights(c) Reserved by www.msaccesstips.com
'------------------------------------------------------

Public Property Get OnAfterUpdate() As String
 OnAfterUpdate = After_Update
End Property

Public Property Let OnAfterUpdate(ByVal vNewValue As String)
 After_Update = vNewValue
 
 Call AfterUpdateMac
End Property

Private Sub AfterUpdateMac()
'Evaluate the given option
'and Run the Event action
Dim opt As String
Dim vx As Variant

On Error GoTo AfterUpdateMac_Err

opt = Nz(After_Update, "")

If Len(opt) = 0 Then
    Exit Sub
ElseIf UCase(opt) = "[EVENT PROCEDURE]" Then
    'RaiseEvent: Call Event Subroutine
    RaiseEvent AfterUpdat("RaiseEvent MESSAGE TEXT")
ElseIf Left(opt, 1) = "=" Then
    'Expression
    opt = Mid(opt, 2)
    vx = Eval(opt)
Else
    'Run Macro
    DoCmd.RunMacro opt
End If

AfterUpdateMac_Exit:
Exit Sub

AfterUpdateMac_Err:
MsgBox Err & ": " & Err.Description, , "AfterUpdateMac_Err()"
Resume AfterUpdateMac_Exit
End Sub

Review of the Class Module Code.

In the Global declaration area, an Event is defined with the name AfterUpdat(ByVal txt As String). The letter e in AfterUpdate is omitted intentionally. Another Property After_Update As String is also declared for inserting the Event running option, analogous to the AfterUpdate Property of the TextBox.

Then the Get/Let Property Procedures to get the selected option from the Form and pass it on to the AfterUpdateMac() to execute the Option. The AfterUpdateMac() is trying to Mimic the action of the AfterUpdateMacro hidden Property/Procedure of the TextBox we saw in the Object Browser Image given at the top of this Page.

The options that we can normally insert into an Event Property are given in a ListBox.

A Macro with the name Macro1
The Function/Expression =DisplayText() to call the Function in the Standard Module
The String [Event Procedure] to call the declared Event Subroutine in the Form Module.
Other built-in Functions like MsgBox(), and InputBox().

By clicking on an option, you effectively insert it into the After_Update Property and activate a process within the Class Module that evaluates and executes the chosen option.

The AfterUpdateMac() Subroutine VBA Code.

Let us have a closer look at the AfterUpdateMac() Subroutine Code.

Private Sub AfterUpdateMac()
'Evaluate the given option
'and Run the Event action
Dim opt As String
Dim vx As Variant

On Error GoTo AfterUpdateMac_Err

opt = Nz(After_Update, "")

If Len(opt) = 0 Then
    Exit Sub
ElseIf UCase(opt) = "[EVENT PROCEDURE]" Then
    'RaiseEvent: Call Event Subroutine
    RaiseEvent AfterUpdat("RaiseEvent MESSAGE TEXT")
ElseIf Left(opt, 1) = "=" Then
    'Expression
    opt = Mid(opt, 2)
    vx = Eval(opt)
Else
    'Run Macro
    DoCmd.RunMacro opt
End If

AfterUpdateMac_Exit:
Exit Sub

AfterUpdateMac_Err:
MsgBox Err & ": " & Err.Description, , "AfterUpdateMac_Err()"
Resume AfterUpdateMac_Exit
End Sub

Within the subroutine, two local variables, opt and vx are declared. The selected option, inserted into the Text2 TextBox Control on the form, is assigned to the After_Update Property declared in the global area of the Class Module. The statement opt = Nz(After_Update, "") checks whether the After_Update Property contains any value. If it does not, the subroutine gracefully exits.

Should the received value be the text [Event Procedure], the Subroutine proceeds to trigger the user-defined Event AfterUpdat() with some sample text parameter. This event is captured in the Form Module, subsequently displaying the parameter text in a MessageBox.

In the scenario where the opt variable contains an expression (Note: an expression commences with an '=' symbol), a check is made for the presence of the equal symbol as the first character. If detected, it is presumed to be a function or a valid expression. The expression is then passed to the Eval() function after removing the '=' symbol.

If the value received in the After_Update Property doesn't satisfy any of the aforementioned criteria, it is assumed to be a macro name. Subsequently, the macro is executed using the DoCmd.RunMacro command.

If any Error is encountered then it shows an Error Message and will exit from the Program.

The Form Module Code is listed below:

Option Compare Database
Option Explicit

Private WithEvents C1 As ClsAfterUpdateMacro

Private Sub Form_Load()
Set C1 = New ClsAfterUpdateMacro
End Sub

Private Sub cmdClose_Click()
DoCmd.Close
End Sub

Private Sub List0_Click()
    Me.Text2.Value = List0
    C1.OnAfterUpdate = Me![Text2]
End Sub

'UserDefined Event Message
Private Sub C1_AfterUpdat(ByVal otxt As String)
    MsgBox otxt
End Sub

'This is the Normal Procedure
'Executed by Access System.
Private Sub Text27_AfterUpdate()
    MsgBox "AfterUpdate Event Subroutine Fired."
End Sub

The Form Module Code Review.

The ClsAfterUpdateMacro Class is declared with the Object name C1 in the Global declaration area.

In the Form_Load() Event Subroutine the C1 Object is instantiated and loaded into memory.

In the List0_Click() Event Procedure the selected ListBox option is assigned to the After_Update Property through the C1.OnAfterUpdate Property Procedure.

The following code segment represents the subsequent subroutine that captures the AfterUpdateMac() Event when triggered from the Class Module using the RaiseEvent action, specifically when the option selected from the ListBox is [Event Procedure].

'UserDefined Event Subroutine
Private Sub C1_AfterUpdat(ByVal otxt As String)
    MsgBox otxt
End Sub

This is used for the second part of this experiment for the Normal Form Module Coding and Event firing from the Access System.

'This is the Normal Procedure
'Executed by Access System.
Private Sub Text27_AfterUpdate()
    MsgBox "AfterUpdate Event Subroutine Fired."
End Sub

The Second Part of the Form.

In the Second part of the Form, the same set of Options is typed in a Label Control so that when you are on the Form Design View you can highlight and Copy the required option from the Label Control and Paste it into the AfterUpdate Event Property of the Text27 TextBox Control. This is easier than typing them correctly in the AfterUpdate Property, without errors.

Then save the Form and open it in Normal View.

Type at least one character in the TextBox and Press Enter-Key to fire the AfterUpdate Event for the option inserted into the Property of TextBox Text27.

The AfterUpdate() Event Fires at this point, depending on the option given in the Property, and executes the action as we saw it in our own earlier experiment.

Event Properties and their related Macros.

As depicted in the Object Browser image provided above, the left panel highlights the CommandButton Class selection, while the right panel showcases its event properties, including the concealed ones. Notably, all event option-setting properties are presented with an "On" prefix appended to the event name, such as OnClick. Beneath each of these properties, there exists another property or procedure bearing the same name, suffixed with the term "Macro," such as OnClickMacro. This signifies the internal program responsible for detecting the option entered within the OnClick property and subsequently executing the specified option.

It's highly plausible that these additional procedures attached to the event properties, matching their names with the "Macro" suffix, encapsulate a code structure similar to the one we experimented with in the initial section. The presence of analogous naming conventions suggests a consistent pattern in the internal program's structure, further affirming the feasibility of such a code structure.

The Demo Database with the above VBA Code and Form is attached for running your own experiments.

0_RaiseEvent2.zip