Introduction.
In this Episode of Streamlining Form Module VBA Code, we will create Wrapper Classes for ComboBox and OptionGroup Controls. Having gone through the previous Episodes, you are now acquainted with the process of creating Wrapper Class Modules for other controls, such as TextBox, ListBox, CommandButton, and TabControl. You have also learned to write Event Subroutines within these modules instead of placing them in the Form Module.
ComboBox and OptionGroup Control.
This time we will learn the usage of ComboBox and OptionGroup Controls as shown in the form image given below.
The Order Detail data in the ListBox are filtered in OrderDetailQ1 based on the Employee ID selected in the ComboBox with the name cboEmp above the ListBox Control. The SQL of the Query is given below:
OrderDetailQ1 SQL.
SELECT Orders.EmployeeID, Employees.LastName, Orders.OrderID, Val(Format([OrderDate],"yyyy")) AS [Year], Orders.Freight FROM Employees INNER JOIN Orders ON Employees.EmployeeID = Orders.EmployeeID WHERE (((Orders.EmployeeID)=[Forms]![Frm_OptionGroup]![cboEmp]));
The Freight Value in OrderDetailQ1 is summarised Year-wise in OrderSummaryQ1. The OrderSummaryQ1 Query has the year-wise Freight Value Source Data for the Graph Chart on the Form. OrderSummary SQL is given below.
OrderSummaryQ1 SQL.
SELECT [OrderDetailQ1].EmployeeID, [OrderDetailQ1].LastName, [OrderDetailQ1].Year, Sum([OrderDetailQ1].freight) AS Freight FROM OrderDetailQ1 GROUP BY [OrderDetailQ1].EmployeeID, [OrderDetailQ1].LastName, [OrderDetailQ1].Year;
Freight Summary Data for the Chart.
Employee Last Name Year Freight Davolio, Nancy Davolio 1996 ₹ 1,871.09 Davolio, Nancy Davolio 1997 ₹ 4,584.47 Davolio, Nancy Davolio 1998 ₹ 2,381.13
How ComboBox, ListBox, and the Chart linked Together?
When the Employee Code is selected in the ComboBox the AfterUpdate Event is fired and the statement cbofrm.List0.Requery updates the ListBox contents.
There is a hidden Unbound TextBox on the Form with an expression to copy the changed Employee ID Value from the cboEmp ComboBox. This is used for the "Link Master Field" Property in the Graph Chart to update the Freight Year-wise Summary Data in the Graph Chart.
So, by changing the Employee ID (Combobox displays the Employee Name only, and the first column EmployeeID width Property value is set to zero) the ComboBox refreshes the ListBox OrderDetail and the Freight Year-wise Values on the Graph Chart instantly.
The OptionGroup Control.
The OptionsGroup Control can have a group of Radio Buttons or Check Boxes or Toggle Buttons and all of them are placed within a Frame. Here we are using the Radio Buttons within the OptionGroup Frame with the name Frame7. The Frame7 has three Radio Buttons with their Labels and is placed below the ListBox and Graph Chart objects.
There are three options to display the Employee's Freight Sales Values in three different categories.
- - The highest Freight Sales Value of the Employee.
- - The Lowest Freight Sales Value.
- - The Total Fright Sales Value.
The Unbound TextBox at the left side of the OptionGroup Control will display the Value when the option Radio Button is selected. The selected option description will appear in a label control, at the left side of the Unbound TextBox in an animated style, by moving the Text from right to left.
The Command Button Click will close the Form.
We already created wrapper classes for the ComboBox and ListBox in the earlier episodes. The OptionGroup Control is new in this Series of Tutorials and needs a Wrapper Class. When we place an OptionGroup Control on the Form the default name used by Microsoft Access is something like Frame7. So we will create a Wrapper Class for the OptionGroup Control with the name OptFrame.
The OptFrame Wrapper Class VBA Code.
Option Compare Database Option Explicit Private WithEvents Opt As Access.OptionGroup Private frm As Access.Form '------------------------------------------------------ 'Streamlining Form Module Code 'in Stand-alone Class Modules '------------------------------------------------------ 'OptionGroup Wrapper Class 'Author: a.p.r. pillai 'Date : 31/08/2023 'Rights: All Rights(c) Reserved by www.msaccesstips.com '------------------------------------------------------ 'Form's Property GET/SET Procedures Public Property Get opt_Frm() As Form Set opt_Frm = frm End Property Public Property Set opt_Frm(ByRef ofrm As Form) Set frm = ofrm End Property 'TextBox Property GET/SET Procedures Public Property Get o_opt() As OptionGroup Set o_opt = Opt End Property Public Property Set o_opt(ByRef mopt As OptionGroup) Set Opt = mopt End Property 'Event Subroutines Code Private Sub opt_Click() Dim Rslt As Variant Dim Cap(1 To 3) As String Static strText As String Cap(1) = "Highest Freight Value:" Cap(2) = " Lowest Freight Value:" Cap(3) = " Total Freight Value:" Select Case Opt.Name Case "Frame7" Select Case Opt.Value Case 1 'Repeated Clicks on the same option is ignored. If strText = Cap(1) Then Exit Sub Rslt = DMax("Freight", "OrderDetailQ1") Case 2 If strText = Cap(2) Then Exit Sub Rslt = DMin("Freight", "OrderDetailQ1") Case 3 If strText = Cap(3) Then Exit Sub Rslt = DSum("Freight", "OrderDetailQ1") End Select End Select frm!Result = Rslt strText = Cap(Opt) Call Animate(strText) 'Label Animation End Sub Private Sub Animate(ByVal txt As String) 'Label Animation Dim L As Double Dim n As String Dim T As Double Dim j As Integer L = Len(txt) txt = Space(L) & txt For j = 1 To Len(txt) - L n = Left(txt, 1) txt = Right(txt, Len(txt) - 1) txt = txt & n frm.lblResult.Caption = Left(txt, L) Delay 0.02 'delay 20 milliseconds Next End Sub
The OptionGroup Control object Opt Property is qualified with the Keyword WithEvents is declared in the global area of the Class Module. The Wrapper Class Module name is OptFrame. There is a Form object Property with the name frm also declared in the Global Area followed by the Property Procedures for the Global Properties.
The Opt_Click() Event Subroutine.
Private Sub opt_Click() Dim Rslt As Variant Dim Cap(1 To 3) As String Static strText As String Cap(1) = "Highest Freight Value:" Cap(2) = " Lowest Freight Value:" Cap(3) = " Total Freight Value:" Select Case Opt.Name Case "Frame7" Select Case Opt.Value Case 1 If strText = Cap(1) Then Exit Sub Rslt = DMax("Freight", "OrderDetailQ1") Case 2 If strText = Cap(2) Then Exit Sub Rslt = DMin("Freight", "OrderDetailQ1") Case 3 If strText = Cap(3) Then Exit Sub Rslt = DSum("Freight", "OrderDetailQ1") End Select End Select frm!Result = Rslt strText = Cap(Opt) Call Animate(strText) 'Label Animation End Sub
There are three options in the OptionGroup Control on the Form to extract the Highest, Lowest, and Total Freight Values from the Order Sales transactions, for the selected Employee ID in the ComboBox cboEmp.
The OptionGroup Button Click will retrieve the values from OrderDetailQ1 using the DMax(), DMin(), and DSum() Functions from the filtered Order Details data and display it in the Rslt TextBox, on the left side of the OptionGroup Control.
The displayed freight value's category description, defined in the Cap() Array is picked using the selected OptionGroup Control's Radio Button Index Number. This text is passed over to the Animate() Subroutine as the parameter. This is displayed on the Label control Caption at the left side of the TextBox Rslt. The Text is displayed in an animated style, exposing the description character by character moving from right to left till the full text is exposed.
The statement If strText = Cap(n) Then Exit Sub ignores the Animation from repeating when Clicked on the Body of the OptionGroup Frame.
The Label Animation.
The Animate() Subroutine Code segment is given below:
Private Sub Animate(ByVal txt As String) 'Label Animation Dim L As Double Dim n As String Dim T As Double Dim j As Integer L = Len(txt) txt = Space(L) & txt 'Add spaces at the left side For j = 1 To Len(txt) - L n = Left(txt, 1) txt = Right(txt, Len(txt) - 1) txt = txt & n frm.lblResult.Caption = Left(txt, L) Delay 0.02 ' Pause 20 Milliseconds Next End Sub
The length of the Parameter value in the Variable txt is calculated and stored in variable L. The parameter variable txt content is modified by adding an equal number of spaces of its original length at the left side.
The Animation Sequence.
The For . . . Next Loop is set to run to the original length in Variable L. In the next three lines of Code, remove one character from the left side of the String and add it to the right end of the String. Then the leftmost L length of characters is displayed in the Caption Property of the lblResult Label Control.
The next batch of characters display is delayed by 20 milliseconds and the same action is repeated till the complete description is displayed on the Label Control.
The Delay() Function VBA Code in the Standard Module is given below for information:
Public Sub Delay(ByVal Sleep As Double) Dim T As Double T = Timer Do While Timer < T + Sleep DoEvents Loop End Sub
The OptObject_Init Class Module VBA Code.
Option Compare Database Option Explicit Private iFrm As Access.Form Private LstB As OptListBox Private txt As OptTextBox Private Fram As OptFrame Private wcbo As optCombo Private wcmd As OptCmdButton Private Coll As New Collection '------------------------------------------------------ 'Streamlining Form Module Code 'in Stand-alone Class Modules '------------------------------------------------------ 'Combo and Option Group Controls 'Author: a.p.r. pillai 'Date : 31/08/2023 'Rights: All Rights(c) Reserved by www.msaccesstips.com '------------------------------------------------------ 'Form's Property GET/SET Procedures Public Property Get m_Frm() As Form Set m_Frm = iFrm End Property Public Property Set m_Frm(ByRef mfrm As Form) Set iFrm = mfrm iFrm.cboEmp.DefaultValue = iFrm.cboEmp.Column(0, 1) iFrm.List0.Requery Call Class_Init End Property 'Events Enabling Subroutine Private Sub Class_Init() Dim ctl As Control Const EP = "[Event Procedure]" For Each ctl In iFrm.Controls 'Scan for Controls Select Case TypeName(ctl) Case "OptionGroup" Select Case ctl.Name Case "Frame7" 'Option Group Name Set Fram = New OptFrame 'Create Instance Set Fram.opt_Frm = iFrm 'Assign Form Object Set Fram.o_opt = ctl 'TextBox Fram.o_opt.OnClick = EP Coll.Add Fram 'Save EmpTextBox Class Set Fram = Nothing 'Erase temp Instance End Select Case "ComboBox" Set wcbo = New optCombo Set wcbo.cbo_Frm = iFrm Set wcbo.c_cbo = ctl wcbo.c_cbo.AfterUpdate = EP wcbo.c_cbo.OnGotFocus = EP wcbo.c_cbo.OnLostFocus = EP Coll.Add wcbo Set wcbo = Nothing Case "TextBox" Set txt = New OptTextBox Set txt.tx_Frm = iFrm Set txt.t_Txt = ctl txt.t_Txt.OnGotFocus = EP txt.t_Txt.OnLostFocus = EP Coll.Add txt Set txt = Nothing Case "ListBox" Set LstB = New OptListBox Set LstB.lst_Frm = iFrm Set LstB.m_Lst = ctl LstB.m_Lst.OnGotFocus = EP LstB.m_Lst.OnLostFocus = EP Coll.Add LstB Set LstB = Nothing Case "CommandButton" Set wcmd = New OptCmdButton Set wcmd.cmd_Frm = iFrm Set wcmd.c_cmd = ctl wcmd.c_cmd.OnClick = EP Coll.Add wcmd Set wcmd = Nothing End Select Next End Sub Private Sub Class_Terminate() 'Delete Collection Object contents Do While Coll.Count > 0 Coll.Remove 1 Loop Set iFrm = Nothing End Sub
As usual, all the Access Object Wrapper Classes are defined as Properties in the Global Area of the Module followed by the Property Procedures. After the Form Object is assigned to the iFrm object, in the Set m_frm() Property Procedure two statements are executed for refreshing the ComboBox and ListBox objects to initialize with default values in both these Form Controls. From there the Class_Init() Subroutine is called.
The procedures written there are explained in detail in earlier episodes and I am sure you are well versed in the procedure.
But, I would like to draw your attention to the controls on the Form. There is only one instance each of Combobox, ListBox, TextBox, Command Button, OptionGroup Control, and Chart Object on the Form. Even though Chart Object also has several Events and can have a Wrapper Class, if we plan to capture those Events in a standalone Class Module.
When to create a Wrapper Class?
There may be more than one instance of a particular object of the same type on the Form (like TextBox) but if there is only one instance of any Control, that needs one or more Event Subroutines, then it is not necessary to use the Wrapper Class for it. We can create a single Instance of such Objects in the Declaration area of the OptObject_Init Class Module (the intermediate Class Module), qualified with the WithEvents keyword. Then assign the Control instances references from the Form, enable their Events, and write the Event Subroutines in the OptObject_Init Class Module.
We need a Wrapper Class only when more than one instance of the Objects of the same Type needs to be enabled with the Event Procedures. In this frm_OptionGroup Form, there is only one Instance of the TextBox, ComboBox, ListBox, and Command Button on the Form.
Since the OptionGroup Control (Frame7) is the new entry, in the Streamlining of Form Module Code Series of Articles, we created a Wrapper Class OptFrame for that control alone, and for others we will create a single instance of each control qualified with the WithEvents keyword, to capture the Events in the OptObject_Init Class Module. They will be enabled with their required Events and write their Event Subroutines in this Intermediate Class Module.
Even though we have already created and used Wrapper Classes for these Objects earlier, using them in this case involves more VBA Code than necessary for a single instance for those Objects.
So, I created two Forms for Demo purposes:
frm_OptionGroup - All Control's Wrapper Classes are used in the OptObject_Init Class
frm_OptionGroup2 - Only Opt_Frame Wrapper Class is in the Opt_Object_Init2 Class.
The Opt_Object_Init2 Class Module Code.
The frm_OptionGroup2 Form's Intermediate Class Module (Opt_Object_Init2) Code is given below:
Option Compare Database Option Explicit Private WithEvents txt As Access.TextBox Private WithEvents cmd As Access.CommandButton Private WithEvents cbo As Access.ComboBox Private WithEvents Lst As Access.ListBox Private Fram As Opt_Frame2 Private iFrm As Access.Form Private Coll As New Collection '------------------------------------------------------ 'Streamlining Form Module Code 'in Stand-alone Class Modules '------------------------------------------------------ 'Combo and Option Group Controls 'Author: a.p.r. pillai 'Date : 31/08/2023 'Rights: All Rights(c) Reserved by www.msaccesstips.com '------------------------------------------------------ 'Form's Property GET/SET Procedures Public Property Get m_Frm() As Form Set m_Frm = iFrm End Property Public Property Set m_Frm(ByRef mfrm As Form) Set iFrm = mfrm iFrm.cboEmp.DefaultValue = iFrm.cboEmp.Column(0, 1) iFrm.List0.Requery Set txt = iFrm.Result Set cmd = iFrm.cmdClose Set cbo = iFrm.cboEmp Set Lst = iFrm.List0 Call Class_Init End Property 'Events Enabling Subroutine Private Sub Class_Init() Dim ctl As Control Const EP = "[Event Procedure]" 'Scan for Form Controls 'and Enable the required Event Procedures For Each ctl In iFrm.Controls 'Find TextBox, ComboBox & CommandButtons Select Case TypeName(ctl) Case "OptionGroup" Select Case ctl.Name Case "Frame7" 'Option Group Name Set Fram = New Opt_Frame2 'Create Instance Set Fram.opt_Frm = iFrm 'Assign Form Object Set Fram.o_opt = ctl 'TextBox Fram.o_opt.OnClick = EP Coll.Add Fram 'Save EmpTextBox Class Set Fram = Nothing 'Erase temp Instance End Select Case "CommandButton" cmd.OnClick = EP Case "ComboBox" cbo.AfterUpdate = EP cbo.OnGotFocus = EP cbo.OnLostFocus = EP Case "TextBox" Select Case ctl.Name Case "Result" txt.OnGotFocus = EP txt.OnLostFocus = EP End Select Case "ListBox" Lst.OnGotFocus = EP Lst.OnLostFocus = EP End Select Next End Sub Private Sub Class_Terminate() 'Delete Collection Object contents Do While Coll.Count > 0 Coll.Remove 1 Loop Set iFrm = Nothing End Sub 'Event Subroutines Private Sub txt_GotFocus() GFColor iFrm, txt 'Field Highlight End Sub Private Sub txt_LostFocus() LFColor iFrm, txt 'Field Highlight End Sub Private Sub cmd_Click() If MsgBox("Close " & iFrm.Name & " Form?", vbYesNo + vbQuestion, "cmd_Click") = vbYes Then DoCmd.Close acForm, iFrm.Name Exit Sub End If End Sub Private Sub cbo_GotFocus() GFColor iFrm, cbo 'ComboBox highlight 'Reset OptionGroup to default settings iFrm.Frame7 = Null 'Reset earlier selection of OptionGroup option iFrm!lblResult.Caption = "Result" iFrm.Result.Value = 0 End Sub Private Sub cbo_LostFocus() LFColor iFrm, cbo 'ComboBox highlight End Sub Private Sub cbo_AfterUpdate() iFrm.List0.Requery End Sub 'Event Subroutines Code Private Sub lst_GotFocus() GFColor iFrm, Lst 'ListBox highlight End Sub Private Sub lst_LostFocus() LFColor iFrm, Lst 'ListBox highlight End Sub
Segmentwise Review of the VBA Code.
The Global Declaration Code segment is given below for review:
Option Compare Database Option Explicit Private WithEvents txt As Access.TextBox Private WithEvents cmd As Access.CommandButton Private WithEvents cbo As Access.ComboBox Private WithEvents Lst As Access.ListBox Private Fram As Opt_Frame2 Private iFrm As Access.Form Private Coll As New Collection
All single object instance declarations are given at the global area of the Module, as we did at the beginning of this series of Tutorials.
Since OptionGroup Control is a new entry in this series its Wrapper Class is included here. The Form and Collection Object declarations come next. The Collection Object declaration is included only for the OptionGroup Control with the name Frame7.
The Property Procedure Segment.
Next, the iFrm's Property Procedure Code Segment is given below:
'Form's Property GET/SET Procedures Public Property Get m_Frm() As Form Set m_Frm = iFrm End Property Public Property Set m_Frm(ByRef mfrm As Form) Set iFrm = mfrm 'Set the ComboBox EmployeeID first item as default value iFrm.cboEmp.DefaultValue = iFrm.cboEmp.Column(0, 1) iFrm.List0.Requery 'Refresh the Order Details ListBox Set txt = iFrm.Result Set cmd = iFrm.cmdClose Set cbo = iFrm.cboEmp Set Lst = iFrm.List0 Call Class_Init End Property
In the Set m_Frm() Property Procedure the ComboBox and ListBox default values are assigned when the Form is open.
Next, all the single object references on the Form are assigned to the Objects declared in the global area and then Called the Class_Init() Subroutine.
The Class_Init() Subroutine.
'Events Enabling Subroutine Private Sub Class_Init() Dim ctl As Control Const EP = "[Event Procedure]" 'Scan for Form Controls 'and Enable the required Event Procedures For Each ctl In iFrm.Controls 'Find TextBox, ComboBox & CommandButtons Select Case TypeName(ctl) Case "OptionGroup" Select Case ctl.Name Case "Frame7" 'Option Group Name Set Fram = New Opt_Frame2 'Create Instance Set Fram.opt_Frm = iFrm 'Assign Form Object Set Fram.o_opt = ctl 'TextBox Fram.o_opt.OnClick = EP Coll.Add Fram 'Save EmpTextBox Class Set Fram = Nothing 'Erase temp Instance End Select Case "CommandButton" cmd.OnClick = EP Case "ComboBox" cbo.AfterUpdate = EP cbo.OnGotFocus = EP cbo.OnLostFocus = EP Case "TextBox" Select Case ctl.Name Case "Result" txt.OnGotFocus = EP txt.OnLostFocus = EP End Select Case "ListBox" Lst.OnGotFocus = EP Lst.OnLostFocus = EP End Select Next End Sub
The Class_Init() Subroutine starts with the usual way of scanning for the Controls and checks for the OptionGroup control with the name Frame7, enables the Click Event, and then adds it to the Collection Object.
Next, other Form Controls' references are already assigned in the Set m_frm() Property Procedure and are enabled with the Events. We will be writing the Event Subroutine Code in this Class Module itself.
Next, in the TextBox's case, we have another TextBox (EID) that is kept hidden on the Form. Even though it is hidden it will appear in the scanning cycle and it will be enabled with the Events. Since there is no Event Procedure Code for that TextBox in this Module it will not have any impact. But, we would like to check for that specific TextBox (Resul) for clarity and enable its Events. This will ignore the EID TextBox. Next, the ListBox is also enabled with the required Events.
The Event Subroutines of Single Control Instance Cases.
Next, the Event Subroutines Segment Code which runs in the Opt_Object_Init2 Class.
'Event Runs automatically when the Form is Closed. Private Sub Class_Terminate() 'Delete Collection Object contents Do While Coll.Count > 0 Coll.Remove 1 Loop Set iFrm = Nothing End Sub 'TextBox Event Subroutines for highlighting the control Private Sub txt_GotFocus() GFColor iFrm, txt 'Field Highlight End Sub Private Sub txt_LostFocus() LFColor iFrm, txt 'Field Highlight End Sub 'Command Button Subroutines Private Sub cmd_Click() If MsgBox("Close " & iFrm.Name & " Form?", vbYesNo + vbQuestion, "cmd_Click") = vbYes Then DoCmd.Close acForm, iFrm.Name Exit Sub End If End Sub 'ComboBox Subroutines Private Sub cbo_GotFocus() GFColor iFrm, cbo 'ComboBox highlight 'Reset OptionGroup to default settings iFrm.Frame7 = Null 'Reset earlier selection of OptionGroup option iFrm!lblResult.Caption = "Result" iFrm.Result.Value = 0 End Sub Private Sub cbo_LostFocus() LFColor iFrm, cbo 'ComboBox highlight End Sub Private Sub cbo_AfterUpdate() iFrm.List0.Requery End Sub 'ListBox Event Subroutines Code Private Sub lst_GotFocus() GFColor iFrm, Lst 'ListBox highlight End Sub Private Sub lst_LostFocus() LFColor iFrm, Lst 'ListBox highlight End Sub
All Event Subroutines are written with the Object Name declared in the Global Declaration Area in the Opt_Object_Init2 Class Module.
Demo Database Download
Streamlining Form Module Code in Standalone Class Module.
- Reusing Form Module VBA Code for New Projects.
- Streamlining Form Module Code - Part Two.
- Streamlining Form Module Code - Part Three
- Streamlining Form Module Code - Part Four
- Streamlining Form Module Code - Part Five
- Streamlining Form Module Code - Part Six
- Streamlining Form Module Code - Part Seven
- Streamlining Form Module Code - Part Eight
- Streamlining Form Module Code - Part Nine
- Streamlining Form Module Code - Part Ten
- Streamlining Form Module Code - Part Elevan
- Streamlining Report Module Code in Class Module
- Streamlining Module Code Report Line Hiding-13.
- Streamlining Form Module Code Part-14.
- Streamlining Custom Made Form Wizard-15.
- Streamlining VBA Custom Made Report Wizard-16.
- Streamlining VBA External Files List in Hyperlinks-17
- Streamlining Events VBA 3D Text Wizard-18
- Streamlining Events VBA RGB Color Wizard-19
- Streamlining Events Numbers to Words-20
- Access Users Group(Europe) Presentation-21
- The Event Firing Mechanism of MS Access-22
- One TextBox and Three Wrapper Class Instances-23
- Streamlining Code Synchronized Floating Popup Form-24
- Streamlining Code Compacting/Repair Database-25
- Streamlining Code Remainder Popup Form-26
- Streamlining Code Editing Data in Zoom-in Control-27
- Streamlining Code Filter By Character and Sort-28
- Table Query Records in Collection Object-29
- Class for All Data Entry Editing Forms-30
- Wrapper Class Module Creation Wizard-31
- wrapper-class-template-wizard-v2
Hi a.p.r. pillai, I can't download the Streamline11.zip file. I think the link is wrong.
ReplyDeleteThanks in advanced.
Send your email address to this Email-ID: aprpillai@gmail.com. I will forward the Zip file to you. I checked the link it is working for me.
ReplyDeleteI also cannot download the file as I receive this message
ReplyDeleteSorry, the file you have requested does not exist.
I can't download the Streamline11.zip Please Update The Link
ReplyDeleteSorry for the inconvenience. Correct Link now updated. Thank you.
ReplyDeleteI have a question about this article
ReplyDeletehttps://www.msaccesstips.com/2018/12/wrapper-class-functionality.html
I could not add a comment to it
Please advise me of a way through which I can send my question