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

Introduction.

This Article is the continuation of the preceding articles addressing the topic of Streamlining Form Module Code. Assuming familiarity with the previously discussed title topic, "Streamlining Form Module Code," 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 singular objective is to transition VBA coding from the Form Module to a stand-alone Class Module, reserving the Form solely for user interface design purposes. Is this feasible? Absolutely. Our intention is to demonstrate this feasibility through the forthcoming pages. Furthermore, upon implementing the new VBA coding procedure, segments of the work can be seamlessly transferred to new projects, facilitating coding endeavors therein.

With this new method of Coding procedure, we will be able to open the Code Modules independently and work without struggling with the Form Design every time to reach the Code, saving a good deal of manual effort. This new approach in Coding will serve the purpose of faster completion of Projects. The procedure we plan to devise will work in conjunction with Objects on the Form, as they normally do & efficiently as far as the end user's perspective. Besides that, the backbone of this procedure can be exported into other Projects for ease of coding there.

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 to lay down a proper procedure so that Access Developers understand it and use it routinely. Before doing that, we should know how the existing method of coding works on the Form Module, and what tricks are kept hidden behind the Form to make us do the coding in the Form Module itself.

The earlier Article Links:

Do you have any idea as to how and why we are asked to write the VBA Code on the Form Module itself for Event Procedures? How the Events are Raised and the Code is executed when an Event occurs. It is interesting to explore and find out these aspects of Coding. Once we know the inner workings of this trick and a few basic rules that go with it, then we can think of refining the existing procedure and doing it differently and in a better organized way.

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 any other alternative approach that can give us the same result and ease of Coding?

Objects And Their Built-in Events.

Let us study what happens when you add a Textbox Object to the Form. The diagram of a Textbox on the Form and all things associated with it, which we should know about, are shown below.

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, like 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 internally as something like WithEvents Text0 As TextBox. (If you look at the diagram above, that’s 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 understand if we want to grasp 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 is declared 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 Change Event. Therefore, we assume that this has something to do with the RaiseEvent (Announcer) action for the Event Change.

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

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.

So when we visualise the hierarchy of objects and Events, the Form Class Module is the top-level container of other Objects on the Form, like Textbox. Conversely, Textbox's Parent Object is the Form Class Module. The Textbox object Instance is the parent object of its built-in Events Properties, and captures them when Events like AfterUpdate, Change, and GotFocus fire from within these Objects..

Events On Form and Class Module.

The Event Procedure name must be written with the Parent Object name as a Prefix, like Sub Text0_AfterUpdate(). Following the same rule, the Event Procedure must be coded in the Textbox object's Parent Module - the Form Class Module. When an Event of the Textbox Object is fired, the parent object Textbox captures it (the WithEvents declaration enables it to do that), and the Event Procedure, which is written in the Textbox Object's Parent Form Class Module, executes the Event Procedure-based task. Here, you can see a pattern forming as to how the object event handling is concerned.

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

Text0 has its own built-in Event Collection. When any of them is raised, the parent object Text0 captures it. Because the Textbox Object instance Text0 is declared on the Form (or wherever an instance is created) with the WithEvents keyword, the VBA Code must be written in the Parent Class Module of the Text0 object. Our example is concerned with the Textbox object's parent Class Module is the Form's Class Module.

Keeping the above rules in mind, let us try something different than our earlier experiment with two Forms and Form Modules. This time, we will use a Form and a standalone Class Module, Class1.

Create an Instance of the same Text0 object in a stand-alone Class Module (say Class1), qualify it with the WithEvents keyword, and assign it to the Reference of a TextBox on the Form. Capture the Text0 Events fired from the Form Class Module and execute the Event Subroutines in the Class1 Class Module, rather than from the Form's 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 on 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 with the Name Form1 and Close the Form.
The Stand-alone Class Module.

Now, we need to create a standalone Class Module named Class1.

Open 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 Class Module with the name Class1, then do not change the Class Module Name; instead, change the Class1 name in the Form Module to match the name of 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 option [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, and the [Event Procedure] option selected in the Event Properties will be deleted by the Access System. If that happens, then our idea will not work as planned. To prevent that, we have added a Rem line '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 stand-alone Class Module Class1 to validate the entered Quantity Value (the valid value range is set as 1 to 10 only) and display a message based on the validity of the value entered. Try to figure out how the Event fired on the Form Module is captured in the Class1 Class Module, and the Quantity Textbox content is validated, and displays a status message.

The GotFocus Event will change the Quantity Textbox's background color to Yellow, and the LostFocus Event will reset the color.

The second Textbox is only a support control for setting the focus on this control when the LostFocus event occurs on the Quantity Textbox.

Now, we are all set.

Open Form1 in Normal View.
You will see the Textbox's background is now Yellow.
Enter the Quantity value 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 background color is now reset.
Now enter any value from the range 1 to 10 in the Quantity Textbox again.
This time, you will see the message saying that the Value entered is Valid.

Let us see how this works.

Check the Declaration line of 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. 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 declare it with the keyword WithEvents and create a Textbox object Instance, named 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 on 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 with the name txt and with the WithEvents keyword alone will not make any automatic 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, and then assign the reference of the Quantity Textbox on the form to the txt object to capture the Events of the Quantity Textbox on the Form. 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 is 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 Class1, 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 the 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 can be captured in the Class1 Module and execute the Event Procedure Code written within the Class1 Class Module. To trigger that Event firing action (RaiseEvent), we created the AfterUpdate empty 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 other methods to invoke 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