LEARN MS-ACCESS TIPS AND TRICKS

Wrapper Class Template Wizard V2

a.p.r. pillaiJune 29, 2024 No comments

Streamlining Form Module Code in Standalone Class Module.

Class Module Template Creation Wizard.

The earlier version of the Class Module Wizard was a testing platform for creating Wrapper Class Module templates. Although it served its purpose, the procedure adopted there is somewhat semi-automatic, and I am not satisfied with that either.

This improved Version 2.0 of the Wizard can create several Class Module Templates for different Object Types in your form. This Version creates about 10 frequently used Objects Wrapper Class Module Templates, based on your selection of choices at a time.

The Screenshot of the Class Template Wizard is given below:

The ListBox's Source Data is from the 'ListItems' Table. The Source Table Image is given below.

The Table has ten Records with six columns of data, but only the first three columns are shown on the ListBox.

(1) The RecordID - for easier retrieval of Wizard Function Parameters.
(2) the Field/Control list File Name for creating on Disk - The files will be created in the Current Project Folder.
(3) the Class Module Template Name - suffixed with the word '_Template' is preferred, not mandatory.

The following three Columns of data are used by the Wizard.

(4) The Wizard Function that creates the Class Template - Applicable for all Types of Objects.
(5) The Object TypeName - self-explanatory.
(6) Object Short Name - you may change it to a more descriptive Name if required.

How to Run the WrapperClassWizard from within a Database?

Place the WrapperClass_Wizard.accdb database into a Trusted Folder.

Open your Database and open the VBE Window.

Select References... from the Tools menu, find the Wizard Database, attach, and select to add it to the selected List of Library Files.

Create a SELECT Query in your Project with the name ListItemsQ using the following SQL:

SELECT ListItems.*
FROM ListItems IN 'D:\DEMO\Code2\WrapperClass_Wizard.accdb';

Change the Folder Path to the location of the WrapperClass_Wizard.accdb correctly.

You can create class module templates with the main object declarations (e.g., TextBox and Form, or any other Control and Form) with their property procedures for up to 10 of the most frequently used controls on the form.

Additionally, you can add a sample Click Event Subroutine Code foundation with all the Data Field Names (eg: TextBox and ComboBox Names) collected from the Form. To achieve this, insert a few lines of code (shown in red) in the Class_Init() Subroutine listing below, to collect each Control Type in separate Groups from the Form and save them into their designated text files on disk, when that Form is in open state.

Note: If you plan to implement this Data Field Name saving procedure for two or more Forms these text file contents on Disk will be overwritten with the last open Form's Field Names.

For creating the TextBox or ComboBox sample Click Event Subroutine for a particular Form, open the Form with its Class_Init() Subroutine Code Lines in red, for a few seconds, and close it before running the Class Template creation Wizard.

I recommend this procedure to collect the Data field names from the Form with the help of Code because all of them are inserted from the data source Table/Query, and memorizing all their names correctly for easier event subroutine coding is impractical. We need to refer to the data source Table/Query or check the Control Names on the Form, after opening the Form in Design View to pick their names correctly for use in the Code.

The Field Names writing Code lines (marked in Red) are inserted into the Class_Init() subroutine which writes the control names of each category into their respective designated text files on disk. Below is the Class_Init() Subroutine in the Class_ObjInit Class Module of Employees Form implemented with this procedure.

Option Compare Database
Option Explicit

Private txt As Data_TxtBox
Private Cbo As Data_CboBox
Private cmd As Data_CmdButton

Private Coll As New Collection
Private Frm As Form

'--------------------------------------------------------------------
'Streamlining Form Module Code
'in Stand-alone Class Modules
'With Reusable Code
'--------------------------------------------------------------------
'The 'WrapperClassWizard.accdb' attached to this Database
'Check: Tools--> References...
'--------------------------------------------------------------------
'1. The 'Class_ObjInit' Class saves TxtBox/Field Names, Command Button,
'ComboBox Names, and others from the Open Form into TextBoxFields.txt,
'CmdButtonList.txt & ComboBoxList.txt files in the same Database Folder.
'
'2. The 'WrapperClassWizard.accdb' must be attached to the
'Current Project to open up the Wizard Form by running
'the Function: OpenClassWizard() from within a Command button Click.
'
'3. The Wizard Reads the Field Names from tbe above Text Files.
'
'4. The Wizard will use the Field/Control Names(if any) for the sample
'Event Subroutine in the Template. If the file is empty then it will
'create a sample Evet Subroutine without the Control names.
'
'5. This saves Coding time otherwise spent on creating a
'New Wrapper Class Module from scratch and picking the
'Field Names and other Control Names correctly
'From the Form for Event Subroutines.
'--------------------------------------------------------------------
'Author:  a.p.r. pillai
'Date  :  20/06/2024
'Remarks: with Ready-made Data Entry Events Handler
'       : in the Wrapper TextBox Class Module
'       : Suitable for Columnar, Tabular or DataSheet Form
'Rights:  All Rights(c) Reserved by www.msaccesstips.com
'--------------------------------------------------------------------

Public Property Get o_Frm() As Form
    Set o_Frm = Frm
End Property

Public Property Set o_Frm(ByRef vFrm As Form)
    Set Frm = vFrm

    Class_Init
End Property

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

On Error GoTo ClassInit_Err

Const EP = "[Event Procedure]"

'Save TextBox & CombBox Names into separate Text Files
'for creating Event Subroutines in the Templates
ProjectPath = CurrentProject.Path & "\"

FieldListFile = DLookup("FieldListFile", "ListItemsQ", "ID = " & wizTextBox)
ComboBoxList = DLookup("FieldListFile", "ListItemsQ", "ID = " & wizComboBox)

txtPath = ProjectPath & FieldListFile
ComboPath = ProjectPath & ComboBoxList

Open txtPath For Output As #1 'TextBoxFields.txt for writing
Open ComboPath For Output As #2 '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"
    Print #1, ctl.Name 'write Field/TextBox Name in TextBoxFields.txt File
            Set txt = New Data_TxtBox
            Set txt.m_Frm = Frm
            Set txt.m_txt = ctl
            
'//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
                
                cmd.cmd_Button.OnClick = EP
                
                Coll.Add cmd
                Set cmd = Nothing
                
      Case "ComboBox"
    Print #2, ctl.Name 'write ComboBox Names in ComboBoxList.txt File
            Set Cbo = New Data_CboBox
            Set Cbo.m_Frm = Frm
            Set Cbo.m_Cbo = ctl
    
'//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
                
        Case "ListBox"
            
    End Select
Next
'Close all files
Close #1
Close #2

ClassInit_Exit:
Exit Sub

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

Private Sub Class_Terminate()
While Coll.Count > 0
    Coll.Remove 1
Wend
Set Coll = Nothing

End Sub

The red lines are inserted in the required locations to collect the Data Field and ComboBox Names and record them in separate Text Files in the Project Folder as Input to the Class Module creation Wizard. Similarly, you can insert related Code lines for other Types of Controls on the Form.

The Wizard will look for these files on the disk and check their contents. If control names are found, they will be used for building a sample Click Event Subroutine after the main object declarations and property procedure lines of code.

If the above lines are embedded in the Class_Init() subroutine, we should open and keep the Employees Form for a few seconds and close it before running the wizard options. This will create the controls list in their respective text files, using the predefined file names taken from the wizard table ListItems, through the Select Query ListItemsQ.

The TextBoxFields.txt File contents created from the Employee Form Field Names are listed below for reference:

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

The Sample Click Event Subroutine Structure Code.

