Standalone Class Module and Events - Part Four ~ LEARN MS-ACCESS TIPS AND TRICKS

Continued from the earlier episodes of 'Objects and Their Built-in Events Part 3.'

Links to the earlier Episodes of the above topic are given below for Reference:

A Quick Look at Last Week's Session.

The Quantity TextBox object's events are enabled by selecting the [Event Procedure] option in the control's Event Property settings. When an event is raised by the TextBox object instance, it is captured by the Form's Class Module, where the corresponding event procedure is executed. This process is illustrated in the graphical image below.

The events raised by the Quantity TextBox on the Form can also be captured in the standalone `Class1` Class Module. To achieve this, an instance of the TextBox object must be declared in `Class1` and qualified with the `WithEvents` keyword. A reference to the Quantity TextBox control on the Form is then assigned to this object variable in the `Class1` Class Module.

Once the reference assignment is complete, events raised by the Quantity TextBox can be captured and processed within `Class1`. The same event procedures that are traditionally written in the Form's Class Module, where the Quantity TextBox control resides, can instead be implemented in the `Class1` Class Module. The graphical illustration below depicts this event-handling mechanism and the associated object relationships.

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

We want the `AfterUpdate` event, along with other events, to be raised by the Quantity TextBox on the Form to validate the value and ensure that it falls within the permitted range of 1 to 10. The `GotFocus` event changes the background color of the active TextBox, while the `LostFocus` event restores the control's original background color. Selecting the [Event Procedure] option for these event properties ensures that the corresponding events are raised at the appropriate time.

When an event is raised, the VBA code written within the associated event procedure stub is executed. In this example, however, we want to redirect these events to event procedures located in a standalone Class Module. The objective is to use the Form primarily for user interface design while moving business logic and event-handling code into separate Class Modules, thereby improving code organization, maintainability, and reusability.

This approach is particularly beneficial when the same functionality must be applied to multiple controls. For example, if a Form contains ten TextBox controls, implementing the background-color change in the Form Module would typically require ten separate `GotFocus` event procedures. By handling the events through a standalone Class Module, a single `GotFocus` event procedure can service all applicable TextBox controls, eliminating repetitive code and simplifying maintenance.

To implement this technique, we must adopt a different approach to event handling. Developers who are new to VBA event programming may find some aspects of this concept challenging at first. Since the complete architecture and its implications cannot be fully explained in a few pages, the topic is presented as a step-by-step tutorial. This method allows both the underlying concepts and their practical implementation to be examined in a structured manner, making the learning process more effective and easier to follow. The effort invested in understanding this approach will be rewarded with a more scalable, reusable, and maintainable coding framework.

The new RaiseEvent Procedure.

Instead of manually setting the [Event Procedure] option in the Property Sheet for events like AfterUpdate, OnGotFocus, or OnLostFocus, we can configure them in real-time through code. This allows us to directly invoke the RaiseEvent action from the object itself. To demonstrate this approach, we’ll create a new form by copying the earlier Form1 and renaming it Form1_RaiseEvent_2. In this new form, we’ll implement the updated method within its Class Module.

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

Make a copy of Form1 as explained above. Open the new Form in Design View, and change the Detail Section background color, then display its VBA Module. Copy the following Codes and paste them over the existing Code to replace them.

Option Compare Database
Option Explicit

'Decare an Object Variable C as Type Class1
Private C As Class1

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

'Assign Quantity TextBox Object to C.Txt Property
  Set C.Txt = Me.Quantity
  
'Enable Event Procedures of Quantity TextBox
'This method replaces the empty
'Event Procedure stub used earlier.

    Me.Quantity.AfterUpdate = "[Event Procedure]"
    
    Me.Quantity.OnGotFocus = "[Event Procedure]"
    
    Me.Quantity.OnLostFocus = "[Event Procedure]"
    
End Sub

Private Sub Form_Unload(Cancel As Integer)
'Release Class1 Object instance C from memory
    Set C = Nothing
End Sub

VBA Code Change Review.

As shown at the end of the `Form_Load()` event procedure, three lines of VBA code have been added to set the [Event Procedure] option programmatically for the corresponding event properties when the Form is loaded into memory. These settings remain active until the Form is closed. This approach eliminates the need to maintain empty event procedure stubs in the Form Module solely to trigger the event-raising mechanism.

Save and close the Form, then reopen it in Normal View. Enter a value greater than 10 in the Quantity TextBox and verify that the `AfterUpdate` event is raised and that the appropriate invalid-quantity message is displayed.

Next, enter a valid value within the range of 1 to 10. The validation process should function as expected, confirming that the event-handling mechanism continues to operate correctly.

It is important to note that all of the changes made so far have been implemented within the Form Module itself, primarily to demonstrate how events raised by the TextBox control can be captured and processed by the TextBox object instance declared in the `Class1` Class Module. However, our ultimate objective is different. We want to move the event-handling and application logic out of the Form Module and into a standalone Class Module. This design allows the Form to remain focused on user interface functionality. The Class Module handles the event processing and business logic, except for a few essential initialization lines that must remain in the Form Module.

