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

Introduction.

Continued from "Streamlining Form Module Code - Part Six & Seven"

In the past, we utilized the TextBox Wrapper Class Array to store TextBox instances with Events enabled, allowing us to capture them in memory and execute the associated Event Subroutines. Although this Array method served its purpose, it required re-dimensioning the Array space every time and maintaining separate indices for various types of objects, such as Command Buttons, ComboBoxes, ListBoxes, and others. Fortunately, there is a more efficient option at our disposal - the Collection Object.

The earlier Article Links are given below:

In this session of our Tutorial on the Title topic, we will learn the following tricks:

Usage of Data Table.
Collection Object replaces the Object Array.
Command Button Wrapper Class.
Form Wrapper Class. Sections: Header, Detail, and Footer Event Procedure.
Using Timer-Interval Property for Digital Clock on the Form.
Form Closing Count Down without using TimerInterval Property.

Trial Run Form-Image, taken from the Countdown and Form closing phase.

Using Data Table.

We run all kinds of validation checks on the data entered into the TextBox before accepting it. The entered data also can be part of any calculations and the result may be saved in another TextBox on the Form. These actions we could do on the Form from the Class Module, whether the TextBox's Control Source is assigned to a Table field or not. The only difference is that the data is not stored anywhere, but we could run the required data validation checks away from the Form Module and in the stand-alone Class Module.

The Collection Object replaces the Object Array.

With the new Coding procedure, we could reduce the number of Event Subroutines of a particular type into a single one.

For example, check the following sample Exit() Event Procedure of three TextBoxes written under a single Event Subroutine:

Private Sub Txt_Exit(Cancel As Integer)
Dim i As Integer, Msg As String
Dim info As Integer

Select Case Txt.Name
    Case "Description"  'TextBox - Description
        If Len(Nz(Txt.Value, "")) = 0 Then
            MsgBox "Item Description Empty"
            Cancel = True
        End If
        
        
    Case "Quantity"     'TextBox - Quantity
        i = Nz(Txt.Value, 0)
        If i < 1 Or i > 10 Then
            Msg = "Valid Value Range 1 - 10 Only."
            info = vbCritical
            MsgBox Msg, vbOK + info, "Quatity_Exit()"
        GFColour frm ', Txt
            Cancel = True
        Else
            Msg = "Quantity: " & i & " Valid."
            info = vbInformation
            MsgBox Msg, vbOK + info, "Quatity_Exit()"
        End If
        
        
    Case "UnitPrice" 'TextBox - UnitPrice
    
        i = Nz(Txt.Value, 0)
        Msg = ""
        If i <= 0 Then
            Msg = "Enter a Value greater than Zero!"
             info = vbCritical
         GFColor frm 
            Cancel = True
            MsgBox Msg, vbOK + info, "UnitPrice_Exit()"
        Else
            info = vbInformation
            MsgBox "Unit Price: " & i, vbOK + vbInformation, "UPrice_Exit()"
        End If
    End Select
End Sub

If there are more TextBoxes with the Exit() Event Subroutines, they can be written within this structured Subroutine. If we write them in the Form Module they will be within three different Subroutines in three different locations among other object's Subroutines in the Form Module.

The TextBox names (highlighted with Red Color along with other TextBoxes) may be enabled (RaiseEvent) with other required inbuilt Events, like GotFocus, LostFocus, and others and they also will be grouped as shown above, all GotFocus cases within a single GotFocus() Event Subroutine, and all LostFocus cases within one LostFocus() Event Subroutine.

In short, you need to write only one BeforeUpdate() Event Subroutine in the Standalone Class Module for 25 TextBoxes on the Form, if all of them are enabled with this Event. All TextBox's BeforeUpdate() Event Procedure can be inserted into this single Subroutine. This rule applies to all other Event Subroutines of TextBoxes. Everything will be in one Standalone TextBox Wrapper Class Module for Code maintenance and debugging.

All Object Types will have their own Standalone Wrapper Classes and their Event Subroutines are organized this way.

All these Event Procedures are executed one at a time as and when it is called for. They can be in the Form Module or they can be in the Wrapper Class Module in memory.

