Objects and Built-in Events Triggering - Part Three ~ LEARN MS-ACCESS TIPS AND TRICKS

Objects and Built-in Events

This article is a continuation of the preceding ones, emphasizing the topic "Streamlining Form Module Code". Assuming familiarity with the previously discussed title 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. The following pages will demonstrate how this can be achieved. Once this coding approach is implemented, significant portions of the code can be reused across new projects, simplifying development and reducing coding effort.

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 framework of this approach 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 see how we can break away from that pattern, implement procedures in the way we want, and reuse them across other projects.

To achieve that goal, it needs a proper procedure so that Access Developers understand and use it routinely. Before doing that, it is important to understand how the existing coding method works on the Form Module and what tricks are hidden behind the Form, to force us to do the coding 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 can give 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 Large Image in New Tab/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 to know 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 inbuilt Event-related Procedure runs 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.

Events Captured by the TextBox and Form Class Module.

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 Large Image in New Tab/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 class module of the TextBox 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 Large Image in New Tab/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 need to 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 that there are no errors in the Code.
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 empty Program stubs will be removed from the Form Module by the system, 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 we can use 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 the Quantity 25 in the first TextBox and press the Enter Key. You will see the Validation Error message saying that "Valid Value Range 1 - 10 only".
The first TextBox's backcolor 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 for now, 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 WithEvents keyword, alone 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 Event of the Quantity TextBox in the Class1 Class Module. The Class1 Class Module will remain 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 the City variable does not actually 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 on 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 on the Form Module.

Download Demo Database.

Streamline3.zip

LEARN MS-ACCESS TIPS AND TRICKS

Friday, May 12, 2023