The sample Click Event Subroutine Code structure, created using the Field Names from the TextBoxFields.txt File is given below.

Private Sub txtBox_Click()
   Select Case txtBox.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

Remove the unwanted Control Names from the List, if no Events are invoked from those controls.

Opening the ClassWizard Form.

Open the ClassWizard Form, by calling the Function OpenClassWizard() from your Project through a Command Button Click Event Subroutine, with the following sample VBA Code lines:

Private Sub Command25_Click()

    VBE.CodePanes.Item(1).show

    OpenClassWizard VBE.ActiveVBProject.Name
End Sub

The OpenClassWizard() Function Code is given below.

Note: You should not create a Function with the same name: OpenClassWizard() in your Project, where the Wizard is attached as a Library database.

Dim ProjectName As String

'Public Function to Open the Wizard Form
'from the Host Application
Public Function OpenClassWizard(ByVal projName As String)
On Error GoTo OpenClassWizard_Err

ProjectName = projName
DoCmd.OpenForm "ClassWizard", acNormal
    
OpenClassWizard_Exit:
Exit Function

OpenClassWizard_Err:
MsgBox Err & ": " & Err.Description, , "OpenClassWizard()"
Resume OpenClassWizard_Exit
End Function

When the Wizard Form is open in your Application Window select one or more items from the List of Control Types, like TextBox, Command Buttons, or any other Classs Module you need, and Click the Run Wizard Command Button.

The ClassWizard Form Module Code:

Option Compare Database
Option Explicit

Private obj As New Wiz_ObjInit

Private Sub Form_load()
    Set obj.O_Form = Me
End Sub

Private Sub Form_Unload(Cancel As Integer)
    Set obj = Nothing
End Sub

The Wiz_ObjInit Intermediary Class Module VBA Code.

Option Compare Database
Option Explicit

Private Lst As Wiz_ListBox
Private oFrm As Access.Form
Private cmd As Wiz_CmdButton
Private Coll As New Collection
'----------------------------------------------------------
'Streamlining Form Module Code in Stand-alone Class Modules
'With Reusable Structured Event Subroutine Coding.
'----------------------------------------------------------
'Event Procedure Coding in Standalone Class Module is now
'made easier with Readymade Object Wrapper Class Templates
'Demo Event Subroutine is created with the Control Names.
'----------------------------------------------------------
'Program: Wrapper Class Template Wizard
'Author:  a.p.r. pillai
'Date  :  20/06/2024
'Rights:  All Rights(c) Reserved by www.msaccesstips.com
'----------------------------------------------------------

Public Property Get O_Form() As Form
   Set O_Form = oFrm
End Property
 
Public Property Set O_Form(ByRef objForm As Form)
   Set oFrm = objForm
   
   Class_Init
End Property

Private Sub Class_Init()
On Error GoTo Class_Init_Err
Dim ctl As Control
Const EP = "[Event Procedure]"
   
For Each ctl In oFrm.Controls
    Select Case TypeName(ctl)
        Case "ListBox"
            Set Lst = New Wiz_ListBox
            Set Lst.Obj_Form = oFrm
            Set Lst.Lst_Box = ctl
                Lst.Lst_Box.OnClick = EP
                
                Coll.Add Lst
            Set Lst = Nothing
            
        Case "CommandButton"
            Set cmd = New Wiz_CmdButton
            Set cmd.Obj_Form = oFrm
            Set cmd.Obj_cmdButton = ctl
                cmd.Obj_cmdButton.OnClick = EP
                
                Coll.Add cmd
            Set cmd = Nothing
            
    End Select
Next

Class_Init_Exit:
Exit Sub

Class_Init_Err:
MsgBox Err & ": " & Err.Description, , "Class_Init()"
Resume Class_Init_Exit
End Sub

Private Sub Class_Terminate()
    While Coll.Count > 0
        Coll.Remove 1
    Wend
    Set Coll = Nothing
    Set Lst = Nothing
    
End Sub

The Class_Init() Subroutine has only the ListBox and Command Button Control's Click Event to Enable.

The ListBox Wrapper Class 'Wiz_ListBox' Module VBA Code.

Option Compare Database
Option Explicit
 
Private WithEvents LstBox As Access.ListBox
Private Frm As Access.Form
'----------------------------------------------------------
'Streamlining Form Module Code in Stand-alone Class Modules
'With Reusable Structured Event Subroutine Coding.
'----------------------------------------------------------
'Event Procedure Coding in Standalone Class Module is now
'made easier with Readymade Object Wrapper Class Templates
'Demo Event Subroutine is created with the Control Names.
'----------------------------------------------------------
'Program: Wrapper Class Template Wizard
'Author:  a.p.r. pillai
'Date  :  20/06/2024
'Rights:  All Rights(c) Reserved by www.msaccesstips.com
'----------------------------------------------------------

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 Lst_Box() As Access.ListBox
   Set Lst_Box = LstBox
End Property
 
Public Property Set Lst_Box(ByRef objListB As Access.ListBox)
   Set LstBox = objListB
End Property

Private Sub LstBox_Click()
On Error GoTo LstBox_Click_Err
  Select Case LstBox.Name
    Case "List1"
        Dim lCount As Integer
        Dim tmpList As ListBox
        Dim j As Integer

        Set tmpList = Frm.List1
        lCount = tmpList.ListCount - 1

        For j = 0 To lCount
            If tmpList.Selected(j) Then
                Frm.cmdRun.Enabled = True
                Exit Sub
            Else
                Frm.cmdRun.Enabled = False
            End If
        Next

  End Select
  
LstBox_Click_Exit:
Exit Sub

LstBox_Click_Err:
MsgBox Err & ": " & Err.Description, , "LstBox_Click()"
Resume LstBox_Click_Exit
End Sub

The Click Event of the ListBox Control checks for the presence of any selected Items in the ListBox, if found then enables the Command Button with the Caption 'Run Wizard' otherwise the Command Button is disabled.

The Command Button Wrapper Class 'Wiz_CmdButton' Module Code.

Option Compare Database
Option Explicit
 
Private WithEvents cmdButton As Access.CommandButton
Private Frm As Access.Form
'----------------------------------------------------------
'Streamlining Form Module Code in Stand-alone Class Modules
'With Reusable Structured Event Subroutine Coding.
'----------------------------------------------------------
'Event Procedure Coding in Standalone Class Module is now
'made easier with Readymade Object Wrapper Class Templates
'Demo Event Subroutine is created with the Control Names.
'----------------------------------------------------------
'Program: Wrapper Class Template Wizard
'Author:  a.p.r. pillai
'Date  :  20/06/2024
'Rights:  All Rights(c) Reserved by www.msaccesstips.com
'----------------------------------------------------------

Public Property Get Obj_Form() As Access.Form
   Set Obj_Form = Frm
End Property
 
Public Property Set Obj_Form(ByRef objForm As Access.Form)
   Set Frm = objForm
End Property
 
Public Property Get Obj_cmdButton() As Access.CommandButton
   Set Obj_cmdButton = cmdButton
End Property
 
Public Property Set Obj_cmdButton(ByRef vcmdButton As Access.CommandButton)
   Set cmdButton = vcmdButton
End Property
 