The TextBoxes (and other objects) on the Form are defined with the Keyword WithEvents individually when they are inserted in the Form. They are all spread out on the Form and we need to write Event Subroutines separately for each TextBox on the Form Module.

But, with the use of our Wrapper TextBox Class, we create only one Instance of the TextBox Class predefined with the keyword WithEvents. The TextBox Class declaration in the Wrapper Class is with an object name Txt like Private WithEvents Txt As TextBox. So all the TextBox-related Event Subroutine Name will be prefixed with the TextBox Object Name Txt like Private Sub Txt_GotFocus().

We will be creating separate instances of this Wrapper Class Module for each TextBox on the Form, and assigned with the References of the TextBoxes on the Form, through an automated procedure. This saves a lot of time we normally spend on writing and maintaining the Code for individual TextBoxes on the Form Module, during the Database development phase.

All GotFocus Events will be captured in this Subroutine and we need to check for their name in Select Case Txt.Name followed by Case "Quantity" to determine which TextBox on the Form fired the Event, accordingly we write the required Code under the TextBox Name. This kind of individual name checking is unnecessary if all the TextBoxes require the same VBA Code for a particular Event, like the TextBox highlight example we tried in the earlier Episode. Or selectively write Code for required TextBoxes in this way and Code that applies globally for all TextBoxes can be written outside this name-checking structure.

The Wrapper Class will have an additional declaration of a Form object, with the name like frm, mostly without the WithEvents qualification. It is necessary to read or write other control values on the Form, from the Wrapper Class in memory.

To keep the Wrapper Class Instances in memory we first chose the Wrapper Class Array option in the Class_Init() Subroutine in the WrapObject_Init2 intermediate Class Module. We need to create Wrapper Classes for other Access Controls like Command Buttons, Combo Boxes, and others. In that case, we need separate Indices based on the number of such controls on the Form. An alternative option is to use the Collection Object. The Collection object can hold any type of object or other type of Values, with or without the Item Key. Those who are not familiar with the Collection Object go through the following links:

COLLECTION OBJECT

The Class_Init() Subroutine Code in the WrapTextBox_Init Class is presented in the Title Topic Part-Six and the new version WrapTextBox_Init2 is implemented with the Collection Object.

WrapTextBox_Init Version VBA Code.

'Scan for TextBox Controls on Form
j = 0
'and Enable the required Event Procedures
For Each ctl In iFrm.Controls
Select Case TypeName(ctl)
    Case "TextBox"
        Select Case ctl.Name
            Case "Quantity"
                
                    Set iTxt = New WrapTextBox 'Create Instance
                    Set iTxt.tx_Frm = iFrm     'Pass Form object
                    Set iTxt.t_Txt = ctl       'Pass TextBox Control on Form
            
                    iTxt.t_Txt.OnExit = EP     'Enable Event
                    iTxt.t_Txt.OnGotFocus = EP '     "
                    iTxt.t_Txt.OnLostFocus = EP '    "
        
                j = j + 1                       'increment counter
                ReDim Preserve TxtArray(1 To j) 'Redim Array
                Set TxtArray(j) = iTxt          'Save WrapTextBox Class
                Set iTxt = Nothing              'Erase temp Instance

            Case "UnitPrice"
                
                    Set iTxt = New WrapTextBox 'Create Instance
                    Set iTxt.tx_Frm = iFrm
                    Set iTxt.t_Txt = ctl
            
                    iTxt.t_Txt.OnExit = EP
                    iTxt.t_Txt.OnGotFocus = EP
                    iTxt.t_Txt.OnLostFocus = EP
        
                j = j + 1
                ReDim Preserve TxtArray(1 To j)
                Set TxtArray(j) = iTxt          'Save it in Object Array
                Set iTxt = Nothing              'Erase temp Instance
    
            Case "TotalPrice"
            
                Set iTxt = New WrapTextBox      'Create Instance
                Set iTxt.tx_Frm = iFrm
                Set iTxt.t_Txt = ctl
            
                iTxt.t_Txt.OnGotFocus = EP
                iTxt.t_Txt.OnLostFocus = EP
        
                j = j + 1
                ReDim Preserve TxtArray(1 To j)
                Set TxtArray(j) = iTxt           'Save it in Object Array
                Set iTxt = Nothing               'Erase temp Instance
            End Select
        
    End Select
