April 2023 ~ LEARN MS-ACCESS TIPS AND TRICKS

Defining Custom Events in Microsoft Access.

I hope you enjoyed last week's article and the demonstration on user-defined custom events in an Access Form's Class Module. We explored how a custom event can be defined and raised within one form and then captured in another form's Class Module to execute the corresponding event procedure. You may still have questions about how this mechanism works. In the coming weeks, we will examine it in greater detail to develop a clearer understanding of the underlying concepts.

When designing a Microsoft Access form, we typically add controls such as TextBoxes, CommandButtons, and ComboBoxes. Each control provides several built-in events triggered by specific user actions or system-generated operations. These events are handled by their associated event procedures, where we write VBA code to perform the required tasks.

Traditionally, we have built Forms by adding controls, relying on their built-in events, and writing VBA code to perform tasks—often without considering how the underlying event mechanism works. Over time, this developmental VBA coding style becomes second nature: add a control, assign an event procedure, write the required code in the Form Module, and repeat the same for other Events.

When discussing custom events in Microsoft Access, three VBA keywords are fundamental: Event, RaiseEvent, and WithEvents. The Event keyword declares a custom event, RaiseEvent triggers that event, and WithEvents enables another object to receive and respond to it through an event procedure. These three keywords form the foundation of the VBA custom event model, allowing events to be defined, raised, and handled. You will see each of these keywords used throughout the examples in this article and explored in greater depth in the articles that follow.

Defines an event in the Form1 Class Module using the keyword Event: Public Event QtyUpdate(). This is the conventional method for defining Access events.
Fire the Event with the Keyword RaiseEvent: RaiseEvent QtyUpdate() in the Form1 Class Module.
To capture the event in the Form2 class module, we first need to create an instance of the Form1 class module using the declaration:
```
Public WithEvents ofrm As Form_Form1
```
One key point to remember: a user-defined event can’t be captured within the same Form where it’s defined. That’s because the event only becomes “catchable” when the Form is instantiated as an object using the keyword WithEvents. In other words, the fired Event on Form1 can only be captured and handled by another Form (like Form2) that’s set up to listen for the Event-firing action.
The resulting Event Procedure for the 'ofrm_QtyUpdate()' Event must be written in the target Form Module. The ofrm_ prefix to the Subroutine name is taken from the Event Capturing declaration: Private WithEvents ofrm As Form_Form1.
```
Private Sub ofrm_QtyUpdate()
   'VBA Code
End Sub
```

User-Defined Event Example-2.

For a clearer insight into these three keywords, let us examine another example that demonstrates the use of user-defined events. Suppose we have a TextBox that allows users to enter a quantity subject to specific validation rules. Our objective is to pass the entered value to another form via a user-defined event, validate it there, and return an appropriate validation message to the user.

To accomplish this, we first declare a Public Event named `QtyUpdate()` by using the Event keyword. We then trigger the event with the RaiseEvent keyword, passing the entered quantity as an argument. In another open Form, the event is captured through an object declaration qualified with the keyword WithEvents in the Form's Class Module. The event procedure receives the argument, validates it according to the required criteria, and displays an appropriate message based on the validation result.

To demonstrate this process, we will create two simple Forms. The first Form1 will define and raise the custom event, while the second Form2 will capture the event and execute the corresponding event procedure within its Class Module.

In brief, the custom event is declared and raised in the first Form1. The event raised by the first form is captured by the object declared with the WithEvents keyword in the second Form2's Class Module, where the associated event procedure is executed.

In other words, the Object declared with Events using the keywords Event & RaiseEvent (in Form1) is captured in the parent Class Module (of Form2), where Form1 is instantiated with the object name ofrm, qualified with the keyword WithEvents.

Once you understand this event-handling model, you can begin moving business logic that is traditionally written directly in a Form Module into dedicated Class Modules. This design enables the Form Module to focus primarily on user interface functionality, while the Class Modules encapsulate the application's processing logic. Adopting this structured approach improves code organization, promotes reusability and maintainability, and can significantly reduce development time for larger Microsoft Access applications.

