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 the controls on a form into groups based on their type—such as TextBox/Field, CommandButton, or ComboBox—and create separate wrapper class modules for each group to manage their event subroutine code. Each class module follows a consistent structure, starting with declarations for the form and control objects in the global area, followed by the corresponding property procedures.

The event subroutines come next and require the correct TextBox/Field names from the form to write the code under each Case statement within a subroutine. Accurately memorizing control names can be difficult, which often forces developers to repeatedly consult the property sheet of the controls. This results in constant switching between the class module and the form’s design view, making the process tedious and time-consuming.

The ClassWizard Form Image.

Right-click to open Large Image in New Tab/Window.

When the database form is opened, we typically scan it for different types of controls as part of the new Streamlined Event Subroutine Coding procedure. The control names can then be saved into text files on disk, organized by control type—for example, TextBox names in one file, CommandButton names in another, and ComboBox names in a separate file. This approach makes it easier to reference control names and minimizes the need to constantly switch between the class module and the form’s design view.

So, we now have 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 and written into a Text File on Disk. We can build a single Subroutine structure, with all the Field Names placed within the Select Case Statements.

Once the Wrapper Class Module Template (ClassTextBox_Template) is generated with a sample BeforeUpdate() event subroutine using the Class Wizard, you can reuse its structure for other events, such as GotFocus() or LostFocus(), by simply copying, pasting, and updating the event names. From there, you can add the specific code required for each event. Any unnecessary field names can be removed from the Case statements to keep the code clean and focused.

The Wizard’s input file, TextBoxFields.txt, stores the field names from the Employees table. These names are captured when the Employee Form is open and the Class_Init() subroutine runs in the Class_ObjInit Class Module. An example of this input file for the Class Wizard Program is shown below.

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.

Right-click to open Large Image in New Tab/Window.

The new Class_Init() Subroutine 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 contains the Class Wizard Form. An image of the Wizard Form is provided at the top of this page. To use it, you must first attach this database as a Library Database to your current project.

Once attached, you can run the Public Function OpenClassWizard() either by typing it directly in the Debug Window or by calling it from a Command Button’s Click Event Subroutine.

After attaching ClassTemplateWizard.accdb as a Library Database (via Tools → References...), ensure it is checked in the list of available library files.

Now, follow the steps below to create the Class Template file on disk:

Steps to Create the Class Template File

Open a Form (e.g., the Employees Form) and close it after a few seconds.
- The Class_Init() Subroutine creates three text files in your database folder:
  - TextBoxFields.txt – list of TextBox names
  - CmdButtonList.txt – list of CommandButton names
  - ComboBoxList.txt – list of ComboBox names
- These files are automatically overwritten each time a new form is opened.
Run the OpenClassWizard() Function from the Debug Window.
- The Wizard Form opens behind the VBA window. Minimize the VBA window to see it.
Select TextBoxFields.txt from the left-hand ListBox in the Wizard input Form.
Select ClassTextBox_Template.cls from the right-hand ListBox.
Click the Run Wizard button.
A confirmation message will appear, informing you that ClassTextBox_Template.cls has been created in your project’s folder.
In the VBA editor, right-click the Navigation Pane (near your project’s Class Modules list, not the attached Wizard database’s list) and choose Import File....
Browse to your project folder, select ClassTextBox_Template.cls, and click Open.

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

Even though the ClassTextBox_Template.cls file is generated for a specific form, it can also be reused in other forms or imported into a new project. To adapt it for a different form, simply update the TextBox names accordingly.

The Wizard functions are available in the Standard Module of the ClassTemplateWizard.accdb database. For testing purposes, you can use the sample database ClassLibrary_Main.accdb.