Next

WrapTextBox_Init2 Version VBA Code.

'Scan for TextBox Controls on Form
'and Enable the required Event Procedures
For Each ctl In iFrm.Controls

    Select Case TypeName(ctl)
        Case "TextBox"
            Select Case ctl.Name
                Case "Description"
                    Set iTxt = New WrapTextBox2 'Create Instance
                    Set iTxt.tx_Frm = iFrm
                    Set iTxt.t_Txt = ctl       'Pass TextBox Control on Form
                     
                    iTxt.t_Txt.OnGotFocus = EP '     "
                    iTxt.t_Txt.OnLostFocus = EP '    "
                
                    Coll.Add iTxt 'Save WrapTextBox2 Class
                
                    Set iTxt = Nothing 'Erase temp Instance
                    
                Case "Quantity", "UnitPrice"
                    Set iTxt = New WrapTextBox2 'Create Instance
                    Set iTxt.tx_Frm = iFrm
                    Set iTxt.t_Txt = ctl       'Pass TextBox Control on Form
                     
                    iTxt.t_Txt.OnExit = EP     'Enable Event
                    iTxt.t_Txt.OnGotFocus = EP '     "
                    iTxt.t_Txt.OnLostFocus = EP '    "
                
                    Coll.Add iTxt 'Save WrapTextBox2 Class
                
                    Set iTxt = Nothing 'Erase temp Instance
                
                
                Case "Discount"
                    Set iTxt = New WrapTextBox2 'Create Instance
                    Set iTxt.tx_Frm = iFrm
                    Set iTxt.t_Txt = ctl       'Pass TextBox Control on Form
                     
                    iTxt.t_Txt.OnGotFocus = EP '     "
                    iTxt.t_Txt.OnLostFocus = EP '    "
                
                    Coll.Add iTxt 'Save WrapTextBox2 Class
                
                    Set iTxt = Nothing 'Erase temp Instance
            End Select
        
    End Select
Next

Compare both versions of the same VBA Code, those lines marked with red, in both versions, are used for the TextBox Object Instances.

The Z-Concept - Streamlining of Form Module Coding Logic of Class Objects at a Glance in the updated Diagram.

Form Sections Header, Detail, Footer Event handling.

There are several Form object Events. We will check how the Click and MouseMove Events are invoked from the Form Sections: Detail, Form Header, and Form Footer areas. I don't ask you to design a Form like the one shown at the top of this page. You can download the Demo database from the link given at the end of this page and try it out. Look for the changes we made in the WrapObject_Init2, WrapTextBox, in the Demo database. Compare the VBA Code with the Code you already have in the Demo Database downloaded from Episode Six.

Episode Number Eight introduces two new Wrapper Classes, WrapForm, and WrapCmdButton Classes. Form Event Subroutines and Events specific to Form Sections are run from the WrapForm Class Module.

WrapForm Class Module VBA Code is given below:

Option Compare Database
Option Explicit

Private WithEvents Sfrm As Access.Form
Private WithEvents ScD As Access.Section

'------------------------------------------------------
'Streamlining Form Module Code
'in Stand-alone Class Modules
'------------------------------------------------------
'Access.Form Wrapper Class Module
'Author: a.p.r. pillai
'Date  : 27/07/2023
'Rights: All Rights(c) Reserved by www.msaccesstips.com
'------------------------------------------------------
Public Property Get s_frm() As Access.Form
Set s_frm = Sfrm
End Property

Public Property Set s_frm(ByRef FFrm As Access.Form)
    Set Sfrm = FFrm
End Property

Public Property Get s_SecD() As Section
    Set s_SecD = ScD
End Property

Public Property Set s_SecD(ByVal iSec As Section)
    Set ScD = iSec
End Property

Private Sub scD_Click()
Select Case ScD.Name
    Case "FormHeader"
        MsgBox "FormHeader Click", , "FormHeader Section"

End Select
End Sub

Private Sub scD_DblClick(Cancel As Integer)
Select Case ScD.Name
    Case "FormFooter"
        MsgBox "FormFooter DblClick", , "FormFooter Section"