Let us try something very simple to understand the concept a little more.

Open your Database
Create a new Form
Insert a Textbox
Display the Property Sheet of the Textbox.
Change the Name Property value to OrderQty (Qty stands for Quantity).
Change the child Label Caption value to: Quantity (1 - 5 only).
Insert a CommandButton below the Textbox and change its Name Property value to cmdClose.
Change its Caption property to 'Close Form'.
Add another CommandButton below the earlier one, and change its Name Property Value to cmdOpen.
Change the Caption Property value to 'Open Event Capture Form'
Display the Form's Property Sheet. Set the Record Selectors Property Value to No.
Set Navigation Buttons to No.
Set Scroll Bars to Neither.
Right-click on the TextBox and select Properties from the displayed Menu.

Right-click to open the Large Image in a New Tab/Window.
Select the AfterUpdate [Event Procedure] option on the TextBox Property, and click the Build (...) Button to open the VBA Module.

Copy and paste the following VBA Code into the Form's Module, overwriting existing lines, if any:

Option Compare Database
Option Explicit

'User-Defined Events
'will Capture in Second Form Module.

Public Event QtyUpdate(mqty As Single)
Public Event formClose(txt As String)

Private Sub cmdOpen_Click()
    DoCmd.OpenForm "frmUDCapture", acNormal
End Sub

Private Sub OrderQty_AfterUpdate()

'Announce the Event and pass the OrderQty as Prameter
  RaiseEvent QtyUpdate(Me!OrderQty)
  
End Sub

Private Sub cmdClose_Click()
'Announce the Event and pass the Text as Parameter
  RaiseEvent formClose("Close the Form?")
End Sub

Private Sub Form_Unload(Cancel As Integer)
  DoCmd.Close acForm, "frmUDCapture"
End Sub

Save the Form with the Name frmUserDefined.

User-Defined Events.

In the global declaration area of the Form's class module, we have defined two user-defined events that closely resemble functions.

Public Event QtyUpdate(mqty As Single)
Public Event formClose(txt As String)

Access has built-in events that also look like functions, such as Text0_Exit(Cancel As Integer), Form_Unload(Cancel As Integer), and Form_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single).

The user-defined Event declarations both begin with the keyword Public Event, followed by the event name and any associated parameters enclosed in parentheses. It is important to note that the event name should not contain an underscore character (e.g., Public Event Qty_Update()). Additionally, the declaration must have Public scope.

The first user-defined event validates the contents of the OrderQty TextBox whenever it is updated on the Form. The second event (RaiseEvent formClose()) is triggered by the first CommandButton click to close the Form and passes a text message as a parameter.

After entering a numeric value into the OrderQty TextBox and pressing the Enter key, the OrderQty_AfterUpdate event procedure is executed as usual. In the event procedure, the RaiseEvent QtyUpdate(Me!OrderQty) statement is called; the first user-defined event is raised (fired), passing the OrderQty TextBox value as the parameter.

Private Sub OrderQty_AfterUpdate()
'RaiseEvent and pass the OrderQty as a parameter
  RaiseEvent QtyUpdate(Me!OrderQty)
  
End Sub

In Microsoft Access, event procedures for controls such as Text Boxes and CommandButtons are typically written directly within the Form Module. These controls are MS Access Standalone Class Objects, with their built-in Events declared internally. When a control's data needs to pass through certain checks and balances, its required event is raised to execute the task written in the Form Module.

When you add a TextBox object in the Form, Access qualifies the Control with the Keyword WithEvents. Conceptually, this is equivalent to a declaration such as `WithEvents OrderQty As TextBox`. This enables the TextBox to capture the event raised by the TextBox Object and execute the corresponding event procedures written in the Form Module. Check the Object Browser Image given below to see how the OrderQty TextBox declaration appears in the bottom panel of the image.

Right-click to open the large image in a new tab or window.