The real-time RaiseEvent is enabled in the Class1 Module.

Now, we understand the need for the real-time Event-enabling Property option setting on the Form and have successfully completed the VBA Code. Let us see how we can implement this method in the stand-alone Class Module rather than coding in the Form Module.

Make a copy of the Class1 Module and rename it as Class2. We will preserve the Code in the Class1 Class Module for reference, and the changes we made in the new Copy. Make a copy of the earlier Form1_RaiseEvent_2 Form and rename it as Form1_RaiseEvent_3.

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

New Form Module Code Change.

Open the new Form and open its VBA Module. Copy the following code and paste it over the existing Form Module Code.

Option Compare Database
Option Explicit

Private C As Class2

Private Sub Form_Load()
  Set C = New Class2
  Set C.m_Frm = Me 'What is m_Frm member in Class2 Module?
End Sub

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

Save the Form with the new Code.

Now that we’ve removed the Me.Quantity.AfterUpdate = "[Event Procedure]" event-enabling lines from the Form Module, those actions will instead be handled in the new Class2 Class Module. The earlier Class1 Class Module code is still in place, so you can easily compare the two and see the transformation that takes place in Class2.

The second line in the Form_Load() Event Procedure is new and needs some explaining. As you can see on the left side of the = symbol, the Class2 Class Module object C has a Property Procedure with the name m_Frm that demands a Form Object to be passed to it as a parameter.

On the right side of the = symbol, the Form object (Me) is passed to the C.m_Frm Property procedure of the Class2 Class Module. In simple terms, we are passing the current Form object to a property of the same type within Class2. In earlier examples, we passed the Form object directly to the Form property declared with Public scope in Class1. However, in Class2, we declared the Frm object with a Private scope. By routing the assignment through a Public Property procedure, we ensure that external programs cannot directly modify the Frm property value, keeping it safe. This difference will become clearer when we examine the new VBA code in Class2.

Class2 Class Module Code Change.

Open Class2 Class Module (normally, the Class Modules will be given some meaningful name, instead of Class1, Class2). Copy the following VBA Code and paste it, overwriting the existing Code in the Module:

Option Compare Database
Option Explicit

Private Frm As Form
Private WithEvents Txt As TextBox

Public Property Get m_Frm() As Form 'GET Property Procedure
    Set m_Frm = Frm
End Property

Public Property Set m_Frm(ByVal mFrm As Form) 'SET Property Procedure

If Left(TypeName(mFrm), 4) = "Form" Then
    Set Frm = mFrm
Else
    MsgBox "Invalid Object " & mFrm & " passed as Form!"
    Exit Property
End If
    
    Call Class_Init 'Run Class_Init() Subroutine
End Property


Private Sub Class_Init()
    Set Txt = frm.Quantity
    
    Txt.AfterUpdate = "[Event Procedure]"
    Txt.OnGotFocus = "[Event Procedure]"
    Txt.OnLostFocus = "[Event Procedure]"
    
End Sub

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

Save the Class Module, open the new Form in Normal View, and try out the TextBox Quantity as before by entering Test Data.

Let us check the Code segment-wise to understand it. We are already familiar with the AfterUpdate and other Event Procedures.

Private Frm As Form
Private WithEvents Txt As TextBox

We declared two object Properties in the global declaration area of the Class Module: Frm the Form object Property and the earlier Property, Txt, the TextBox Object. Both are declared with Private scope here rather than with the Public scope, which we preferred in earlier examples. As I stated earlier, the Public scope declaration is not advisable for Class Object Properties because their values can be changed by external programs.

In this case, we should pass the Form object reference through the Property procedure, as shown in the code segment below. This approach not only ensures proper assignment but also allows us to validate the data passed into the Property procedure before actually storing it in the Property.

Public Property Get m_Frm() As Form
    Set m_Frm = Frm
End Property

Public Property Set m_Frm(ByRef mFrm As Form)

If Left(TypeName(mFrm), 4) = "Form" Then
    Set Frm = mFrm
Else
    MsgBox "Invalid Object " & mFrm & " passed to Form"
    Exit Property
End If

    Call Class_Init 'Run Class_Init() Subroutine
End Property

We are receiving a Form Object in the mFrm Parameter in the Public Property Set m_Frm() Property Procedure. We check the received object Type by using the TypeName() Function and extracting the first four characters of the object name Form_Form1_RaiseEvent_3. If the first Four Characters match the text 'Form', then it is the correct object for the Frm Property declared in the Global area. The Set Frm = mFrm assigns the Form object to the Property Frm.

When we need the Form reference stored in the Frm property, we use the public Property Get m_Frm() procedure. For example:


Debug.Print m_Frm.Name

However, from within the Class2 module itself, we can directly reference the property without going through the m_Frm wrapper, like this:


