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 TextBox. When the Events are fired in the TextBox object instance, they are captured in the Form Module itself and the Event Procedure is executed, as depicted in the graphical image given 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. For doing 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 is assigned to the new object instance. 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.

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, both included 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 we write 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.

To achieve that we must devise a new procedure for the Coding task, which is what we plan to do. VBA beginners may find it difficult to understand the concept fully. The full story of this concept cannot be explained in a few pages, which is why this task has been taken in the form of a Tutoring Session so that each level of concept and practical implementation can be explained in detail.

The new RaiseEvent Procedure.

We can set, the "[Event Procedure]" option in real-time, rather than manually setting, in the AfterUpdate, OnGotFocus, and OnLostFocus, and others in the Event Property for invoking the RaiseEvent action to happen in the Object. To try out that, we have created a new Form (with the name: Form1_RaiseEvent_2), by copying the earlier Form1 and renaming it Form1_RaiseEvent_2, to implement the new method in its Class Module.

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 will eliminate the need for keeping 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.

But, again we have made all of these changes on the Form Module itself to capture the action in the Class1 Class Module-based TextBox object instance. Our main targeted plan is to remove the VBA Coding from the Form Module and capture them into the stand-alone Class Module and do all the coding there, leaving the Form for User Interface design only, except for a few lines of essential Code is unavoidable.

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

New Form Module Code Change.

Open the new Form and open its VBA Module. Copy the following Codes and Paste them 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 have removed the Me.Quantity.AfterUpdate = "[Event Procedure]" Event enabling lines from the Form Module and these will be run from the new Class2 Class Module. This will retain the earlier Class1 Class Module Code and you can compare what kind of transformation took place in the Class2 Class Module.

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. It simply means that we are passing our Form Object to a Property of the same type in the Class2 Class Module. In earlier examples we pass the current Form Object directly to the Form Object Property declared in the Class1 Class Module with Public scope. In the new Class2 Class Module, we declared the Frm object with Private Scope. Form Object assignment to the Frm Property we routed it through the Public Property Procedure so that external Programs cannot change the Frm Property value and will remain safe. It will be clear when we see the new VBA Code of the Class2 Class Module.

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

Let us check the Code segment-wise to understand them. 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 route the Form Object reference through the Property Procedure, as shown in the Code segment given below and we can check the validity of the data passed to the Property Procedure, before assigning it to 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 want the Form reference in the Frm Property we call for the Public Property Get m_frm() Property Procedure, like Debug.Print m_Frm.Name. But, from within the Class2 Class Module, we can refer to the Frm Property directly like 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 its Data Type is shown as Form Object. If you remember our Form Module entry: Set C.m_Frm = Me passes the Form Object Me 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 Key word 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 get 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 "[Event Procedure]" Option and setting them dynamically when the Form is Open. Earlier this procedure executed on the Form Module itself 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 these 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 you should know about is that the standalone Class Module Object cannot load itself into memory like the Form or Report-based Class Modules.

We have to instantiate the Class Module Object, in any other Module that will be active in memory, like Form Module, or in another stand-alone Class Module that loads into memory first, to use its Properties, Methods, and Events. 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

Coming back to the remaining Code in the Class2 Class Module we are familiar with the Event Procedure Code given below used to validate the Quantity entered into its TextBox on 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

There is no change in the above Event Procedure Code for the time being. So far we have played around with a single TextBox object on the Form. If you could follow the concept so far then that would be very helpful to go further deep into this concept and to learn more 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 are getting impatient, bored with the details and explaining, and wondering what this is all about, I will give a sample of the Final Product. Open the Link: WithEvents and all Form Control Types for a sample demo of the final product we plan to implement.

At the end of the 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.

Demo Database Download

Streamline4.zip

LEARN MS-ACCESS TIPS AND TRICKS

Thursday, June 8, 2023