Later, we will examine this built-in event mechanism in greater detail and compare it with the implementation of user-defined events. Both approaches rely on the same underlying Event model concept in setting up and handling Events in an Access Class Object. Here, Form1 acts as an events-enabled Class Object, similar to a TextBox added to Form2 (WithEvents frm As Form_Form1)

For example, when a `Form_Form1` object is declared in the `Form2` Class Module using the WithEvents keyword:

Public WithEvents ofrm As Form_Form1

Any custom Events declared and raised within the `Form1` Class Module can be captured and processed by event procedures written in the `Form2` Class Module. The same principle applies when the event source is a standalone Class Module instead of `Form1`. This behavior is made possible by the WithEvents keyword, which enables the object variable to receive notifications whenever the referenced class raises an event, allowing the appropriate event procedure to execute automatically.

User-defined events are fundamentally different from the built-in events of Access controls, such as those provided by a TextBox. Unlike built-in control events, user-defined events are not associated with any specific object or user action. They are raised only when the programmer explicitly executes a `RaiseEvent` statement, as demonstrated in the preceding example.

Similarly, a user-defined event declaration doesn't have built-in event functionality. It simply defines the event name, specifies any required parameters, and determines the event's accessibility. Implementing both the code that raises the event and the code that handles it rests entirely with the developer.

A user-defined event can be declared and raised only within the Form Class Module or within a standalone Class Module. To receive and process the event in another module, that module must declare an instance of the event source by using the WithEvents keyword. This enables the receiving module to capture the event and execute the corresponding event procedure whenever the event is raised.

In our example, the `RaiseEvent` statement within the `OrderQty_AfterUpdate()` event procedure raises the `QtyUpdate()` user-defined event and passes the value of the `OrderQty` control as an argument. The `QtyUpdate()` Event is then captured by the second Form's Class Module through its WithEvents object declaration. The corresponding event procedure validates `OrderQty` and displays an appropriate message.

The cmdClose_Click() event procedure in the frmUserDefined Form raises the formClose() event with a message text as the parameter. Then the second Form's Module captures this event and displays a message asking for permission to close frmUserDefined.

This is a simple example of how user-defined events communicate between different Forms or Class Modules in VBA.

Private Sub cmdClose_Click()
'Announce the Event and pass the Text as Parameter
  RaiseEvent formClose("Close the Form?")
End Sub

Private Sub Form_Unload(Cancel As Integer)
  DoCmd.Close acForm, "frmUDCapture"
End Sub

The Form_Unload() event of the frmUserDefined form is triggered first, as the form is closed, and the frmUDCapture Form is closed first.

The Form_Close() event is triggered next after the frmUDCapture form is closed, and closes the frmUserDefined Form in the cmdClose_Click() event procedure.

The User-Defined Event Capturing Form.

Create a new Form and open it in Design View.
Insert two labels as shown in the Image of the Form's design view given below.

Right-click to open the large image in a new window.
Change the first label caption to EVENT MESSAGES.
Select the second label and display its Property Sheet.
Change the Name Property Value to Label2 if it is different.
Change the Caption Property Value to Event.
Display the Form's Property Sheet. Set the Record Selectors Property Value to No.
Set Navigation Buttons to No.
Set Scroll Bars to Neither.
Change the Detail Section Area of the Form to the same size as shown in the above Image.
Display the Form's VBA Module.

Copy the following VBA Code and paste it, overwriting existing lines in the Module.

Option Compare Database
Option Explicit

Public WithEvents ofrm As Form_frmUserDefined

Private Sub Form_Load()
On Error Resume Next
Set ofrm = Forms("frmUserDefined")End Sub

Private Sub ofrm_QtyUpdate(sQty As Single)
Dim Msg As String
  If Nz(sQty, 0) < 1 Or Nz(sQty, 0) > 5 Then
     Msg = "Valid Qty Range: 1 - 5 Only"
  Else
     Msg = "Order Qty: " & sQty & " Approved."
  End If
  
  Me.Label2.Caption = Msg
  
  MsgBox Msg, vbInformation, "QtyUpdate()"