Debug.Print Frm.Name

The SET and GET Property Procedures.

Both the GET and SET Property Procedures must have the same name, m_Frm, or any suitable name you prefer, and their Data Type is shown as Form Object. If you recollect the Form Module statement: Set C.m_Frm = Me, that passes the active Form Object as a parameter to the Set Property Procedure in Class2 Class Module. In this Property Set Procedure, it performs a validation check to ensure that the parameter value is a Form Object and then assigns it to the Frm Property. The SET keyword is used for Objects only, and the LET keyword is used for other Property Types, like String, Integer, and others.

Note: To know more about Property Procedures and how they work in the Class Module, visit an earlier Page: MS Access Class Module and VBA

Once we receive the Form Object reference in our Class Module, we can easily address the controls on the Form and get their references in the Class Module to work with them. We need the reference of the TextBox with the name Quantity to enable its Events and validate the value entered into the TextBox by running the Event Procedures in the Class Module. For these actions, we have created a separate Subroutine called Class_Init()

The Class_Init() Subroutine is called from the Property Set m_Frm() Procedure, immediately after assigning the Form (Form_Form1_RaiseEvent_3) reference to the Global Property Frm.

Private Sub Class_Init()
    Set Txt = Frm.Quantity
    
    Txt.AfterUpdate = "[Event Procedure]"
    Txt.OnGotFocus = "[Event Procedure]"
    Txt.OnLostFocus = "[Event Procedure]"
    
End Sub

In this procedure, we first assigned the Quantity TextBox reference to the Txt TextBox Object Instance declared in the global declaration area of the Class3 Class Module. Check how we address the Quantity Textbox on the Form from Class3 Class Module (Frm.Quantity like Me.Quantity). If you would like to assign some value to the Quantity TextBox on the Form, you can do that with the statement Frm!Quantity = 25, from the Class Module.

The next three statements enable the AfterUpdate, OnGotFocus, and OnLostFocus Event Procedures by setting the text "[Event Procedure]" Option and setting them dynamically when the Form is Open. earlier, this procedure executed on the Form Module is now transferred to the Class Module.

Did you notice the change in the Event Procedure Names? In the Form Module, the AfterUpdate Event Procedure name is written as Private Sub Quantity_AfterUpdate(), the Textbox name as a name prefix follows the Event name, and both elements are joined with an underscore character.

In the Class Module, the TextBox object instance declaration name Txt becomes the Event Procedure name prefix: Private Sub Txt_AfterUpdate()

Instead of repeating the text "[Event Procedure]", we can define this as a Constant and use a simple name instead, like:

Private Sub Class_Init()
Const EP = "[Event Procedure]"
    Set Txt = frm.Quantity
    
    Txt.AfterUpdate = EP
    Txt.OnGotFocus = EP
    Txt.OnLostFocus = EP
    
End Sub

Loading Standalone Class Module in Memory.

Note: One important point is that the standalone Class Module Object cannot load itself into memory like the Class Modules of a Form or Report.

We need to instantiate the Class2 Class Module in memory through another Class Module or through the Class Module of a Form or Report, when these objects become active in memory. The standalone Class Module cannot load itself into memory. A Class Module attached to the form is loaded into memory when the form is opened. We already did this in the Form_Load() Event Procedure of the Form, with the following declarations:

Option Compare Database
Option Explicit
Private C As Class2

Private Sub Form_Load()

  Set C = New Class2
  
  Set C.m_Frm = Me 'What is m_Frm member in Class2 Module?
  
End Sub

Now, returning to the remaining code in the Class2 module, let’s review the event procedure we already know well—the one that validates the value entered in the Quantity textbox on the form.

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

For now, there’s no change in the event procedure code itself. Up to this point, we’ve only worked with a single TextBox object on the form. If you’ve followed the concept this far, that’s a big step forward—it will make it much easier to move ahead and explore more advanced techniques and tricks.

Open Form1_RaiseEvent_3 in normal view and try it out by entering a value greater than 10 first, then retry with a new value from the range 1 to 10 to ensure that all three Event Procedures work as planned.

Event Procedure of all Controls on Form in Standalone Class Module.

If you’re starting to feel impatient or bored with all the details and explanations, you might be wondering what this is really leading to. So, let me give you a quick preview of the final product. Open the link “WithEvents and all Form Control Types” to see a sample demo of what we’re ultimately working toward.

At the end of the above link Page, you will find a Demo Database Download Link. Download the Demo Database and open it. Open the frmControls_All Form in Design View and then display its Form Class Module. You will find only three or four lines of Code. Open the Form in Normal View, enter some Data in TextBoxes, and select options from ComboBox, ListBox, and other controls on the Form.

You will find responses either by displaying the data you entered in the Message Box or on the colored Help text display Label control at the bottom of the Form, indicating that the Events in the Form are tracked and responded to by the Class Module objects in memory. This is only a sample database demonstrating how the Events of the Form-based controls are captured in the Class Module and display messages.