LEARN MS-ACCESS TIPS AND TRICKS

Objects and Built-in Events Triggering - Part Three

a.p.r. pillaiMay 12, 2023 No comments

Objects and Built-in Events

This is the third article on this topic, Streamlining Form Module Code. Assuming your familiarity with the previously discussed topic, "Defining Custom Events in Microsoft Access," readers are expected to have a foundational understanding of our trajectory. Specifically, we have delved into the concepts of Event, RaiseEvent, and 'WithEvents' declarations, elucidating their interplay and collective functionality.

The primary objective is to move VBA code from the Form Module to a standalone Class Module, reserving the Form exclusively for user interface design. Is this feasible? Absolutely. Once this structured coding approach is implemented, significant portions of the code can be reused across new projects, simplifying development and reducing coding effort and code management.

With this coding methodology, Code Modules can be opened and edited independently, eliminating the need to repeatedly switch to Form Design View simply to access the code. This saves considerable manual effort and improves development efficiency, resulting in faster project completion. The proposed solution will continue to work seamlessly with the controls on the Form, preserving the standard behavior from the end user's perspective. In addition, the underlying structured coding framework can be exported and incorporated into other projects, making code reuse and maintenance significantly easier.

But first, I think it’s worth looking at why we’re always directed to write code in the Form Module. Once we understand that, it becomes easier to break away from that pattern, implement procedures as easily as we want, and reuse them across other projects.

To achieve that goal, we need a streamlined, structured, and independent coding procedure outside the Form Module that Access Developers understand and use routinely. Before doing that, it is important to understand how the existing coding method works in the Form Module and what tricks are hidden behind the Form that force us to code in the Form Module itself.

The earlier Article Links:

Why are the Event Procedures in Microsoft Access always written directly in the Form Module? Have you ever wondered how events are raised and how the associated VBA code is executed when an event occurs?

Exploring these concepts provides valuable insight into the underlying mechanism of event-driven programming. Once you understand how this process works and become familiar with the fundamental rules that govern it, you can begin refining existing procedures and implementing them in a more structured, efficient, and well-organized manner.

What happens when we add a control to the Form? How does the code we write in the Event Procedure get executed when an event occurs? Is there an alternative approach that gives us the same result and ease of Coding?

Objects And Their Built-in Events.

Let us examine what happens when a TextBox control is added to a form. The following diagram illustrates the TextBox and all associated components and relationships that are essential to understanding how it functions.

Right-click to open the Large Image in a New Tab or Window.

When designing a form in MS Access, we often create several instances of TextBox objects, along with other native controls: Command Buttons and ComboBoxes. Each of these is actually a Class Module object. Every time you add a TextBox to a form or report, Access automatically defines the new instance of that object internally as something like WithEvents Text0 As TextBox. (If you look at the diagram above, check the label shown with a dark background and grey text.)

Of course, we don’t have to keep the default name. We can change Text0 to something more meaningful, like Quantity. When events are fired from within this object instance, they’re captured by the parent Form or Report Class Module, where we can write the corresponding event procedure. This parent–child relationship between the object instance and its Form/Report is important for understanding how event procedures really work.

Two selected Properties of the Textbox are shown in the diagram above: Change Event and 'On Change' properties. The Change Event Property will not appear in the Property Sheet of the Textbox object. We have seen how an Event Property declaration is in our User-defined Event examples in earlier articles.

The 'On Change' Event Property (with String Type data) only appears in the Property Sheet of the Textbox. When we select the text [Event Procedure] option from the drop-down control in this Property, we can write the Event Procedure Code in the Form Module for the intended task of the Change Event. Therefore, we assume that this property invokes the RaiseEvent (Announcer) action for the Event Change.

Whenever we type some text in the msg Textbox, the Change Event fires for each character typed (or triggers the Subroutine, with the Event name Change and the parent object name msg as Prefix - Sub msg_Change()).

Private Sub Msg_Change()
'Announce/Transmit the Event
    RaiseEvent Message(Me!Msg.Text)
End Sub

The Event and the WithEvents Property declarations are not normally shown on the Property Sheet of any Object Instance created within the Form, except the Name Property. The WithEvents declaration goes with the Name Property only when an object instance is added to the Form.

Text0 object's built-in event procedures run with its Parent Object name (Text0) as the event name prefix, and the Event Procedure is written in the Form Module; in other words, the Event Procedure Code must be written in the Parent Class Module of the TextBox Object Instance.