Private Sub cmdButton_Click()
On Error GoTo cmdButtonClick_Err
   Select Case cmdButton.Name
     Case "cmdClose"
        DoCmd.Close acForm, "ClassWizard"
        
        
     Case "cmdRun"
        Dim modul As Module
        Dim flag As Boolean
        Dim vbcompo As vbcomponent
        Dim tmpList As ListBox
        Dim wiz As Integer
        Dim strWiz As String

        Dim FunctionName As String
        Dim FieldListFile As String
        Dim ClsTemplate As String
        Dim s_ObjTypeName As String
        Dim s_ObjName As String
        
        Dim msg As String
        Dim lCount As Integer
        Dim j As Integer, k As Integer
        Dim qot As String
        Dim objType As Long
        Dim Dt As Double
        Dim ClsSourceFile As String
        Dim ProjectName As String
        Dim Result As Boolean
        
        qot = Chr(34)
        Set tmpList = Frm.List1
        lCount = tmpList.ListCount - 1
        k = 0

        'Validation Check
        msg = ""
        For j = 0 To lCount
          If tmpList.Selected(j) Then
                'FieldList File
                FieldListFile = CurrentProject.Path & "\" & tmpList.Column(1, j)
        
                'Chek for FieldList File on Disk
                    If Len(Dir(FieldListFile)) = 0 Then
                      Open FieldListFile For Output As #1
                        Print #1, Space(5)
                      Close #1
                    End If
        
                'Class Template Name
                ClsTemplate = tmpList.Column(2, j)
                
                msg = ""
                FunctionName = tmpList.Column(3, j)
                If Len(Nz(FunctionName, "")) = 0 Then
                    FunctionName = "CreateClassTemplate"
                End If
                
                s_ObjTypeName = tmpList.Column(4, j)
                If Len(Nz(s_ObjTypeName, "")) = 0 Then 'Control Type Name column empty
                    msg = "*** Object TypeName not specified!"
                Else
                    objType = ControlTypeCheck(s_ObjTypeName) 'Is it a Valid Control TypeName
                    If objType = 9999 Then 'Name not in specified list
                        msg = "*** object Typename: " & UCase(s_ObjTypeName) & vbCr _
                        & "Not in specified List?"
                    End If
                End If
    
                s_ObjName = tmpList.Column(5, j)
                If Len(Nz(s_ObjName, "")) = 0 Then 'Column is empty
                    msg = msg & vbCr & vbCr & "User-Defined Object Name Column is Empty!"
                End If
                If Len(msg) > 0 Then 'Errors Found
                    msg = msg & vbCr & vbCr & "Errors Found in Item: " & tmpList.Column(0, j) & _
                    vbCr & "Rectify the Errors and Re-run!"
                    MsgBox msg, vbCritical + vbOKCancel, "cmdButton_Click()"
                    Exit Sub
                Else 'No Errors then creaate Template
                'Call the Wizard
Result = CreateClassTemplate(FieldListFile, ClsTemplate, s_ObjTypeName, s_ObjName)
                    If Not Result Then
                        MsgBox "Errors Encountered for '" & ClsTemplate & "'" & vbCr _
                        & "Review/Modify the Parameter value(s) and Re-try."
                    End If
                End If
    End If

Next j
        MsgBox "Class Module Templates Created successfully!"

        'Save created Template Class modules
        DoCmd.RunCommand acCmdCompileAndSaveAllModules

     Case "cmdHelp"
        DoCmd.OpenForm "Wiz_Help", acNormal
        
   End Select

cmdButtonClick_Exit:
Exit Sub

cmdButtonClick_Err:
MsgBox Err & ": " & Err.Description, , "cmdButtonClick()"
Resume cmdButtonClick_Exit
End Sub

It runs a series of checks on the Wizard parameters and Validates them before calling the CreateClassTemplate() Function with its parameters.

The Wizard will retrieve the Parameters for the selected items from the ListItems Table and check for the Field List/Control Files from the selected options on the disk. If found then retrieves the list of controls, if any, for the sample Event Subroutine.

After the validation check of all the parameter values, it calls the Wizard Function CreateClassTemplate() which creates the Class Module Templates, for the selected options, with the predefined names in your Project's VBE Window's Navigation Pane.

The CreateClassTemplate() Wizard Function VBA Code is given below:

Public Function CreateClassTemplate(ByVal FieldListFile As String, _
ByVal ClassTemplateName As String, ByVal strObjTypeName As String, _
ByVal strObjName As String) As Boolean

'----------------------------------------------------------
'Streamlining Form Module Code in Stand-alone Class Modules
'With Reusable Structured Event Subroutine Coding.
'----------------------------------------------------------
'Event Procedure Coding in Standalone Class Module is now
'made easier with Readymade Object Wrapper Class Templates
'Demo Event Subroutine is created with the Control Names.
'----------------------------------------------------------
'Program: Wrapper Class Template Wizard
'Author:  a.p.r. pillai
'Date  :  20/06/2024
'Rights:  All Rights(c) Reserved by www.msaccesstips.com
'----------------------------------------------------------

On Error GoTo CreateClassTemplate_Err
Dim j As Long, k As Long, h As Long, CountLines As Long
Dim low As Long, high As Long
Dim FieldList() As String
Dim strItem As Variant
Dim strLines(1 To 33) As String
Dim msg As String
Dim idx As Integer
Dim spacex As String
Dim qot As String
Dim ClsPath As String
Dim vbcompo As vbcomponent

spacex = Chr(32)
qot = Chr(34)
idx = 1

strLines(idx) = "VERSION 1.0 CLASS": GoSub NextIndex
strLines(idx) = "BEGIN": GoSub NextIndex
strLines(idx) = "  MultiUse = -1": GoSub NextIndex
strLines(idx) = "End": GoSub NextIndex
strLines(idx) = "Attribute VB_Name = " & qot & ClassTemplateName & qot: GoSub NextIndex
strLines(idx) = "Attribute VB_GlobalNameSpace = False": GoSub NextIndex
strLines(idx) = "Attribute VB_Creatable = False": GoSub NextIndex
strLines(idx) = "Attribute VB_PredeclaredId = False": GoSub NextIndex
strLines(idx) = "Attribute VB_Exposed = False": GoSub NextIndex
strLines(idx) = "Option Compare Database": GoSub NextIndex
strLines(idx) = "Option Explicit": GoSub NextIndex
strLines(idx) = spacex: GoSub NextIndex
strLines(idx) = "Private WithEvents " & strObjName & " as Access." & strObjTypeName: GoSub NextIndex
strLines(idx) = "Private Frm as Access.Form": GoSub NextIndex
strLines(idx) = spacex: GoSub NextIndex
strLines(idx) = "Public Property Get Obj_Form() as Access.Form": GoSub NextIndex
strLines(idx) = "   Set Obj_Form = Frm": GoSub NextIndex
strLines(idx) = "End Property": GoSub NextIndex
strLines(idx) = spacex: GoSub NextIndex
strLines(idx) = "Public Property Set Obj_Form(ByRef objForm as Access.Form)": GoSub NextIndex
strLines(idx) = "   Set Frm = objForm": GoSub NextIndex
strLines(idx) = "End Property": GoSub NextIndex
strLines(idx) = spacex: GoSub NextIndex
strLines(idx) = "Public Property Get Obj_" & strObjName & "() as Access." & strObjTypeName: GoSub NextIndex
strLines(idx) = "   Set obj_" & strObjName & " = " & strObjName: GoSub NextIndex
strLines(idx) = "End Property": GoSub NextIndex
strLines(idx) = spacex: GoSub NextIndex
strLines(idx) = "Public Property Set Obj_" & strObjName & "(ByRef v" & strObjName & " as Access." & strObjTypeName & ")": GoSub NextIndex
strLines(idx) = "   Set " & strObjName & " = v" & strObjName: GoSub NextIndex
strLines(idx) = "End Property": GoSub NextIndex
strLines(idx) = spacex: GoSub NextIndex
strLines(idx) = "Private Sub " & strObjName & "_Click()": GoSub NextIndex
strLines(idx) = "   Select Case " & strObjName & ".Name"