End Sub

Private Sub ofrm_formClose(txt As String)
Me.Label2.Caption = txt

If MsgBox(txt, vbYesNo, "FormClose()") = vbYes Then
  DoCmd.Close acForm, ofrm.Name
Else
  Me.Label2.Caption = "Close Action = Cancelled."
End If

End Sub

Save the Form as frmUDCapture and close it.

Event Capturing Form Module VBA Code Review.

Public WithEvents ofrm As Form_frmUserDefined

Private Sub Form_Load()
	On Error Resume Next
	Set ofrm = Forms("frmUserDefined")
End Sub

The line of code Public WithEvents ofrm As Form_frmUserDefined is a crucial global declaration in the second Form's Class Module. It declares a new instance of the first Form, frmUserDefined, as an Event Listener Object variable named ofrm.

This is important because it enables the second Form Module to capture and respond to events that are raised by the first Form. By the above declaration, the Listener Object in the second Form can intercept and handle user-defined events raised by frmUserDefined.

Without this declaration, the second Form would be unable to capture and respond to events raised by the first Form, and the user-defined event functionality would not work as intended.

To make a long story short, we are declaring an Instance of the Form frmUserDefined Class Module Object Variable named ofrm. The Form_ prefix of the Form Name indicates that we are referring to the Form's Class Module and will create an Instance of it.
The first Form's Class Module Object Instance will be assigned to the declared ofrm Object Variable through the frmUDCapture Form's Form_Load() Event Procedure.

The WithEvents keyword declares the ofrm as a Listener Object to capture the Events Raised from its original Class Module-based user-defined Events.
Note: Remember, the WithEvents keyword is used only when an instance of any Object similar to Form, TextBox, ComboBox, etc., is created.
The general rule of Object Instance creation as a Listener Property is that:
- The WithEvents keyword is used in the parent Class Module to connect the object to its events. This connection allows the Class Module to capture events fired by the object and call the corresponding event procedure in response.
  
  The Event Subroutine name must start with the object instance name, followed by an underscore, and then the event name. This naming convention ensures that Access can properly map the event to the correct event procedure.
  
  For example, if we have a TextBox object Instance named "txtName" and we want to capture its LostFocus event, we would declare it in the parent Class Module like this:
```
Private WithEvents txtName As Access.TextBox

Private Sub txtName_LostFocus()
    ' Do something when the txtName TextBox loses focus
End Sub
```
  In this example, the object instance name "txtName" is prefixed to the event name "LostFocus" to create the event procedure name "txtName_LostFocus".
- To capture the user-defined event QtyUpdate() from the frmUserDefined form module, the parent form module instance should be declared with the WithEvents keyword. This allows the frmUDCapture form to listen to the Event transmission from the frmUserDefined form and execute the corresponding event procedure.
- Note: When you add a TextBox (or any other Access Object) to a form, the Access system creates an instance of the TextBox object and adds it as a control to the form. The Name property is assigned a default name, followed by the text "As TextBox" as the class name. For example, if the default name of the TextBox is "Text0", then the TextBox Control's Property declaration on the Form will be:
```
Private WithEvents Text0 As TextBox
```
  You can change the TextBox's Name Property Value Text0 to something more meaningful, like Quantity. This declaration is not directly visible to us except for the Name Property Value. But it can be displayed through the Object Browser.
- Check the last two lines at the bottom of the Object Browser image below and see how the TextBox Object Instance name OrderQty declaration appears on our Form frmUserDefined. Check the declaration starting with the Keyword WithEvents OrderQty As TextBox.
  
  Right-click to open the large image in a new tab or window.
  
  To inspect the properties of the TextBox in the Object Browser or any other control on the Form, you can follow these steps:
  1. Right-click on the VBA Editing Window and select the Object Browser Option, or click the Object Browser button from the Toolbar above to Open the Object Browser.
  2. In the Object Browser, select the Database Name in the <All Libraries> Control.
  3. The Object Browser will display the Properties and Methods of the selected control, including the declaration for the control's Object Instance.
  4. Select the Form Name Form_frmUserDefined from the left panel of the Browser Window.
  5. The Form's Property List appears in alphabetical order in the right-side Panel.
  6. Scroll down to find the OrderQty Property of the TextBox and select it.
  7. For example, the declaration for a TextBox with the name OrderQty on a form named frmUserDefined would look like this:
