Wrapper Class Module Creation Wizard ~ LEARN MS-ACCESS TIPS AND TRICKS

Streamlining Form Module Code in Standalone Class Module.

Wrapper Class Module Wizard.

We organize controls on the form into groups based on their type, such as TextBox/Field, CommandButton, or ComboBox, and create separate wrapper class modules for each group to handle their event subroutine codes. All class modules follow a similar structure, with declarations for the Form and Control objects in the global area, and followed by property procedures for these objects.

Event Subroutines come next and need the correct TextBox/Field Names from the form for writing the Code below each Case statement within a Subroutine. Memorizing control names accurately is challenging, making it necessary to frequently refer to the Property sheet of the controls on the form. This process involves repeated back-and-forth navigation between the class module and the form's design view.

The ClassWizard Form Image.

When the Database's Form is open, we normally scan it for various types of controls, as part of the new Streamlined Event Subroutine Coding Procedure and can easily save the Control Names into Text files on the disk. Separate Text Files can be created by control type groups, like; with TextBox names in one file, CommandButton names in another, and ComboBox names in their own separate text file. This method simplifies referencing control names and reduces the need to switch back and forth between the Class Module and the form's design view.

So, we have now the following necessary elements for building the ClassWrapper Class Templates with Code:

Global Declaration of Objects.
The Standard Property Procedures for the declared Objects.
The Control Names, are collected from the Form in a Text File in Disk. We can build a single sample Subroutine structure, with all the Field Names placed within the Select Case Statements.

Once the Wrapper Class Module Template (ClassTextBox_Template) is created with a sample BeforeUpdate() event subroutine Code using the Class Wizard, you can copy and paste the BeforeUpdate() event subroutine structure for other events, such as GotFocus() or LostFocus(), by modifying their event names. You can then write the specific code for these events. Unwanted field names can be removed from the case statements to ensure the code remains clean and relevant.

The Wizard's input file, TextBoxFields.txt, contains field names from the Employees table. These names are saved when the Employee Form is open and run the Class_Init() subroutine in the Class_ObjInit Class Module. Below is an example of this input file for the Class Wizard Program.

TextBoxFields.txt File contents.

ID
Company
Last Name
First Name
E-mail Address
Job Title
Address
City
State/Province
ZIP/Postal Code
Country/Region

The above Field List File is created from the Employees Form's Class_Init() Subroutine in the Class_ObjInit Intermediary Class Module.

The Employees Form Image is given below, with some not-in-use CommandButtons and ComboBoxes added for trial run purposes.

The new Class_Init() Subroutine Code of Employees Form with the Text File creation Code is listed below.

Private Sub Class_Init()
Dim ProjectPath As String
Dim txtPath As String
Dim cmdPath As String
Dim ComboPath As String
Dim ctl As Control

On Error GoTo ClassInit_Err

Const EP = "[Event Procedure]"

'Save TextBox, CommandButton & CombBox Names
'to the Text Files for creating Event Subroutine Template
ProjectPath = CurrentProject.Path & "\"

txtPath = ProjectPath & "TextBoxFields.txt"
cmdPath = ProjectPath & "CmdButtonsList.txt"
ComboPath = ProjectPath & "ComboBoxList.txt"

If Len(Dir(txtPath)) > 0 Then 'delete earlier file
  Kill txtPath
End If

If Len(Dir(cmdPath)) > 0 Then 'delete earlier file
  Kill cmdPath
End If

If Len(Dir(ComboPath)) > 0 Then 'delete earlier file
  Kill ComboPath
End If

Open txtPath For Output As #1 'TextBoxFields.txt for writing
Open cmdPath For Output As #2 'CmdButtonsList.txt
Open ComboPath For Output As #3 'ComboBoxList.txt

'Instantiate the 'Data_txtBox' Class for each TextBox
'on the Form for streamlined Event Procedures Coding
For Each ctl In Frm.Controls
    Select Case TypeName(ctl)
        Case "TextBox"
            Set txt = New Data_TxtBox
            Set txt.m_Frm = Frm
            Set txt.m_txt = ctl
    Print #1, ctl.Name 'write Field/TextBox Name in TextBoxFields.txt File
            
'//Colin Riddington Technique: Highlighting BackColor on GotFocus
                txt.m_txt.BackColor = 62207 'yellow
                txt.m_txt.BackStyle = 0
    
                txt.m_txt.BeforeUpdate = EP
                txt.m_txt.OnDirty = EP
                
                Coll.Add txt 'Add Data_TxtBox Class Instance to Collection
                Set txt = Nothing 'Reset txt object
      
      Case "CommandButton"
            Set cmd = New Data_CmdButton
            Set cmd.Obj_Form = Frm
            Set cmd.cmd_Button = ctl
    Print #2, ctl.Name 'write CmdButton Name in CmdButtonsList.txt File
                
                cmd.cmd_Button.OnClick = EP
                
                Coll.Add cmd
                Set cmd = Nothing
                
      Case "ComboBox"
            Set Cbo = New Data_CboBox
            Set Cbo.m_Frm = Frm
            Set Cbo.m_Cbo = ctl
    Print #3, ctl.Name 'write ComboBox Names in ComboBoxList.txt File
    