'Read the Field Names into Array
Open FieldListFile For Input As #1
strItem = ""
j = 0
While Not EOF(1)
    Input #1, strItem
 If Len(Trim(Nz(strItem, " "))) > 0 Then
    j = j + 1
    ReDim Preserve FieldList(1 To j) As String
    FieldList(j) = strItem
 End If
Wend
Close #1

If j > 0 Then 'If 0 then Field List File is empty
    low = LBound(FieldList)
    high = UBound(FieldList)
End If

'Write the Array contents to file
ClsPath = CurrentProject.Path & "\TempClass.cls"
Open ClsPath For Output As #1
For k = 1 To idx
    Print #1, strLines(k)
Next

'Subroutine Lines
If j > 0 Then 'if 0 then Field List file is empty
    For h = low To high
        Print #1, "     Case " & qot & FieldList(h) & qot
        Print #1, "        ' Code"
        Print #1, spacex
    Next
End If
    Print #1, spacex
    Print #1, "   End Select"
    Print #1, "End Sub"
    Print #1, spacex
Close #1

    ' Import the class module
Set vbcompo = Application.VBE.VBProjects(ProjectName).VBComponents.Import(ClsPath)
    
    ' Verify if the imported module is a class module
    'Compile and Save module
    If vbcompo.Type = vbext_ct_ClassModule Then
        CreateClassTemplate = True
        Kill ClsPath
    Else
        CreateClassTemplate = False
        MsgBox "Import failed: Not a class module."
    End If

CreateClassTemplate_Exit:
Exit Function

NextIndex:
idx = idx + 1
Return

CreateClassTemplate_Err:
MsgBox Err & ": " & Err.Description, , "CreateClassTemplate()"
Resume CreateClassTemplate_Exit
End Function

Check the VBE navigation pane for the newly created Template Files. They may not appear immediately in the Database navigation pane.

The Wizards's VBA Code is straightforward. The first 33 lines of Code are standard with the change of Object Names and Type declarations inserted at appropriate positions using the Parameters obtained from the ListItems Table.

Next, it checks for the Field Names list file (or the Control Type-related Text File Names given in the ListItems Table) in the TextBoxFieldsList.txt. If Control Names are present in the Text File they are loaded into an Array in memory.

After that, the first 33 VBA Code lines are written into the TempClass.cls Text File. If the Field List/Control Name lines are found in the Array it creates a sample Click Event Subroutine Code within the Select Case . . . Case . . . End Select structure for ready-to-write Event Procedure Code wherever applicable.

This sample Event Subroutine structure can be copied for other Event Procedures like BeforeUpdate(), GotFocus(), and others.

Finally, the TempClass.cls Text File is Imported into the VBE Project's Code Pane. Look for the Class Modules with the _Template Suffix to spot them quickly.

If the Import action is unsuccessful then it will display an Error Message. In that case, you should investigate and find the cause of the Error, correct it, and try again.

You may highlight one or more required Object Template Options from the ListBox and the Wizard creates them quickly. You can rename the Template Modules or Copy the generated Code from the Template Module and Paste it into a separate Class module for normal use. You may modify the Object Names part for use with other types of Control too.

Click the Help Command Button to display the Help Form in your Application Window. The Help Form Image is shown below.

The listItems Table Record ID Enumerations are given below.

Enum ParamID
    wizTextBox = 1
    wizCommandButton = 2
    wizComboBox = 3
    wizListBox = 4
    wizLabel = 5
    wizOptionGroup = 6
    wizOptionButton = 7
    wizCheckBox = 8
    wizToggleButton = 9
    wizTabControl = 10
End Enum

The Control Type Validating Function Code Listing.

Public Function ControlTypeCheck(ByVal strctl As String) As Long
On Error GoTo ControlTypeCheck_Err
Dim ctrlType(1 To 10) As String
Dim ctrl(1 To 10) As Long
Dim j As Integer

For j = 1 To 10
 Select Case j
    Case 1: ctrlType(j) = "Label": ctrl(j) = acLabel '100
    Case 2: ctrlType(j) = "CommandButton": ctrl(j) = acCommandButton '104
    Case 3: ctrlType(j) = "OptionButton": ctrl(j) = acOptionButton '105
    Case 4: ctrlType(j) = "CheckBox": ctrl(j) = acCheckBox '106 
    Case 5: ctrlType(j) = "OptionGroup": ctrl(j) = acOptionGroup '107 
    Case 6: ctrlType(j) = "TextBox": ctrl(j) = acTextBox '109
    Case 7: ctrlType(j) = "ListBox": ctrl(j) = acListBox '110
    Case 8: ctrlType(j) = "ComboBox": ctrl(j) = acComboBox '111
    Case 9: ctrlType(j) = "ToggleButton": ctrl(j) = acToggleButton '122 
    Case 10: ctrlType(j) = "TabControl": ctrl(j) = acTabCtl '123  
End Select
Next

For j = LBound(ctrlType) To UBound(ctrlType)
    If ctrlType(j) = strctl Then
        ControlTypeCheck = ctrl(j): Exit For
    Else
       ControlTypeCheck = 9999 'Error
    End If
Next

ControlTypeCheck_Exit:
Exit Function

ControlTypeCheck_Err:
MsgBox Err & ": " & Err.Description, , "ControlTypeCheck()"
Resume ControlTypeCheck_Exit
End Function

I hope you are enjoying the new approach to coding with reusable VBA code and easier code maintenance, without the need to constantly interfere with the Form Design View. The 'Streamlining Form Module Code in the Standalone Cass Module' also enhances code portability to other projects, offering a significant advantage.

Demo Databases Download

WrapperClass_Wizard.zip

Wrapper Class Module Creation Wizard

a.p.r. pillaiMay 29, 2024 No comments

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.

Demo Databases Download

ClassTemplateWizard.zip

Classes For All Data Entry Editing Forms

a.p.r. pillaiMay 16, 2024 No comments

Streamlining Form Module Code in Standalone Class Module.

Ready-made Class Modules for Data-Entry, Editing, or Viewing.

All the TextBox and ComboBox Controls, on the data handling Form, when enabled with the Dirty(), and BeforeUpdate() Event Procedures are fully protected from unintentional changes.

Manually writing code for every TextBox and ComboBox on a form can lead to duplication of work and inefficiency. Moreover, modifying event procedure names for each field to match the control name can become tedious and error-prone. Typically, only essential fields undergo this kind of data protection exercise, leaving others vulnerable.

To streamline this process, consider implementing a more automated or systematic approach, such as leveraging reusable code structures, like Standalone Class Modules, that help to centralize and organize Event Procedures more efficiently.

Utilizing a Class Module with VBA Coding enables a swift and automated solution to implement data protection methods across all fields on the form. By leveraging Events Dirty() and BeforeUpdate() Subroutine Code reuse techniques, these data-protecting procedures can be efficiently applied to all TextBoxes and ComboBoxes on the form. This streamlined approach ensures quick and consistent implementation of data protection measures, enhancing the overall robustness and security of the form's data handling processes.

With the following few Simple Steps let us do a Demo Run to understand how it works:

Use the Form-Wizard to create a Form: Columnar or Tabular Design using any Table/Query as a data source.
Open the Form in Design View.
Display the Form Property Sheet and Select the Other Tab.
Change the Has Module Property Value to Yes.
Display the Form Module.

Copy and Paste the following VBA Code into the Form Module.