```
WithEvents OrderQty As TextBox
```
    At the bottom panel of the Object Browser in the detail view of the selected Property.
  - Explore the user-defined Event QtyUpdate() declaration, and how it is displayed in the right panel with a lightning icon, and check the details view of the event declaration in the bottom panel.
  - Both user-defined Events and built-in events of Access objects require an event procedure to be created in their parent VBA module. The Event name has two parts: 1) the Object Name, 2) the Event Name, both joined with an underscore character. For example, if the Access object is a TextBox named "Text0" and the Event is "LostFocus", the Event Procedure name will be "Sub Text0_LostFocus()".
    
    The User-defined Event Subroutine name consists of two parts. The first part is the Event Subroutine name Prefix, and the second part is the Event Name. The first part is the First Form Module's Instance name declared with the WithEvents keyword in the second form Module - objFrm. The second part is QtyUpdate, like objFrm_QtyUpdate(); both parts are separated by an underscore character.
  - The LostFocus event is an inbuilt event of the TextBox control in Access. When you add a TextBox control to a form, Access creates an instance of the TextBox object and assigns it a default name such as Text0. This TextBox instance is then declared as a property of the form, with the WithEvents keyword, which allows it to capture and handle its inherent events.
    
    As mentioned earlier, the `Text0` control is contained within the Form object, and its built-in events are handled by procedures in the Form's Class Module. Consequently, the code that responds to the `LostFocus` event—or any other built-in control event—must be written in the Form's Class Module.
    
    Microsoft Access follows a standard naming convention for control event procedures. The procedure name consists of the control instance name, followed by an underscore (`_`), and then the event name. For example, if the Text Box control is named `Text0`, the event procedure that handles its `LostFocus` event must be declared as:
```
Private Sub Text0_LostFocus()

End Sub
```
    Several TextBox object instances can be created in the same Form with appropriate names, and their Event Subroutines are written in the same Form's Class Module with the TextBox names as the prefix. This is what we normally do in the Form Module.
  - The QtyUpdate() event is a User-defined event and is not part of any built-in object. It is defined in the Class Module of the frmUserDefined Form, and the parent object of the User-defined event is the form's Class Module.
    
    In the frmUDCapture form, we have declared an instance of the frmUserDefined form as a listener parent object using the WithEvents keyword. Since the Form Class Module instance is named "ofrm", the event procedure for the QtyUpdate() event will have the format "Sub ofrm_QtyUpdate()". This allows the frmUDCapture form to capture and respond to the QtyUpdate() event raised by the frmUserDefined form.
  The statement Set ofrm = Forms("frmUserDefined") is within the Form_Load() Event Procedure of the frmUDCaptured Form Module, and if the Form is in the open state, then it assigns to the ofrm Class Module object. If this Form is not open, then it will run into an Error. The On Error Resume Next statement will ignore the error message, but the Event capturing will not work as expected.
  Ensure that the frmUserDefined form opens first, then the second form, frmUDCapture, next.
