Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Streamlining Form Module Code - Part Eight

 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:

  1. Re-using Form Module VBA Coding for New Projects.
  2. Streamlining Form Module Coding Part-Two.
  3. Streamlining Form Module Coding Part-Three.
  4. Streamlining Form Module Coding Part-Four.
  5. Streamlining Form Module Coding Part-Five.
  6. Streamlining Form Module Coding Part-Six.
  7. Streamlining Form Module Coding Part-Seven.

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

  1. Usage of Data Table.
  2. Collection Object replaces the Object Array.
  3. Command Button Wrapper Class.
  4. Form Wrapper Class. Sections: Header, Detail, and Footer Event Procedure. 
  5. Using Timer-Interval Property for Digital Clock on the Form.
  6. 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

  1. MS-Access and Collection Object Basics
  2. MS-Access Class Module and Collection Objects
  3. Table Records in 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.

Demo Database Download Link:

Streamlining Form Module Code in Standalone Class Module.

  1. Reusing Form Module VBA Code for New Projects.
  2. Streamlining Form Module Code - Part Two.
  3. Streamlining Form Module Code - Part Three
  4. Streamlining Form Module Code - Part Four
  5. Streamlining Form Module Code - Part Five
  6. Streamlining Form Module Code - Part Six
  7. Streamlining Form Module Code - Part Seven
  8. Streamlining Form Module Code - Part Eight
  9. Streamlining Form Module Code - Part Nine
  10. Streamlining Form Module Code - Part Ten
  11. Streamlining Form Module Code - Part Elevan
  12. Streamlining Report Module Code in Class Module
  13. Streamlining Module Code Report Line Hiding-13.
  14. Streamlining Form Module Code Part-14.
  15. Streamlining Custom Made Form Wizard-15.
  16. Streamlining VBA Custom Made Report Wizard-16.
  17. Streamlining VBA External Files List in Hyperlinks-17
  18. Streamlining Events VBA 3D Text Wizard-18
  19. Streamlining Events VBA RGB Color Wizard-19
  20. Streamlining Events Numbers to Words-20
  21. Access Users Group(Europe) Presentation-21
  22. The Event Firing Mechanism of MS Access-22
  23. One TextBox and Three Wrapper Class Instances-23
  24. Streamlining Code Synchronized Floating Popup Form-24
  25. Streamlining Code Compacting/Repair Database-25
  26. Streamlining Code Remainder Popup Form-26
  27. Streamlining Code Editing Data in Zoom-in Control-27
  28. Streamlining Code Filter By Character and Sort-28
  29. Table Query Records in Collection Object-29
  30. Class for All Data Entry Editing Forms-30
  31. Wrapper Class Module Creation Wizard-31
  32. wrapper-class-template-wizard-v2
Share:

No comments:

Post a Comment

Comments subject to moderation before publishing.

PRESENTATION: ACCESS USER GROUPS (EUROPE)

Translate

PageRank

Post Feed


Search

Popular Posts

Blog Archive

Powered by Blogger.

Labels

Forms Functions How Tos MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Class Module External Links Queries Array msaccess reports Accesstips WithEvents msaccess tips Downloads Objects Menus and Toolbars Collection Object MsaccessLinks Process Controls Art Work Property msaccess How Tos Combo Boxes Dictionary Object ListView Control Query VBA msaccessQuery Calculation Event Graph Charts ImageList Control List Boxes TreeView Control Command Buttons Controls Data Emails and Alerts Form Custom Functions Custom Wizards DOS Commands Data Type Key Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Command Button Report msaccess menus msaccessprocess security advanced Access Security Add Auto-Number Field Type Form Instances ImageList Item Macros Menus Nodes RaiseEvent Recordset Top Values Variables Wrapper Classes msaccess email progressmeter Access2007 Copy Excel Export Expression Fields Join Methods Microsoft Numbering System Records Security Split SubForm Table Tables Time Difference Utility WScript Workgroup database function msaccess wizards tutorial Access Emails and Alerts Access Fields Access How Tos Access Mail Merge Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Common Controls Conditional Formatting Data Filtering Database Records Defining Pages Desktop Shortcuts Diagram Disk Dynamic Lookup Error Handler External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Message Box Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar updating upload vba code