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 enabled Events, allowing us to capture them in memory and execute the associated Event Subroutines. Although this Array method served its purpose, it required us to redimension the Array space every time and maintain 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.
Earlier Article Links:
- Re-using Form Module VBA Coding for New Projects.
- Streamlining Form Module Coding Part-Two.
- Streamlining Form Module Coding Part-Three.
- Streamlining Form Module Coding Part-Four.
- Streamlining Form Module Coding Part-Five.
- Streamlining Form Module Coding Part-Six.
- Streamlining Form Module Coding Part-Seven.
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.
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 the above Subroutine. If we write them in the Form Module they will be in three different locations within three different Subroutines.
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.
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 transported through the Wrapper Class into memory as we did. 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 the 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(). All GotFocus Events will be captured in these Subroutines 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.
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.
This TextBox Wrapper Class Instance will be created as many times as the number of TextBoxes on the Form, with some Events enabled, and keep them in memory, till the Form is closed, to listen to the Events, and execute the Event Subroutine Code.
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, with or without Item Key. Those who are not familiar with the Collection Object go through the following links:
COLLECTION OBJECT
- MS-Access and Collection Object Basics
- MS-Access Class Module and Collection Objects
- 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.
In Episode Number Eight, two new Wrapper Classes, WrapForm, and WrapCmdButton Classes are introduced. 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.
No comments:
Post a Comment
Comments subject to moderation before publishing.