'//Colin Riddington Technique: Highlighting BackColor on GotFocus
                Cbo.m_Cbo.BackColor = 62207
                Cbo.m_Cbo.BackStyle = 0
                
                Cbo.m_Cbo.BeforeUpdate = EP
                Cbo.m_Cbo.OnDirty = EP
                
                Coll.Add Cbo
                Set Cbo = Nothing
    End Select
Next
'Close all the three files
Close #1
Close #2
Close #3

ClassInit_Exit:
Exit Sub

ClassInit_Err:
MsgBox Err & ": " & Err.Description, , "Class_Init()"
Resume ClassInit_Exit
End Sub

The BASIC Language Text File Creation, Writing/Reading Statements:

Public Sub sub writeText()
Dim strItem As String
strItem = "www.msaccesstips.com"

Open "C:\myTextFile.txt" For Output As #1 'Open file in Output/writing Mode
   Print #1, strItem
Close #1

End Sub

Read the Text Data into a Variable

Public Sub ReadText()
dim strItem as String

Open "C:\myTextFile.txt" For Input As #1 'Open File in Input/Reading Mode
   Input #1, strItem
   debug.print strItem
Close #1

End Sub

The 'As #1' part indicates that this file is the first file in the Open state. If another file is to be opened for Writing/Reading simultaneously, then that will be 'As #2'.

The sample 'ClassTextBox_Template' VBA Code generated by the ClassTemplateWizard is listed below for reference.

The ClassTextBox_Template.

Option Compare Database
Option Explicit
 
Private WithEvents TextB As Access.TextBox
Private Frm As Access.Form
 
Public Property Get Obj_Form() As Form
   Set Obj_Form = Frm
End Property
 
Public Property Set Obj_Form(ByRef objForm As Form)
   Set Frm = objForm
End Property
 
Public Property Get Text_Box() As TextBox
   Set Text_Box = TextB
End Property
 
Public Property Set Text_Box(ByRef objTextB As TextBox)
   Set TextB = objTextB
End Property
 
Private Sub TextB_BeforeUpdate(Cancel As Integer)
   Select Case TextB.Name
     Case "ID"
        ' Code
 
     Case "Company"
        ' Code
 
     Case "Last Name"
        ' Code
 
     Case "First Name"
        ' Code
 
     Case "E-mail Address"
        ' Code
 
     Case "Job Title"
        ' Code
 
     Case "Address"
        ' Code
 
     Case "City"
        ' Code
 
     Case "State/Province"
        ' Code
 
     Case "ZIP/Postal Code"
        ' Code
 
     Case "Country/Region"
        ' Code
 
   End Select
End Sub

The ClassTemplateWizard.accdb database has the Class Wizard Form given on the top of this Page. You must attach this Database as a Library database to your Current Project. After that, run the Public Function OpenClassWizard() by typing it in the Debug Window directly or run it from a Command Button Click Event Subroutine.

After attaching the ClassTemplateWizard.accdb as Library Database, through the Tools --> References... Option, see that it is selected from the List of Library Files. Then go through the following Steps to create the Class Template File on Disk.

1. Open the Form (for example the Employees Form) and close the Form after a few seconds. The Class_Init() Subroutine creates the TextBoxFields.txt with the TextBox Names in it, the CmdButtonList.txt, and the ComboBoxList.txt Files with their list of names in the same Folder of your Database. These Text File contents are overwritten whenever a new Form is open.

2. Run the OpenClassWizard() Function from the Debug Window. The Wizard Form opens behind the VBA Window. Minimize the VBA Window to view the Form.

3. Select the TextBoxFields.txt from the left-side ListBox in the Wizard input Form.

4. Select the ClassTextBox_Template.cls item from the right-side ListBox.

5. Click on the Command Button with the Caption Run Wizard.

6. You will be greeted with a message announcing that the ClassTextBox_Template.cls File is created in your Project's Folder.

7. Right-click on the Navigation Pane of the VBA Window, near the Class Modules List of your own Project (not in the Class Module List of the attached Wizard database's Modules), and select the Import File... Option.

8. Browse for the File ClassTextBox_Template.cls in your Current Project Folder and click Open.

The ClassTextBox_Template Class is created among the Class Modules List in the VBA Navigation Pane.

Even though the Class Module File ClassTextBox_Template.cls is created for a specific Form it can be imported for another Form or into a new Project and modified for new Form(s) by changing the TextBox Names.

At this point naturally, a question comes up in one's mind why can't we create the Class Module Template in the Current Database directly?

I initially designed a Wizard to save the Template code into an existing Class Module by overwriting its contents whenever a new Form is open followed by running the Wizard. However, it has some side effects like the Wizard Form hangs, when the Class Module content is modified while the Wizard Form is in the running mode.

I will find a workaround to this problem and bring it out as Version 2.0 of this Wizard, probably in the next Episode.

In the meantime let us use this feature to speed up our project development time with the new way of Coding by Streamlining the Form Module Event Subroutine Code in the Standalone Class Module.

You can view the Wizard Functions in the Standard Module of the ClassTemplateWizard.accdb. The database ClassLibrary_Main.accdb for test running the Wizard.