- We enter a Numeric Quantity Value into the OrderQty Textbox on the UserDefined Form and press the Enter Key to complete the data entry. The RaiseEvent QtyUpdate(Me!OrderQty) is fired from within the OrderQty_AfterUpdate() Event Procedure.
- This Event is captured in the frmUDCapture Form's Event Procedure Subroutine Code given below:
```
Private Sub ofrm_QtyUpdate(sQty As Single)
Dim Msg As String

  If Nz(sQty, 0) < 1 Or Nz(sQty, 0) > 5 Then
     Msg = "Valid Qty Range: 1 - 5 Only"
  Else
     Msg = "Order Qty: " & sQty & " Approved."
  End If
  
  Me.Label1.Caption = Msg
  
  MsgBox Msg, vbInformation, "QtyUpdate()"

End Sub
```
- The Valid value Range accepted in the TextBox is 1 to 5 only. Normally, we write this validation check procedure in the same Form Module Event Subroutine. Here, we perform the validation check in the second Form Module, and an appropriate message is saved into the Msg String Variable and displayed in the MsgBox. It updates the same message text in the Label control Caption Property on the second Form to view the last message after closing the Message Box.
- Check the Event Subroutine line: Private Sub ofrm_QtyUpdate(sQty As Single)
  The frmUserDefined Form's Class Module Object Instance name ofrm is used as the Subroutine name prefix, combined with the User-Defined Event name QtyUpdate(), both separated by an underscore character.
- Similarly, the FormClose() Event is captured by the Private Sub 'ofrm_formClose (txt As String)' Event Procedure and displays the message Text that was passed as a parameter to the User to respond, whether to close the Form frmUserDefined (first form) now or not. If the response is Yes, then it closes the First Form.
```
Private Sub ofrm_formClose(txt As String)
Me.Label2.Caption = txt

If MsgBox(txt, vbYesNo, "FormClose()") = vbYes Then
  DoCmd.Close acForm, ofrm.Name
Else
  Me.Label2.Caption = "Close Action = Cancelled."
End If

End Sub 
```
- In the formClose() Event Procedure header line, you can see that the ofrm object prefix is appearing. In this subroutine, the parameter text is displayed in the message box, and the User's asked whether to close the frmUserDefined Form (the first Form). The Message text is displayed from the second Form's Event Procedure. If the response is Yes, then it runs the Code to close the first Form.
  
  When we issue the DoCmd.OpenForm "FormName" Command: a series of Events takes place in a certain order: Form_Open, Form_Load, Form_Current, in this predefined sequence. Similarly, when we run DoCmd.Close acForm, the "FormName" Form_Unload Event runs first, before the Form_Close event fires, if both events are run in VBA.
- When the Form_Close() Event Procedure is called to close the first Form, the Form_Unload() event fires first (if this Event Code is present in the form Module) and closes the second form, 'frmUDCapture', before closing the first Form, frmUserDefined.

The User-Defined Events Trajectory View.

Right-click to open the large image in a new tab or window.

From the above graphical image, we can easily see at a glance how the user-defined Event is declared with the Event Keyword, how the Events are announced with the RaiseEvent Keyword, and how the Event Listener declaration is done with the WithEvents keyword that captures the announced Event, identifies it with the Event Procedure, and executes the correct Event Procedure Code.

Once we understand how Events and Event Procedures work behind the scenes, it becomes clear that an event procedure of a TextBox control on the form can actually be written in another form’s class module—something we already demonstrated in the example above. The same approach can be applied using a Form module together with one or more standalone class modules, giving us even more flexibility in how we structure our code.

But before we explore the tricks at that level, we need to know a few basics of Event handling on Forms. How could we do things differently on a Form, other than the traditional method we follow now?

Once we know how these tricks work together, our aim is to move all the VBA code out of the Form Module and organize it in a way that makes coding very easy. We will use the Forms for User Interface design only.

In addition, once the existing Form Module coding procedures are streamlined into standalone Class Modules, the Event Procedure VBA code can be managed independently, without repeatedly opening the Form in Design View to write or modify code. Furthermore, repetitive code—for example, highlighting the background of active TextBoxes—can be handled through a single event procedure for all TextBoxes on a form, rather than duplicating the same code for each TextBox separately.

A Form with several types of Controls, like TextBoxes, Command Buttons, and frequently used other controls, and their various Event Procedures of several types are all mixed up and stored in the Form Module together. We will not be able to use any part of it for any other Project.

Download Demo Database

UserDefined2.zip

Streamlining Form Module Code in Standalone Class Module.

Existing Demo Databases Converted to the new Coding

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