May 2023 ~ 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 implementation of the new VBA coding procedure, segments of the work can be seamlessly transferred to new projects, facilitating coding endeavours 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 that we plan to devise will work together with Objects on the Form as they do normally & efficiently as far as the end user is concerned. Besides that, the backbone of this procedure can be exported into other Projects for ease of Coding there.

But, first I think it is interesting to learn how we are made to do the Coding always on the Form Module. Once we know that we can implement the procedure the way we plan to do it and make it useful in other Projects also.

For achieving that it needs to lay down a proper procedure so that Access Developers understands them and use them 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.

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 Builtin 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.

As shown in the above diagram, we have added a Textbox Control in the Detail Section of the Form. To be more specific and technically correct to say that we have added a Textbox object Instance in the Form.

We normally create several instances of MS Access Textbox objects on the Form. Every time we add a Textbox object instance to the Form, the Access System redefines the Name Property of the Textbox Object as WithEvents Text0 As TextBox as shown in the diagram above, the Label with a dark background and in grey letters. We can change the Name Property value Text0, or any other default name inserted by MS Access, to a meaningful name like Quantity.

Why do we need such a declaration of Textbox object instance with the keyword WithEvents, we have already seen that in the earlier episodes of this topic. It enables capturing the inbuilt Events of the Textbox when fired (RaiseEvent) and executes the Event Procedure Code written on the Form Module.

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 could 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 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

So both 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 is the parent object of its inbuilt Events and captures them when Events like AfterUpdate, Change, and GotFocus fire.

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 Object - 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.

Text0 has its own inbuilt 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, then 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.

The Event Procedure Code can be written only on the Text0 object’s Parent Class Module, that is the Form's Class Module. Hence, the Event Subroutines in this case are written in the Form’s Class Module as shown above. This is what we do normally on the Form Module.

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 with the Reference of the TextBox on the Form. Capture the Text0 Events fired from the Form Class Module and execute the Event Subroutines in the Class1 Class Module.

Create a Demo Form.

Let us try an example to prove this rule that is applied to the stand-alone Class Module too.

Create a new Form.
Add two Text Boxes 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 codes and Paste them 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 stand-alone Class Module with the name Class1.

Open VBA Editing Window (ALT+F11)
Select Class Module from 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 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 to make sure that everything is in order.
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 on the Form Module (for the RaiseEvent action) for After Update, Got Focus, and Lost Focus Events.
When we Compile the VBA Code the empty Program stubs will be removed from the Form Module and the [Event Procedure] option selected on the Event Properties will be deleted by 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 entered value. This is the trick that you have to watch for, capturing the Event of the Quantity Textbox control on the Form and executing the related Event Procedure Code in the Class1 Class Module.

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 in Yellow Color.
Enter the Quantity value 25 in the first Textbox and press 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 and 10 in the Quantity Textbox again.
This time you will get 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 are creating an Instance of the Textbox object, with the object variable name txt as a Listener object with the declaration of WithEvents Keyword and with Public scope.

Class Module Properties are normally declared with Private scope, to ensure that their value is not changed from other programs, 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 and it is ok this way for now.

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 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 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 Class1 Object Variable C in the global declaration area of the Form Module. This is like declaring an inbuilt Variable, say Dim City As String in the declaration area of Form Module. But it is not loaded into memory till you assign a Value into the City Variable, like City="New York". Similarly the declared object must be Instantiated into memory with the keyword New.

On 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.