Option Compare Database
Option Explicit

Private Cls As New Class_ObjInit

Private Sub Form_Load()
Set Cls.o_Frm = Me
End Sub

Private Sub Form_Unload(Cancel As Integer)
  Set Cls = Nothing
End Sub

Sample Form Image with the Employees Table.

Save your Form, but do not open it at this time. We need to create the Class Modules first.

There are three ready-made Class Modules, with a single prewritten OnDirty() and BeforeUpdate() Event Procedures. When the Form is open the Class_ObjInit Class Module is Instantiated in the global declaration area with the object name Cls and in the Form_Load() Event Procedure the Cls.o_Frm Property of the Class Module is assigned with the current Form Object Me.

When the Class_ObjInit Class Object is Instantiated the Data_TxtBox Class and the Data_CboBox Classes declared in the Class_ObjInit Class are also loaded into memory. We will create these Class Modules with the Event Subroutine Codes next.

Open the VBA Editing Window (ALT+F11)
Create a Class Module, Click on the Properties Window Icon (the icon between Project Explorer and Object Browser Icons) to display the Property Sheet of the Module. Change the Module Name from Class1 to Class_ObjInit.

Copy the VBA Code given below Paste them into the Class_ObjInit Class Module and Save them.

The Class_ObjInit Class Module.

Option Compare Database
Option Explicit

Private txt As Data_TxtBox
Private Cbo As Data_CboBox
Private Coll As New Collection
Private frm As Form
Private DetSec As Section

'------------------------------------------------------
'Streamlining Form Module Code
'in Stand-alone Class Modules
'With Reusable Code
'------------------------------------------------------
'Quick Data Entry Screen
'Saving Table/Query Records in Collection Object
'Author:  a.p.r. pillai
'Date  :  10/05/2024
'Remarks: with Ready-made Data Entry Events Handler
'       : in the Wrapper TextBox Class Module
'       : Suitable for Columnar, Tabular or DataSheet Form
'Rights:  All Rights(c) Reserved by www.msaccesstips.com
'------------------------------------------------------

Public Property Get o_Frm() As Form
    Set o_Frm = frm
End Property

Public Property Set o_Frm(ByRef vFrm As Form)
    Set frm = vFrm

    Class_Init
End Property

Private Sub Class_Init()
Dim fldNames() As String
Dim j As Long
Dim Path As String
Dim ctl As Control

On Error GoTo ClassInit_Err

Set DetSec = frm.Section(acDetail)

Const EP = "[Event Procedure]"

'Save Form Detail Section Field Names
'to this Text File for creating Event Subroutine Template

Path = CurrentProject.Path & "\EventSubFields.txt"
If Len(Dir(Path)) > 0 Then
  Kill Path
End If

Open Path For Output As #1

'Instantiate the 'Data_txtBox' Class for each TextBox
'on the Form for streamlined Event Procedures Coding
j = 0
For Each ctl In DetSec.Controls
    Select Case TypeName(ctl)
        Case "TextBox"
        
        j = j + 1
        ReDim Preserve fldNames(1 To j) As String
        fldNames(j) = ctl.Name
        
            Set txt = New Data_TxtBox
            Set txt.m_Frm = frm
            Set txt.m_txt = ctl
'//Colin Riddington Technique: Highlighting BackColor on GotFocus
                txt.m_txt.BackColor = 62207 'RGB(&HFF, &HF2, &H0):Yellow Background
                txt.m_txt.BackStyle = 0 'Transparent
            
    Print #1, ctl.Name 'write Field Name in EventSubFields.txt File
    
                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 "ComboBox"
            Set Cbo = New Data_CboBox
            Set Cbo.m_Frm = frm
            Set Cbo.m_Cbo = ctl
            
'//Colin Riddington Technique: Highlighting BackColor on GotFocus
                Cbo.m_Cbo.BackColor = 62207 'RGB(&HFF, &HF2, &H0)
                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 #1 'Close Text File

ClassInit_Exit:
Exit Sub

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

We instantiate the Class_ObjInit Class Module in the Form Module and assign the Form object to its Property o_Frm. After receiving the Form Object in the Set Property Procedure the Class_Init() Subroutine is called.

The Red colored code lines in this Module will create a Text File EventSubFields.txt to save the Data Field Names from the Detail-Section of the Form. The purpose of this Field List will be explained later on.

The Data_TxtBox Class is Instantiated for each TextBox Control, the Data_CboBox Class (we will create both these Class Modules next) is Instantiated for each ComboBox Control on the Form, The Dirty() and BeforeUpdate() Events are enabled by setting the text "[Event Procedure]" in their OnDirty and BeforeUpdate Event Properties.

NB: If you have not gone through this topic's earlier Pages then you may find most of the discussion on this page somewhat strange. You will find this topic's earlier page Links at the end of this Page. You may visit those Pages, starting from the first one onwards. It starts with the Basics of this Coding concept of Form Module Event Subroutine coding in the Standalone Class Module. The Class_ObjInit Class Module's Code and its usage are almost the same in all the earlier examples too.

The BackColor Property is set with Yellow color and the BackStyle Property is set to Transparent so that the TextBox/ComboBox background highlights only when the Focus is on the Control.

After these changes the TextBox or ComboBox Wrapper Class Module Instance is added as a Collection Object Item, to keep them active in memory. When the Events are fired from the Controls on the Form they are trapped in the TextBox Instance in Collection Object and Execute the Event Subroutine Code.

Remember, each TextBox on the Form has a Data_TxtBox Class Instance created, assigned with the TextBox Reference from the Form, and inserted into the Collection Object Item. When the Dirty() or BeforeUpdate() Event fires from that particular TextBox it is captured in the Data_TxtBox Class Instance representing that TextBox to execute the Event Subroutines.

This is the new VBA Event Subroutine Coding approach that I have designed for ease of VBA Coding in Standalone Class Modules instead of in Form/Report Module. This Coding approach has the following advantages:

Reuse the Event Procedure Code without manual duplication of Event Procedures
Need only one structured Event Subroutine per Event (say BeforeUpdate()) for all Controls of the same Type (for all TextBoxes on the Form) and all the Event Procedure Codes are organized within a single Event Subroutine.
Direct access to the Event Subroutines without interfering with the Form/Report Design
It eliminates the need for interacting with the Form/Report Design View for Code maintenance every time, resulting in faster Project completion.

Create another Class Module and change its name to Data_TxtBox. Copy the following VBA Code Paste it into the Data_TxtBox Class Module and Save it:

The Data_txtBox TextBox Class Module Code.

Option Compare Database
Option Explicit

Private WithEvents mtxt As TextBox
Private mfrm As Form

Dim msgtxt As String
Const cr = vbCr

'------------------------------------------------------
'Streamlining Form Module Code
'in Stand-alone Class Modules
'With Reusable Code
'------------------------------------------------------
'Quick Data Entry Screen
'Author:  a.p.r. pillai
'Date  :  10/05/2024
'Remarks: with Ready-made Data Entry Events Handlers
'       : in the Wrapper TextBox Class Module
'       : Suitable for Columnar, Tabular or DataSheet Form
'       : made for Table/Query.
'Rights:  All Rights(c) Reserved by www.msaccesstips.com
'------------------------------------------------------

Public Property Get m_Frm() As Form
    Set m_Frm = mfrm
End Property

Public Property Set m_Frm(ByRef vmFrm As Form)
    Set mfrm = vmFrm
End Property

Public Property Get m_txt() As TextBox
    Set m_txt = mtxt
End Property

Public Property Set m_txt(ByRef vmtxt As TextBox)
    Set mtxt = vmtxt
