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

Introduction.

This is the continuation of the last two Articles published earlier on this topic. Hope you have gone through the Title topic: Streamlining Form Module Code. By now you have a general idea of where we are heading, with the main points on the Event, RaiseEvent, and WithEvents declarations and how they work together.

There is only one goal: that is to shift the VBA Coding from Form Module to the stand-alone Class Module and use the Form for User Interface Designing only. Is it possible? Yes, it is possible, that is what we are going to prove through these pages. Besides that when the new VBA Coding procedure is implemented part of the work can be transported to new Projects for ease of Coding there.

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, saving a good deal of manual effort. This new approach in Coding will serve the purpose of faster completion of Projects. The procedure which 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, I think it is interesting to learn first, how we are made to do the Coding always on the Form Module. Once we know that we could 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 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 goes 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 gets executed when an event occurs? Is there any other alternative approach that can give us the same result and ease of Coding?

Events and Objects Hierarchy.

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 when we add a Textbox object instance to the Form, 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 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 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, this is the RaiseEvent (Announcer) Property for the Event Change.

Whenever we type some text in Text0 Textbox, the Change Event fires (or triggers the Subroutine, with the Event name Change and the parent object Text0 name (Msg in the Name Property sample Code given below) as Prefix - Sub Text0_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 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 visualize 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 the Events like AfterUpdate, Change, and GotFocus fires.

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

If we create an instance of the same Text0 object in a stand-alone Class Module (say Class1) and qualify it with the WithEvents keyword then you can capture the Text0 Events from the Form Class Module and write the Event Procedures in 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 and 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

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 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 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 Properties to add the empty program 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.

The public scope is required to reference this Txt object, outside the Class Module Class1. Class1 Class Module will be Instantiated in the Form1 Class Module. Normally the Public Scope of Class Module Properties is not allowed because it is vulnerable and can get their value changed from outside.

Class Module Properties are normally declared with Private scope 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, 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 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

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.

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 Class1 Module. The txt Object is declared with the keyword WithEvents so that when an Event, like AfterUpdate, is fired from the Quantity Textbox object it can be captured in 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.