End Select
End Sub

Private Sub scD_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
Select Case ScD.Name
    Case "Detail"
    Sfrm.Label39.Caption = X & " , " & Y
End Select
End Sub

In the Global declaration area of the Class Module two Class objects, Form and Form Section objects are declared. Followed by their Object assignment Public Property procedures.

The Form Sections Click, MouseMove, and Double Click Events are enabled for the WrapForm Class Module Demo. The Mouse Move is enabled in the Detail Section and displays the X, and Y coordinate values in a label in the Detail Section, the Form Header is enabled with the Click Event, and the Footer Section is enabled with the Double-Click Event to run. Both Header and Footer Events display a message.

Note: The indicator label 'Click here' in the Form Footer area may read as 'Double Click'.

The Digital Clock is run from the WrapObject_Init2 Class Module. The iFrm_Timer() Subroutine is also placed in this Module to update the label control Caption Property with the Time in "hh:nn:ss" format.

The Clock runs in one-second intervals it is not advisable to put this Code in the WrapForm Class Module. The Form Section-wise Events create three instances of the WrapForm Class in memory. If the Clock running Code is put in this Class Module the Code will run from all three instances of WrapForm Class in memory.

The WrapCmdButton Class Module VBA Code.

Option Compare Database
Option Explicit

Private WithEvents cfrm As Form
Private WithEvents cmd As CommandButton 'CommandButton object

'------------------------------------------------------
'Streamlining Form Module Code
'in Stand-alone Class Modules
'------------------------------------------------------
'Access.CommandButton Wrapper Class Module
'Author: a.p.r. pillai
'Date  : 27/07/2023
'Rights: All Rights(c) Reserved by www.msaccesstips.com
'------------------------------------------------------

'Form's Property GET/SET Procedures
Public Property Get c_Frm() As Form
    Set c_Frm = cfrm
End Property

Public Property Set c_Frm(ByRef cmdfrm As Form)
    Set cfrm = cmdfrm
End Property

'TextBox Property GET/SET Procedures
Public Property Get c_cmd() As CommandButton
    Set c_cmd = cmd
End Property

Public Property Set c_cmd(ByRef pcmd As CommandButton)
    Set cmd = pcmd
End Property

Private Sub cmd_Click()
Select Case cmd.Name
    Case "cmdClose"
        If MsgBox("Close this Form?", vbOKCancel + vbQuestion, "cmd_Click") = vbOK Then
            DoCmd.Close acForm, cfrm.Name
        End If

End Select
End Sub

'Form Closing CountDown...
'Do Not write Form_Unload() on Form Module

Private Sub cfrm_Unload(Cancel As Integer)
Dim T As Double, t2 As Double
Dim strMsg As String

'cFrm.TimerInterval = 0 - Disable Clock, if necessary
strMsg = "Form will Close in "
cfrm.Label29.Visible = True
T = Timer
Do While Timer < T + 10
            t2 = Timer
            Do While Timer < t2 + 0.25
                DoEvents
            Loop
cfrm.Label29.Caption = strMsg & " " & Int((T + 10) - Timer) & " Seconds."
    DoEvents
Loop

End Sub

The Command Button Wrapper Class is a simple Class that mostly handles the Click Event only. The Command Button and Form object declarations are there in the Global declaration area. The Get and Set Property Procedure pairs of both objects appear next.

Next, the Private Sub cmd_Click() Event Subroutine for the cmdClose Button Clicks Event is inserted. If any other Command Buttons are inserted on the Form their Click Event also can be put in this Event Subroutine by introducing the Select Case . . . End Select structure to identify the Command Button name to handle their Events separately.

Before closing the Form the Sub cfm_Unload() Event fires. The rest of the Code is a Form Closing countdown simulation that runs for 10 seconds before actually closing the Form. There is a Label Control kept hidden above the TextBoxes on the Form made visible now. The Countdown simulation text is displayed in the Label Control and when the count is equal to zero, closes the Form.

Note: If the Form_Unload() Event Subroutine is present in the Form Module then the WrapCmdButton Class-based cfm_Unload() Event Subroutine will not be able to execute. Because the Form Module-based Form_Unload() Event gets priority and closes the Form.