End Property

Private Sub mtxt_Dirty(Cancel As Integer)
'Global area of Subroutine

'If new Record Data Entry then ignore Dirty Event
If mfrm.NewRecord Then
 Exit Sub
End If

'VBA Code Runs for all Fields in the Detail Section

msgtxt = "Editing Field [" & UCase(mtxt.Name) & "]?: " _
& mtxt.Value & cr & cr & "Allow the Change?"

    If MsgBox(msgtxt, vbYesNo + vbQuestion, _
    mtxt.Name & "_BeforeUpdate()") = vbNo Then
        Cancel = True
        mtxt.Undo
    End If

'Field Specific Code for validation checks
'----------------------------------------------
'If Field Specific Event handling is required
'then make a Copy of the Data_TxtBox Class with
'a New Name and use with Fieldname based Event Procedure.
'----------------------------------------------
   Select Case mtxt.Name
     Case "ID"
        ' Code
 
     Case "Company"
        ' Code
        
     Case "Last Name"
        ' Code
        
     Case "First Name"
        ' Code
 

   End Select

End Sub


Private Sub mtxt_BeforeUpdate(Cancel As Integer)
'Global area of Subroutine
'VBA Code Runs for all Fields in the Detail Section

'If new Record Data Entry then ignore BeforeUpdate Event
If mfrm.NewRecord Then
 Exit Sub
End If


msgtxt = mtxt.Name & " Old Value: " & mtxt.OldValue & _
cr & cr & "Update to ?: " & mtxt.Value

    If MsgBox(msgtxt, vbYesNo + vbQuestion, mtxt.Name & "_BeforeUpdate()") = vbNo Then
        Cancel = True
        mtxt.Undo
    End If

'----------------------------------------------
'If Field Specific Event handling is required
'then make a Copy of the Data_TxtBox Class with
'a New Name and use it.
'----------------------------------------------
'Copy and Paste Cls_EventSub_Template Code here

   Select Case mtxt.Name 'Replace with
     Case "ID"
        ' Code
 
     Case "Company"
        ' Code
        
     Case "Last Name"
        ' Code
        
     Case "First Name"
        ' Code

 
   End Select


End Sub

The TextBox Class has the mtxt_Dirty() Event Procedure that monitors the active TextBox for data change attempts by the user. When a key press is detected in a Field a message will appear asking whether the user wants to change the data. If the response is positive then allows to make changes to the field.

When the changed data is about to save the mtxt_BeforeUpdate() Event fires, and at this point, the system asks to reconfirm updating the new changes. If the response is negative then the changes are reversed and the original value is restored.

The Dirty() and BeforeUpdate() Event Subroutines protect the data of all the Fields/TextBoxes in the Form's Detail Section.

Within the Select Case . . . End Select structure some Field Names from the Employees Form is shown below. If we need to write VBA Code with specific requirements for Field(s) then we need to write them in this Structure under each Field Name. We will discuss this aspect and what to do in those circumstances a little later. Here it is presented for information purposes only.

Create another Class Module with the Name Data_CboBox for the ComboBox Controls on the Form. Copy the following VBA Code Paste it into the Data_CboBox Class Module and save the Module.

The Data_CboBox ComboBox Class Module Code.

Option Compare Database
Option Explicit

Private WithEvents mCbo As ComboBox
Private mfrm As Form
Dim msgtxt As String
Const cr = vbCr

'------------------------------------------------------
'Streamlining Form Module Code
'in Stand-alone Class Modules
'With Reusable Code
'------------------------------------------------------
'Quick Data Entry Screen
'Author:  a.p.r. pillai
'Date  :  10/05/2024
'Remarks: with Ready-made Data Entry Events Handler
'       : in the Wrapper TextBox Class Module
'       : Suitable for Columnar, Tabular or DataSheet Form
'       : made for Table/Query.
'Rights:  All Rights(c) Reserved by www.msaccesstips.com
'------------------------------------------------------

Public Property Get m_Frm() As Form
    Set m_Frm = mfrm
End Property

Public Property Set m_Frm(ByRef vmFrm As Form)
    Set mfrm = vmFrm
End Property

Public Property Get m_Cbo() As ComboBox
    Set m_Cbo = mCbo
End Property

Public Property Set m_Cbo(ByRef vmCbo As ComboBox)
    Set mCbo = vmCbo
End Property

Private Sub mCbo_Dirty(Cancel As Integer)
'Global area of Subroutine

'If new Record Data Entry then ignore Dirty Event
If mfrm.NewRecord Then
 Exit Sub
End If

'VBA Code Runs for all Fields in the Detail Section

msgtxt = "Editing ComboBox [" & UCase(mCbo.Name) & "]?: " _
& mCbo.Value & cr & cr & "Allow the Change?"

    If MsgBox(msgtxt, vbYesNo + vbQuestion, mCbo.Name & "_BeforeUpdate()") = vbNo Then
        Cancel = True
        mCbo.Undo
    End If

Select Case mCbo.Name
    Case "Combo1"
        'Code
        
    Case "Combo2"
        'Code
        
End Select
    
End Sub

Private Sub mCbo_BeforeUpdate(Cancel As Integer)
'Global area of Subroutine
'VBA Code Runs for all Fields in the Detail Section

'If new Record Data Entry then ignore BeforeUpdate Event
If mfrm.NewRecord Then
 Exit Sub
End If


msgtxt = mCbo.Name & " Old Value: " & mCbo.OldValue & _
cr & cr & "Update to ?: " & mCbo.Value

    If MsgBox(msgtxt, vbYesNo + vbQuestion, mCbo.Name & "_BeforeUpdate()") = vbNo Then
        Cancel = True
        mCbo.Undo
    End If


Select Case mCbo.Name
    Case "Combo1"
        'Code
        
    Case "Combo2"
        'Code
        
End Select
    
End Sub

The ComboBox Event Procedure is the same as the TextBox Code.

The active Field background will be highlighted with the Yellow color when the Field receives the Focus.

Save all the Modules and Compile them from the VBA Window to ensure that everything is ok.

Test Running the Form.

Open the Form in Normal View.
Click on a Field and touch a Key to edit the Field. A message will appear asking you whether you would like to edit the field or not? The OnDirty Event is enabled in all the TextBoxes and Comboboxes to protect the data from unintentional changes.
If your response is Yes then you are allowed to edit the Field. Type one or two characters at the end of the existing text in the Field then press Enter Key.
While saving the changes the BeforeUpdate() Event will fire and a message will appear again asking you to reconfirm the update action. If your response is negative then changes are reversed to the original value.

Data Entry, Editing, and Viewing.

The Class Modules are designed to work with any Form created for Data Entry, Editing, or Data View purposes.

The Form can be created using the Form Wizard or manually in Columnar, Tabular, or DataSheet Design using Table or Query as Source. The Fields on the Form can be picked selectively or all from the Source Table or Query.
After creating a Form, copy the VBA Code from the Form1 Module Paste it into the new Form Module, save the Form, and open it in normal view.

With the above two steps your Form is ready with all the supporting Programs and the data is fully protected from changes. How much time it takes to do the above two steps?

The Program will monitor the Fields in TextBoxes and ComboBoxes (if present) on the Form.

The Dirty() and BeforeUpdate() Event Procedure Code are applied to all the TextBoxes and ComboBoxes (if present) on the Form.

When the Form is in Data Entry Mode (for a new Record) the OnDirty() and BeforeUpdate() Events are disabled.

Data Validation Checks.

The validation requirements of each Field are different. The Validation checks can be performed in two different ways.

