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 keywords: 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 using Event-related Keywords and crafting Event Subroutines within Standalone Class Modules rather than in the Form Module. Notably, the Event and WithEvents keywords were prominently featured in the Object Browser, as illustrated below:

Right-click to open Large Image in New Tab/Window.

However, RaiseEvent is an internal event-firing mechanism that supports multiple options via a dedicated event-related property. This Event is invoked from the Class Object functions 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 Object Event-firing mechanism is an internal program in the Access System. For instance, the AfterUpdate Event encompasses an AfterUpdateMacro, typically kept hidden from the Object Browser Window. To reveal it, one can simply right-click and select the 'Show Hidden Members' option. 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 given 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 in the Event mechanism. This endeavor aims to replicate the mechanism by which the event 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.

We require only one instance of the TextBox control and one instance each of two Command Button controls, each with a different name, in the intermediary class module. All three object instances are declared using the `WithEvents` keyword, enabling their event procedures to be implemented within the `Card_ObjInit` class.

In this scenario, adopting object-level wrapper classes would likely result in a larger VBA codebase for event handling. Following the recommended design guidelines would require wrapper classes, particularly for the Command Button controls. In addition, a `Collection` object would be necessary to maintain wrapper class instances in memory, ensuring that their event procedures remain available and are executed correctly.

However, given the simplicity of the form interface and the limited number of events to be managed, this approach may introduce unnecessary complexity and resource overhead. If the streamlined solution implemented in the intermediary class module is sufficient to handle these events, it provides a more efficient implementation. The choice should therefore be based on adherence to design guidelines with the practical resource requirements of the specific form.

Accordingly, in this case, we chose to manage all event handling directly within the `Card_ObjInit` class module.

The Event Running Form Image is given below:

Right-click to open Large Image in New Tab/Window.

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 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.

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

The event-running Subroutine, AfterUpdateMac(), is written in 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 to avoid misreading it with the AfterUpdate() System Event Procedure. 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 and Let Property Procedures are used to get the selected option from the Form and pass it on to the 'AfterUpdateMacro()' 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 an option, you 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.

If the received value is the text [Event Procedure], the user-defined AfterUpdate() is called with some sample text parameter. This event is captured in the Form Module, subsequently displays 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, it shows an Error Message and exits 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 in 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 the 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 in the Property, and executes the action as we saw it in our own earlier experiment.

Event Properties and their related Macros.

Right-click to open Large Image in New Tab/Window.

As illustrated in the Object Browser image shown above, the left pane displays the `CommandButton` class selection, while the right pane lists its event-related properties, including the hidden ones. Notably, all event option-setting properties are prefixed with On followed by the event name, such as `OnClick`. Associated with each of these properties is another property or procedure with the same event name, suffixed with Macro, such as `OnClickMacro`. This suggests the presence of an internal routine that evaluates the option specified in the `OnClick` property and then executes the corresponding action.

It is highly likely that these additional procedures associated with the event properties, identified by the Macro suffix, encapsulate logic similar to the code structure we explored in the initial section. The consistent naming convention suggests a standardized internal implementation, reinforcing the plausibility of such an underlying code structure.

The Demo Database is attached for your own experiments and learning.

0_RaiseEvent2.zip