Both Form and Report Objects are Class Module Objects. So when we visualise the hierarchy of objects and Events, the Form is the top-level container for TextBox and other Objects on the Form. Conversely, the TextBox's Parent Object is the Form Class Module. The TextBox object Instance is the parent object of its own inherent Events: AfterUpdate, Change, and GotFocus, and captures them when they are fired.

TextBox Events are captured by the TextBox Instance.

The Event Procedure name must be written with the Parent Object name as a prefix: Sub Text0_AfterUpdate(). Following the same rule, the Event Procedure must be coded in the TextBox object's Parent Class Module - the Form's Class Module. When the AfterUpdate Event is fired, the TextBox captures it (the TextBox Object Instance's WithEvents declaration enables it to do that), and the Event Procedure, which is written in the TextBox Object's Parent Form's Class Module, executes the Event Procedure-based task.

Here, you can see a pattern forming in the object-event handling mechanism. The relationship between the Event and the parent Object TextBox. The TextBox Instance and the parent Form Class Module.

Right-click to open the Large Image in a New Tab or Window.

Text0 TextBox has its own built-in Event collection. Whenever one of these events is raised (fired) and captured by the parent object Text0. Because the TextBox object Text0 instance is declared with the `WithEvents` keyword on the Form (or wherever the instance is created), the corresponding VBA event procedures must be written in the parent class module of the TextBox object. In this example, the parent TextBox Class Module is the Form's Class Module.

Keeping these rules in mind, let us explore it differently from our earlier experiment involving two Forms and their Form Modules. This time, we will use a Form together with a standalone Class Module, `Class1`.

We will create an instance of the TextBox object in the standalone Class Module (`Class1`) and declare it with the `WithEvents` keyword, like WithEvents Txt As TextBox. Next, we will assign the Reference of the Form's `Text0` TextBox control to this object Txt variable. When an event is fired by `Text0` on the Form, it is captured by the `WithEvents` qualified object Txt in `Class1`, causing the corresponding event procedure to execute in the `Class1` Class Module rather than in the Form's Class Module.

Create a Demo Form.

Let us try an example to put what we learned into a practical experiment to understand it better.

Right-click to open the Large Image in a New Tab or Window.

Create a new Form.
Add two TextBoxes on the Form, one below the other.
Click on the First Text Box and display its Property Sheet.
Change the Name Property value to Quantity.
Change the Caption of the Child Label to Max Quantity (1 - 10).
Select the Quantity control's Property Sheet, select the [Event Procedure] Option in the After Update Event Property, and click the Build (...) Button to open the Form's Class Module.
The Form Module VBA Code.

Copy the following VBA code and paste it into the Form's Class Module, overwriting existing lines.

Option Compare Database
Option Explicit

Private C As Class1 'Declare a Class1 Object Variable

Private Sub Form_Load()
  Set C = New Class1 'Instantiate the Class1 Class Module

  Set C.Txt = Me.Quantity 'Assign Quantity Textbox Object to txt Property
  
End Sub

Private Sub Form_Unload(Cancel As Integer)
    Set C = Nothing
End Sub

Private Sub Quantity_AfterUpdate()
 'Code
End Sub

Private Sub Quantity_GotFocus()
 'Code
End Sub

Private Sub Quantity_LostFocus()
 'Code
End Sub

Save the Form as Form1 and Close the Form.
The Stand-alone Class Module.

Now, we will create a standalone Class Module named Class1.

Open the VBA Editing Window (ALT+F11)
Select Class Module from the Insert Menu.
If the Class Module name is not Class1, click on the Properties Button in the Toolbar above to display the Property Sheet, then change the name to Class1.
Note: If you already have a Class1 Class Module, do not change the Class Module Name; instead, change the Class1 name in the Form Module to match the new stand-alone Class Module Name.
The Class Module VBA Code.

Copy the following VBA Code and paste it into the Class1 Class Module:

Option Compare Database
Option Explicit

Public WithEvents Txt As TextBox

Private Sub txt_AfterUpdate()
Dim i As Integer, msg As String
Dim info As Integer

i = Nz(Txt.Value, 0)
If i < 1 Or i > 10 Then
    msg = "Valid Value Range 1 - 10 Only."
    info = vbCritical
Else
    msg = "Quantity: " & i & " Valid."
    info = vbInformation
End If

MsgBox msg, vbOK + info, "txt_AfterUpdate()"

End Sub

Private Sub txt_GotFocus()
With Txt
    .backcolor = &H20FFFF
    .forecolor = 0
End With
End Sub

Private Sub txt_LostFocus()
With Txt
    .backcolor = &HFFFFFF
    .forecolor = 0
End With
End Sub

Select Save from the File Menu or Click on the Save Toolbar Button.
Select Compile from the Debug Menu to compile the code and ensure there are no errors.
We will do a test run first and see how it works. Take note of this Point: we have selected the [Event Procedure] in the Event Property to add the empty Subroutine Stubs in the Form Module (for the RaiseEvent action) for the Events After Update, Got Focus, and Lost Focus.
Note: When we compile the VBA Code, the System removes the empty Subroutine stubs in the Form Module, and the [Event Procedure] option selected in the Event Properties will be deleted. If that happens, then our idea will not work as planned. To prevent that, we have added a REM line of code in between the empty Event Procedure Stub.

There are other methods for the RaiseEvent action rather than creating empty Subroutine stubs, which we will explore later.

We have written VBA code in the standalone Class Module, `Class1`, to validate the Quantity value entered in the TextBox. The valid range is 1 through 10, and a status message is displayed based on whether the entered value falls within this range.

Examine the code carefully to understand how an event raised by the TextBox control on the Form is captured in the `Class1` Class Module. The captured event is then used to validate the contents of the Quantity TextBox and display the appropriate validation message.

When the Quantity TextBox receives focus, its `GotFocus` event changes the background color of the control to yellow. When the control loses focus, the `LostFocus` event restores the original background color.

The second TextBox serves only as a supporting control to receive focus when the Quantity TextBox triggers the `LostFocus` event.

Now, we are all set.

Open Form1 in Normal View.
You will see the TextBox's BackColor is now Yellow.
Enter Quantity 25 in the first TextBox and press the Enter Key. You will see the Validation Error message "Valid Value Range 1 - 10 only".
The first TextBox's background color is reset.
Now, enter any value from the range 1 to 10 in the Quantity TextBox again.
This time, the message confirms that the Value entered is Valid.

Let us see how this works.

Check the declaration line of the code for the TextBox Control in the Class1 Class Module:

Public WithEvents Txt As TextBox

We’re creating an instance of the TextBox object, declared with the WithEvents keyword and given the object variable name txt. This statement is as good as creating a TextBox on a Form. By defining it with Public scope, this variable acts as a listener object, capable of responding to the events raised by the TextBox.

Note: We cannot drag a TextBox object and place it in the Class1 Class Module as we do normally on the Form. Hence, we explicitly qualify it with the keyword WithEvents and create a TextBox object Instance Txt in the Class1 Class Module.

Class Module Properties are normally declared with Private scope to ensure the integrity of their values, and access to them is allowed through Public Property Procedures. This approach will ensure that the value received through the Property Procedure is valid before assigning it to the Property. But we are on the learning curve.

The rest of the Event Procedure Code is similar to what we normally write in the Form Module. But one thing you might have noticed is that we are not using the original TextBox name, Quantity, as the prefix in the Event Procedure subroutine name: Private Sub txt_AfterUpdate()

Because we are capturing the Quantity TextBox events in the txt TextBox object Instance in the Class Module Class1.

Creating a TextBox object txt, qualifying it with the keyword WithEvents, will not establish any relationship with the Quantity TextBox on the Form to capture its Events. We must assign the Quantity TextBox's reference to the txt object in the Class1 Module.
To do that, the Class1 Class Module must be loaded into memory first. The Quantity TextBox reference on the form must be assigned to the txt object in the Class1 Class Module, which makes it a clone of the Quantity TextBox on the Form. Once this step is done, we can capture the Quantity TextBox Event in the Class1 Class Module. The Class1 Class Module remains in memory till we close Form1. In our earlier examples, we used two open Forms and their Class Modules for our experiments.
This is what we do in the Form's Class Module, in the following Code Segment:

Option Compare Database
Option Explicit

Private C As Class1

Private Sub Form_Load()
  Set C = New Class1
  Set C.Txt = Me.Quantity 
End Sub

Private Sub Form_Unload(Cancel As Integer)
    Set C = Nothing
End Sub

The statement Private C As Class1 declares a variable C of type Class1 in the global declaration area of the Form Module. This is similar to declaring a built-in variable, for example: Dim City As String. However, just like a City variable doesn't hold a value until you assign one (e.g., City = "New York"), the object variable C not loaded into memory upon declaration. To make it active and usable, the object must be instantiated with the New keyword.

In the Form_Load() event Procedure, the statement Set C = New Class. The 'New' Keyword instantiates the Class1 Object in memory.

The next statement, Set C.txt = 'Me.Quantity', assigns the Reference of the Quantity Textbox object on the Form to the Txt Textbox instance in the Class1 Module. The Txt object becomes a replica of the Quantity Textbox on the Form. The Txt Object is declared with the Keyword WithEvents so that when an Event, like AfterUpdate, is fired from the Quantity Textbox object on the Form, it is captured by the Txt object in the Class1 Module and executes the Event Procedure there. To trigger the event-firing action (RaiseEvent), we created an empty AfterUpdate() event procedure stub in the Form Module.

When the Form is closed, the Class1 Class Module Instance in object variable C is cleared from memory.

Next week, we will explore better methods for the RaiseEvent action, without keeping the empty Event Procedure stubs in the Form Module.

Download Demo Database.

Streamline3.zip

Streamlining Form Module Code in Standalone Class Module.

Existing Demo Databases Converted to the new Coding

Defining Custom Events in Microsoft Access - Part Two

a.p.r. pillaiApril 25, 2023 No comments

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

Reusing Form Module VBA Code for New Projects.

a.p.r. pillaiApril 12, 2023 1 comment

Streamlining Form Module Event Procedures in a Standalone Class Module.

The Existing Form Module Coding Approach.

Access Forms include a wide range of controls—such as TextBoxes, CommandButtons, and ComboBoxes—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 TextBox, 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 go through the examples carefully and try them yourself before moving on. Hands-on practice will enhance your understanding.

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

Access Control's Event Procedures on a Form

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

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

For the first two options, the form does not require a Class Module. However, the third option [Event Procedure] selection adds a Class Module to the Form.

What’s interesting is how Access manages this internally: an Event is triggered by 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 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. Besides that, the TextBox instance is qualified with the keyword 'WithEvents'. So the TextBox Class Object's definition on the Form is as WithEvents Text0 As Access.TextBox.

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 and raises 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 subroutine stub would look like this:


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

This mechanism provides a consistent way to integrate VBA code in the Event Procedure stub, ensuring that it executes 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 Procedure that executes a small block of VBA code?

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

The organized, structured coding method has the scope for exporting and reusing Form Module Code across multiple projects, saving development time and simplifying database maintenance.

User-defined Custom Events.

Keywords: Event, RaiseEvent, and WithEvents.

Let us examine how to define our own User-defined Event, trigger the Event, capture it, and run the custom event's procedure code.

Open your Database.
Create a new Form.

Right-click to open the large image in a New Tab or 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 captured correctly.
Change the TextBox's child Label Caption value to Msg:.
Save the Form named Form1 and close the Form.
Create a new Form named Form2 and open it in Design View.
Change the Form size to match Form1 dimensions.
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 Form's Popup property value to Yes.
Select the Form Load Event Property, 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 (Form1's) 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 the Large Image in a New Tab or Window.

The declaration line containing the WithEvents keyword establishes a Form object named frm and assigns it a reference to Form1’s Class Module (Form_Form1), 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 the code.
If you are sure that Form1 has a Class Module, then you can use this statement:
```
Set frm = Form_Form1 'The Form will open in memory but not visible in Application Window. 
'You Need another statement.

frm.Visible = True
```
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 displays the typed character 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 in the TextBox.

Right-click to open the large image in a New Tab or 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) is dynamically enabled at runtime.

Download the Demo Database.

UserDefinedEvents.zip

LEARN MS-ACCESS TIPS AND TRICKS

Objects and Built-in Events Triggering - Part Three

Objects and Built-in Events

The earlier Article Links:

Objects And Their Built-in Events.

TextBox Events are captured by the TextBox Instance.

Create a Demo Form.

The Form Module VBA Code.

The Stand-alone Class Module.

The Class Module VBA Code.

Download Demo Database.

Streamlining Form Module Code in Standalone Class Module.

Existing Demo Databases Converted to the new Coding

Defining Custom Events in Microsoft Access - Part Two

Defining Custom Events in Microsoft Access.

User-Defined Event Example-2.

User-Defined Events.

The User-Defined Event Capturing Form.

Event Capturing Form Module VBA Code Review.

The User-Defined Events Trajectory View.

Download Demo Database

Streamlining Form Module Code in Standalone Class Module.

Existing Demo Databases Converted to the new Coding

Reusing Form Module VBA Code for New Projects.

Streamlining Form Module Event Procedures in a Standalone Class Module.

The Existing Form Module Coding Approach.

The Streamlining of Class Module Code.

Before You Dive In

How This Series Works

Access Control's Event Procedures on a Form

User-defined Custom Events.

Streamlining Form Module Code in Standalone Class Module.

Existing Demo Databases Converted to the new Coding

PRESENTATION: ACCESS USER GROUPS (EUROPE)

Translate

Post Feed

Get new posts by email:

Search

USEFUL LINKS

Useful-Links

Popular Posts

About Me

Blog Archive

Labels

Report Abuse

Statcounter