Streamlining Form Module Code Part Four ~ LEARN MS-ACCESS TIPS AND TRICKS

Continued from the earlier episodes on Streamlining Form Module VBA Code Part Three.

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

A Quick Look at Last Week's Session.

A quick look at how the Quantity TextBox object Events were enabled (RaiseEvent) by selecting the "[Event Procedure]" Option from the drop-down list in the Event Property of the TextBox. When the Events are fired from the TextBox object instance, they are captured in the Form Module itself, and the Event Procedure is executed, as depicted in the graphical image below.

When the Quantity TextBox Object's Events are fired on the Form, those Events can be captured in the Class1 standalone Class Module also. To do that, we must define an Instance of the TextBox Object and qualify it with the keyword WithEvents in the Class Module. Then the Quantity TextBox object Reference from the Form must be assigned to the new object instance in the Class1 Class Module. In this way, when the Events of the TextBox are fired in the Form Module, they can be captured in the Class1 Class Module also. The same Event Procedures, which we normally write in the Form Module, where the Quantity TextBox Instance resides, can be written in the Class1 Class Module. The Graphic image of these actions is shown below for reference.

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

We want the AfterUpdate Event and others to fire from the Quantity Textbox in the Form to check the validity of the value entered into the TextBox, to ensure that the criteria range of values between 1 and 10 is met. The GotFocus Event changes the background color of the active TextBox. The LostFocus Event ensures that the background color is reset to its earlier color. The "[Event Procedure]" option selected in all three Event Properties of the Textbox will ensure that these Events will fire (RaiseEvent) at the appropriate Time.

When the Events are fired, the Event Procedure Code written within the empty program stub is executed. But, in our case, we would like to redirect these Events to a stand-alone Class Module-based Event Procedure. Why, because we want the Form for User Interface designing only and take away the Coding task into separate Class Module(s) for ease of Coding and Maintenance. This will also enable the reuse of Event Procedure Code with repetitive use on the Form, like the active TextBox's background settings. When written on the Form Module, it needs to duplicate the GotFocus() Event Procedure 10 times for 10 TextBoxes. The Standalone Class Module-based Coding needs only one GotFocus() Event procedure.

To make this work, we need to adopt a fresh procedure for handling our coding tasks. Admittedly, VBA beginners may find it challenging to fully grasp the finer details of this new concept right away. The complete picture can’t be conveyed in just a few pages, which is why I’ve chosen to present it as a step-by-step tutoring session. This way, each stage—both the underlying concept and its practical application—can be clearly explained. Trust me, the time you invest in understanding this approach will be well worth it.

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 you can see at the end of the Form_Load() Event Procedure, we have added three lines of Code to set the "[Event Procedure]" option, in real-time, in their respective Event Property, when the Form is loaded into memory, and will keep it there till the Form is closed. This approach eliminates the need to keep an empty Event Procedure stub on the Form Module to trigger the RaiseEvent Action.

Save and Close the Form. Open it in Normal View. Try entering a value greater than 10 to test whether the AfterUpdate Event fires and an invalid Quantity message is displayed or not.

Now, try entering a valid value within the range of 1 to 10. All of these tests will work normally as before.

Once again, all these changes were made within the Form Module itself to capture the action in the Class1 Class Module–based TextBox object instance. However, our real goal is different: we want to move the VBA code out of the Form Module entirely and handle it within a standalone Class Module. This way, the Form can remain focused solely on user-interface design, while the heavy lifting of event handling and logic is managed in the Class Module—aside from a few unavoidable lines of essential code.

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 done that with 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 to refer to it to see what 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, we perform a validation check to ensure that the parameter value is a Form Object and then assign 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.