1. Through the Validation Rule and Validation Text Properties (recommended) of the Table or through these Properties of the TextBoxes in the Form.

2. Through VBA Code using Event Procedures.

In this Project, the recommended procedure is the first option to use the Validation Rule and Validation Text Properties of the Field in Table Design View, or in the same Properties of the TextBox Field on the Form. If it is already written in the Table Field Properties then not required to repeat it in the Form Field.

Assign an Error Message in the Validation Text Property to display the message when the entered data is invalid. For example, the Date of Birth entered into the Date Field is invalid if it is greater than Today and the message Text can be 'Future Date Invalid'.

The Validation Rule Property accepts simple expressions to evaluate the entered data.

Example: The Last Name Field length is 15 characters or less and the validation expression is:

Validation Rule: Is Null OR Len([Last Name]) <= 15

Validation Text: Last Name maximum 15 characters only.

The Validation Text message will appear when the Last Name is longer than 15 characters.

If some of the Data Fields require validation checks then the above Properties (either in the Table Field OR the TextBox on the Form, but not necessary in both places) can be assigned with appropriate expressions and messages. Check Allen Browne's Microsoft Access Tips Page for more information on writing the Validation Check expressions.

If the requirement is complex and needs VBA to handle the data validation checks then you may make copies of the above three Class Modules and rename them suitably to identify them with the Form and write VBA Event Procedures under the Field Names, as we did in the earlier Tutorial examples.

When VBA Event Procedures are written with Field/TextBox names then those Class Modules cannot be used for any other Form without change. In such cases follow the above procedure.

For example: With Customized VBA Code for the Employees Form.

Emp_ObjInit
Emp_TxtBox
Emp_CboBox

Then change the Class Module Name declared in the global area of the Form Module and in the Emp_ObjInit Module as shown in the following examples:

The Form Module Code change is highlighted.

The Wrapper Class Declarations in the Emp_ObjInit Class Module.

Keeping this requirement in mind I devised a method to generate an Event Subroutine Template using the Data Field Names, saved from the TextBoxes on the Form, so that it can be copied for specific Event Subroutine and write the Code directly below the required Fields and remove the unwanted Field references from the Subroutines.

The Sample Subroutine Code generated from the Field Names collected from the Form and saved in the EventSubFields.txt File is given below for reference:

Private Sub txt_BeforeUpdate(Cancel As Integer)
   Select Case txt.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 above BeforeUpdate() Event Procedure Code Template is generated through the following procedure:

When the Form is open the Class_ObjInit Class Module Class_Init() Subroutine collects the Field Names from the TextBoxes on the Form and saves them into the EventSubFields.txt File in the Database's Folder. See the red colored lines of Code in the Class_Init() Subroutine.

Run the Public Function (in Module1): CreateEventTemplate from the Debug Window (Immediate Window). It will build the above Subroutine Template in the Class Module ClsEventSub_Template. The Field Names are picked from the EventSubFields.txt Text File created earlier.

Procedure to create the ClsEventSub_Template Class Module Code.

Open the required Form for a few seconds then Close it.
Open the Immediate Window, type the function name CreateEventTemplate and press Enter Key.
The ClsEventSub_Template Class Module will have the Event Subroutine Template with all the Fields collected from the Form.
Highlight the entire Code then copy and paste it into the TextBox Wrapper Class Module Emp_TxtBox and change the Event Subroutine Name, (if necessary to match the Object declaration and the Subroutine Name), and write the Event Subroutine Code under the Field Name.

The unwanted field names may be removed from the Copied Subroutine.

If you follow the above steps for another Form the ClsEventSub_Template will contain the Field Names from the latest Form opened.

Demo Database Download

OneClass_DifferentForms.zip

Table Query Records in Collection Object

a.p.r. pillaiApril 29, 2024 No comments

Class Module and Collection Object.

Create a Data View Form with selected Fields from a Table or Query using the built-in Access Form Wizard.
Create a ComboBox, on the Header of the Form, using any of the Fields, like [Last Name] having unique values from the Record Source Table/Query. The ComboBox values will be used as the Record Key to retrieve the selected record randomly from the Collection Object and displays it in the unbound TextBoxes.
Create an unbound TextBox with the name KeyField, in the Header of the Form, and set its Visible Property value to False. Write the expression ="[Last Name]" in the TextBox, if the [Last Name] Field data is in the ComboBox. If the Last Name field alone will not provide unique values then create a Query using Employees Table and join First Name and Last Name Fields in an expression, use a suitable Column Name, use the Query for the Form, and the new Field Values for the ComboBox on the Form.
Create a Command Button in the Footer of the Form with the Name cmdClose and the Caption Close.
Copy the Form1 Module Code (from the attached Demo Database) and Paste it into the Module of the newly created Employees Form's Module.
Save the Form and Close it.

The Form runs the ready-made VBA Code in the DATA_View Class Module. The Class Module is reusable for Forms created in this manner with any Table/Query as Source Data. No need to make any changes.

Open the Form in Normal View and select an item from the ComboBox the record with that Key value will be retrieved from the Collection Object and displayed in the unbound Textboxes on the Form instantly. This Form is intended for data view purposes only. The TextBoxes are locked and the data cannot be edited.

How much time it takes to create the above Form with the simple steps explained above. You don't have to write any Code on the Form Module. The TextBoxes are placed properly by the Form Wizard. Hardly it takes about 5 minutes to prepare the Data View Form and ready to run with the Data_View ready to run Class Module.

The Ready-made Reusable Form Module Code:

Option Compare Database
Option Explicit

Private Cls As New DATA_View

Private Sub Form_Load()
Set Cls.o_frm = Me
End Sub

Private Sub Form_Unload(Cancel As Integer)
  Set Cls = Nothing
End Subc

The DATA_View Class Object is instantiated in the Employees Form Module and the current Form Object is assigned to the o_Frm() Property of DATA_View Object.

The Ready-made Reusable DATA_View Class Module Code.

Option Compare Database
Option Explicit

Private WithEvents cbo As ComboBox
Private WithEvents cmd As CommandButton
Private oFrm As Form
Private frmSec As Section

Private Coll As New Collection
Private txtBox() As String
Private strTable As String

'------------------------------------------------------
'Streamlining Form Module Code
'in Stand-alone Class Modules
'With Reusable Code
'------------------------------------------------------
'Quick Data View Screen
'Saving Table/Query Records in Collection Object
'Author:  a.p.r. pillai
'Date  :  26/04/2024
'Remarks: Keep Recordset in Collectuon Object
'       : and Retrieve specific record using Key
'Rights:  All Rights(c) Reserved by www.msaccesstips.com
'------------------------------------------------------

Public Property Get o_frm() As Form
    Set o_frm = oFrm
End Property

Public Property Set o_frm(ByRef vfrm As Form)
    Set oFrm = vfrm
    
    Set frmSec = oFrm.Section(acDetail)
    Call Class_Init
End Property

Private Sub Class_Init()
Dim db As Database
Dim rst As Recordset
Dim flds As Integer
Dim ctl As Control
Dim k As Integer
Dim Rec() As Variant, strKey As String
Dim vKeyName As String

strTable = oFrm.RecordSource

Set cmd = oFrm.cmdClose
    cmd.OnClick = "[Event Procedure]"
    
Set cbo = oFrm.cboName
cbo.OnClick = "[Event Procedure]"

'Make the Data Field TextBoxes Unbound
'Save the Field Names on the Form into the txtBox() Array
flds = 0
For Each ctl In frmSec.Controls
    Select Case TypeName(ctl)
        Case "TextBox"
           ctl.ControlSource = ""
           flds = flds + 1
           ReDim Preserve txtBox(1 To flds) As String
           
