Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Wrapper Class Module Creation Wizard

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.  

Class Template Wizard

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:

  1. Global Declaration of Objects.
  2. The Standard Property Procedures for the declared Objects.

  3. 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.

Last Name
First Name
E-mail Address
Job Title
ZIP/Postal Code

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.

Employees Form for Wizard

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
'Close all the three files
Close #1
Close #2
Close #3

Exit Sub

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

  1. Reusing Form Module VBA Code for New Projects.
  2. Streamlining Form Module Code - Part Two.
  3. Streamlining Form Module Code - Part Three
  4. Streamlining Form Module Code - Part Four
  5. Streamlining Form Module Code - Part Five
  6. Streamlining Form Module Code - Part Six
  7. Streamlining Form Module Code - Part Seven
  8. Streamlining Form Module Code - Part Eight
  9. Streamlining Form Module Code - Part Nine
  10. Streamlining Form Module Code - Part Ten
  11. Streamlining Form Module Code - Part Elevan
  12. Streamlining Report Module Code in Class Module
  13. Streamlining Module Code Report Line Hiding-13.
  14. Streamlining Form Module Code Part-14.
  15. Streamlining Custom Made Form Wizard-15.
  16. Streamlining VBA Custom Made Report Wizard-16.
  17. Streamlining VBA External Files List in Hyperlinks-17
  18. Streamlining Events VBA 3D Text Wizard-18
  19. Streamlining Events VBA RGB Color Wizard-19
  20. Streamlining Events Numbers to Words-20
  21. Access Users Group(Europe) Presentation-21
  22. The Event Firing Mechanism of MS Access-22
  23. One TextBox and Three Wrapper Class Instances-23
  24. Streamlining Code Synchronized Floating Popup Form-24
  25. Streamlining Code Compacting/Repair Database-25
  26. Streamlining Code Remainder Popup Form-26
  27. Streamlining Code Editing Data in Zoom-in Control-27
  28. Streamlining Code Filter By Character and Sort-28
  29. Table Query Records in Collection Object-29
  30. Class for All Data Entry Editing Forms-30


No comments:

Post a Comment

Comments subject to moderation before publishing.




Post Feed


Popular Posts

Blog Archive

Powered by Blogger.


Forms Functions How Tos MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Class Module External Links Queries Array msaccess reports Accesstips WithEvents msaccess tips Downloads Objects Menus and Toolbars Collection Object MsaccessLinks Process Controls Art Work Property msaccess How Tos Combo Boxes Dictionary Object ListView Control Query VBA msaccessQuery Calculation Event Graph Charts ImageList Control List Boxes TreeView Control Command Buttons Controls Data Emails and Alerts Form Custom Functions Custom Wizards DOS Commands Data Type Key Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Command Button Report msaccess menus msaccessprocess security advanced Access Security Add Auto-Number Field Type Form Instances ImageList Item Macros Menus Nodes RaiseEvent Recordset Top Values Variables Wrapper Classes msaccess email progressmeter Access2007 Copy Excel Export Expression Fields Join Methods Microsoft Numbering System Records Security Split SubForm Table Tables Time Difference Utility WScript Workgroup database function msaccess wizards tutorial Access Emails and Alerts Access Fields Access How Tos Access Mail Merge Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Common Controls Conditional Formatting Data Filtering Database Records Defining Pages Desktop Shortcuts Diagram Disk Dynamic Lookup Error Handler External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Message Box Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar updating upload vba code