'Get the selected Field Names from the
'TextBoxes on the Form's Detail Section
           txtBox(flds) = ctl.Name
           ctl.Locked = True
    End Select
Next
'Set ComboBox Default Value
'Change Form Properties
oFrm.cboName.DefaultValue = "=[cboName].[column](0,0)"
oFrm.RecordSelectors = False
oFrm.NavigationButtons = False
oFrm.ScrollBars = 0

'Load the Table/Query Records into Collection Object
ReDim Rec(1 To flds) As Variant

Set db = CurrentDb
Set rst = db.OpenRecordset(strTable, dbOpenSnapshot)

'------------------------------
vKeyName = oFrm!KeyField 'Collection Key Field Value
'------------------------------

Do While Not rst.EOF
    For k = 1 To flds
       Rec(k) = rst.Fields(txtBox(k)).Value
    Next

'Key Field Name in the Form Fields
'=========================================
   strKey = rst.Fields(vKeyName).Value
'=========================================
    Coll.Add Rec, strKey 'Save Rec() Array
    rst.MoveNext
Loop

Set rst = Nothing
Set db = Nothing

End Sub

Private Sub cbo_Click()
Dim strKy As String, Record As Variant
Dim j As Long, L As Long, H As Long

'Get Selected Collection Key from ComboBox
strKy = cbo.Value
 
'Retrieve the record using Key from Collection
'and load into Variant Array Record
  
  Record = Coll(strKy)
  
  L = LBound(Record)
  H = UBound(Record)
  
'Add Field Values into corresponding Text Boxes
  For j = L To H
    oFrm(txtBox(j)) = Record(j) 'Display in Unbound TextBox
  Next
  oFrm.Requery
  
End Sub

Private Sub cmd_Click()
    DoCmd.Close acForm, oFrm.Name
End Sub

Private Sub Class_Terminate()
Do While Coll.Count > 0
    Coll.Remove 1
Loop

End Sub

Data_View VBA Code Segment-wise Review.

Note: You can Instantiate this single Data_View Class Module in several such data display Form Modules, in the same Project, and keep all of them open together, if necessary, and work with them. No need to duplicate the Class Module or VBA Code.

The Global Declarations.

The ComboBox and Command Button Controls are declared with the Keyword WithEvents to capture Events when fired from these objects on the Form. In the next two lines, a Form Object oFrm and a Form Section Object are declared.

The Collection Object is Instantiated with the object name Coll followed by the txtBox() Array, with unspecified elements of String Type, and the string Variable strTable for saving the Form Record Source (Table/Query) name from the Form.

The next segment is the Form Get and Set Property Procedures to capture the active Form Object Value passed from the Form_Load() Event Procedure of Employees Form. In the Set Property Procedure after assigning the Form Object received in vFrm Parameter to the oFrm Property the Employees Form Detail Section Reference is assigned to the frmSec Object and then calls the Class_Init() Subroutine.

At the beginning of the Class_Init() Subroutine the statement:

strTable = oFrm.RecordSource

reads the Form's Record Source Property value and retains it in the srtTable Variable.

The Command Button and Combobox Object References from the Form are assigned to the cmd and cbo Objects respectively and are enabled with the Click Events too.

'Make the Data Field TextBoxes Unbound
'Save the Field Names on the Form into the txtBox() Array
flds = 0
For Each ctl In frmSec.Controls
    Select Case TypeName(ctl)
        Case "TextBox"
           ctl.ControlSource = ""
           flds = flds + 1
           ReDim Preserve txtBox(1 To flds) As String
           
'Get the selected Field Names from the
'TextBoxes on the Form's Detail Section
           txtBox(flds) = ctl.Name
           ctl.Locked = True
    End Select
Next
'Set ComboBox Default Value
'Change Form Properties
oFrm.cboName.DefaultValue = "=[cboName].[column](0,0)"
oFrm.RecordSelectors = False
oFrm.NavigationButtons = False
oFrm.ScrollBars = 0

The For . . . Next Loop scans the Detail Section of the Form for the TextBox Control names (or the actual Source Data Field Names) and loads them into the txtBox() Array and calculates the number of TextBox Controls on the Form in Flds Variable. The ctl.ControlSource = "" makes the TextBoxes Unbound. The txtBox() Array is redimensioned at each level increasing the array element by 1, preserving the data of earlier elements, and taking the count of fields in the Flds Vriable. This method automatically adjusts to the number of Fields added/deleted from the Form by the user at will.

The ComboBox's default Value is set with the Statement: =[cboName].[column](0,0).

The next four statements change the Form Properties.

vKeyName = oFrm!KeyField

The above statement reads the KeyField Name given in the expression like ="[Last Name]" and assigns it to the Variable vKeyName. The Collection Object Key must be a Field with unique data, and the same Field data must appear in the ComboBox cboName. The ComboBox item will be used to retrieve the Record from the Collection Object randomly.

ReDim Rec(1 To flds) As Variant
Set db = CurrentDb
Set rst = db.OpenRecordset(strTable, dbOpenSnapshot)

'----------------------------
vKeyName = oFrm!KeyField
'----------------------------

Do While Not rst.EOF
    For k = 1 To flds
       Rec(k) = rst.Fields(txtBox(k)).Value
    Next

'Key Value Field Name in the Form Fields
'=========================================
   strKey = rst.Fields(vKeyName).Value
'=========================================
    Coll.Add Rec, strKey
    rst.MoveNext
Loop

The Rec() Array is redimensioned for the number of Data Fields on the Form. These field values are read from the Source Table/Query, one record at a time, the Rec() Array elements are filled with the Field values and the Rec() Array is added as a single Item in the Collection Object, with the Last Name Field as Collection Item Key, the second parameter of the Collection Object's Add() Method. In this way, all the Source Data Records are loaded into the Collection Object in Memory.

Note: The Source Table/Query may have many more fields of data than what you originally placed on the Form with the Form Wizard. The Program will read data based on the Field Names appearing on the Form only, other fields in the Source Table/Query are ignored. You may add more Fields to the Form or Delete some of them at your will at any time. You may rearrange the Fields the way you like but see that their Name Property Value is not changed and matches with a Field in the Record Source Table or Query. No need for any change in the VBA Code.

The cbo_Click() Event Subroutine.

Private Sub cbo_Click()
Dim strKy As String, Record As Variant
Dim j As Long, L As Long, H As Long

'Get Selected Collection Key from ComboBox
strKy = cbo.Value
 
'Retrieve the record using Key from Collection
'and load into Variant Array R
  
  Record = Coll(strKy)
  
  L = LBound(Record)
  H = UBound(Record)
  
'Add Field Values into corresponding Text Boxes
  For j = L To H
    oFrm(txtBox(j)) = Record(j)
  Next
  oFrm.Requery
  
End Sub

When the User selects an Item from the Combobox the selected value is used as the Collection Object Item Key to retrieve the Item and loads it into Record() Array.

The Record Array Element values are read in the order they are loaded into memory and the unbound TextBox Values are filled in the order their names are read from the Form earlier. The TextBoxes are in the locked state and their contents cannot be changed.

The Data_View Class Module and the Form Module Code can be used for any such Forms created using the same Procedure for Data View without any change. See that the ComboBox Name is cboName and the CommandButton Name is cmdClose.

Data Field Names will be picked from the TextBox's Name Property, created through the Form Wizard, in the Detail Section of the Form.

Creates a Data View Form in Minutes, with ready-to-use Code.