LEARN MS-ACCESS TIPS AND TRICKS

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 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 of 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, and 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 a Form is open, we typically scan for different types of controls in the Form as part of the new Streamlined Event Subroutine Coding procedure. The control names can then be saved in text files, 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 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 the 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 a second 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.

Demo Databases Download.

ClassTemplateWizard.zip

Classes For All Data Entry Editing Forms

a.p.r. pillaiMay 16, 2024 2 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 be tedious. 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 reusable code structures of Standalone Class Modules, that help to centralize and organize Event Procedures more efficiently.

Using the Class Modules for VBA coding enables a swift, automated solution that implements data protection methods across all fields on the form. By leveraging event Dirty() and BeforeUpdate() Subroutine Code reuse techniques, these data-protecting procedures can be efficiently applied to all Text Boxes and Combo Boxes on the form. This streamlined approach ensures quick, 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 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.

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

There are three ready-made Class Modules, with a single prewritten OnDirty() and BeforeUpdate() Event Procedures. The Class_ObjInit Class Module is instantiated in the global declaration area, with the object name Cls. When the Form is open, and in the Form_Load() Event Procedure, the Cls.o_Frm Class Module Property is assigned to 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, then click the Properties Window Icon (located between the Project Explorer and Object Browser Icons) to display the Class Module's Property Sheet. Change the Module Name from Class1 to Class_ObjInit.
Copy the VBA Code given below, paste it into the Class_ObjInit Class Module, and save it.

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 instantiated the Class_ObjInit Class Module in the Form Module and assigned the Form object to its Property o_Frm. After the Form Object is assigned to the Set Property Procedure, the Class_Init() Subroutine is called.

The Code lines in red 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. Find this Topic's earlier links at the end of this Page. You may visit those Pages starting from the first link. 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 usages are almost the same in all the earlier examples, too.

The BackColor Property is set to Yellow, and the BackStyle Property is set to Transparent so that the TextBox/ComboBox background is highlighted only when the Control is active.

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 the 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 a particular TextBox, it is captured in the Data_TxtBox Class Instance representing that TextBox and executes the Event Subroutines.
This is the new VBA Event Subroutine Coding approach for Form/Report Controls that I have designed for ease of VBA Coding in Standalone Class Modules. 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 in a structured manner. Export this code to your other projects and customize it if necessary.
- Direct access to the Event Subroutines Code for maintenance 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, 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. 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 it allows changes to the field.

When the changed data is about to be saved, the mtxt_BeforeUpdate() Event fires, and the system prompts to reconfirm the 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 are 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 address this aspect and the appropriate steps to take later. For now, it is presented for demonstration purposes only.

Create another Class Module Named 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 yellow when the Field receives 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 press any key to begin editing. A confirmation message will appear, asking whether you want to proceed with editing the field. The OnDirty event is enabled on all TextBoxes and ComboBoxes to safeguard against accidental data 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 the 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.

Data Entry, Editing, and Viewing.

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

The form can be created either with the Form Wizard or manually in Columnar, Tabular, or Datasheet layout, using a Table or Query as the data source. You may choose to include specific fields or all fields 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.

After these two steps, the Form is ready with all supporting Programs, and the data is fully protected from changes. How much time does it take for the above two steps?

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

The Dirty() and BeforeUpdate() event procedures are applied to all TextBox and ComboBox controls (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.

Each Field's validation requirement is 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 Validation Rule option and the Validation Text Properties of the Field in Table Design View, or the same Properties of the TextBox Field on the Form. If it is already written in the Table Field Properties, then do not 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 field value exceeds 15 characters.

If certain data fields require validation, you can assign validation rules and messages either at the table field level or in the corresponding TextBox control on the form—but not necessarily in both. For more guidance on writing effective validation expressions, refer to Allen Browne’s Microsoft Access Tips page.

If the validation requirements are more complex, you can handle them with VBA. Start by copying the three Class Modules mentioned above. Next, rename them so you can easily identify which form they belong to. Finally, write the VBA event procedures under the relevant field names, just as we did in the earlier tutorial examples.

An Access Application will have several Forms. Each Form's Controls Event Procedures can be streamlined into Event Procedure Codes in Standalone Class Modules. In such situations, identifying which Class Module belongs to which Form is difficult. To overcome this situation, we decided to add a Form name prefix to the Class Module Name.

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.

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

The Wrapper Class Declarations in the Emp_ObjInit Class Module.

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

Keeping this requirement in mind, I devised a method to generate an event subroutine template using the data field names saved from the Form text boxes. This template can then be copied into a specific event subroutine, allowing you to write the code directly below the required fields. Any unwanted field references can simply be removed from the subroutine.

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’s Class_Init() subroutine collects the field names from the Form and saves them in the EventSubFields.txt file, in the database folder. (Refer to the red-highlighted lines of code in the Class_Init() subroutine.)

Next, run the public function CreateEventTemplate (from Module1) in the Debug/Immediate window. This function will generate the event subroutine template in the class module ClsEventSub_Template, using the field names stored earlier in the EventSubFields.txt file.

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 the 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, 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 last 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.

Steps to Create a Data View Form
1. Create the Form
  - Use the built-in Access Form Wizard to create a Data View Form with the required fields from your table or query.
2. Add a ComboBox in the Form Header
  - Insert a ComboBox in the Header section of the form.
  - Set its Row Source to a field with unique values (for example, [Last Name]) from the form’s record source table/query.
  - The ComboBox will serve as a record key, allowing you to randomly retrieve the selected record from the Collection Object and display its values in unbound TextBoxes.
3. Insert a Hidden TextBox for the Key Field
  - Add an unbound TextBox in the Header of the form and name it KeyField.
  - Set its Visible property to False.
  - In its Control Source, enter the expression:
```
="[Last Name]"
```
  - If [Last Name] alone does not provide unique values, create a query that concatenates First Name and Last Name into a single expression (e.g., FullName: [FirstName] & " " & [LastName]).
  - Use this query as the form’s record source and populate the ComboBox with this new field instead.
4. Add a Close Button in the Footer
  - Insert a Command Button in the Form Footer.
  - Name it cmdClose.
  - Set its Caption property to Close.
5. Copy VBA Code
  - Open the Form1 Module from the demo database.
  - Copy its VBA code and paste it into the module of your newly created Employees Form.
6. Save and Close
  - Save the form.
  - Close the form to complete the setup.

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

The Data View Form runs on the ready-made VBA code in the DATA_View Class Module. This Class Module is fully reusable—any form created using the same method with any Table or Query as the source data can use it without modification.

Once the form is opened in Normal View, simply select an item from the ComboBox. The record with the matching key value is instantly retrieved from the Collection Object and displayed in the unbound TextBoxes. Since this form is designed strictly for data viewing, all TextBoxes are locked to prevent editing.

Creating this form is extremely quick and straightforward:

You don’t need to write any code in the Form Module.
The Form Wizard automatically places and arranges the TextBoxes properly.
With just the simple steps outlined earlier, the entire setup can be completed in about five minutes—and the form is ready to run with the DATA_View 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 Sub

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 the 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

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

Data_View VBA Code Segment-wise Review.

Note: You can instantiate the single DATA_View Class Module across multiple data display forms within the same project. If needed, you can even keep several of these forms open simultaneously and work with them independently. There’s no need to duplicate the Class Module or its VBA code—one module efficiently serves them all.

The Global Declarations.

The ComboBox and Command Button controls are declared with the WithEvents keyword to capture their events when triggered on the form. Following this, a Form object (oFrm) and a Form Section object (frmSec) are declared.

A Collection object (Coll) is then instantiated, along with the txtBox() array (a string array with an unspecified number of elements) and the string variable strTable, which stores the name of the form’s record source (table or query).

The next section defines the Form Get and Set property procedures used to capture the active form object passed from the Form_Load() Event procedure. In the Set property procedure, after assigning the vFrm parameter (the received form object) to the oFrm property, the Employees form’s Detail section reference is assigned to the frmSec object, and after that the Class_Init() subroutine is called.

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

'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 through the Detail section of the form, retrieves the names of all TextBox controls (which correspond to the source data field names) and loads them into the txtBox() array. At the same time, it calculates the total number of TextBox controls and stores this value in the Flds variable.

Each TextBox is then made unbound by setting its ControlSource property to an empty string (ctl.ControlSource = ""). The txtBox() array is dynamically redimensioned at each iteration, increasing its size by one element while preserving the data already stored. The final count of fields is captured in the Flds variable.

This approach ensures that the code automatically adapts to any changes—fields added or removed from the form by the user—without requiring manual adjustments. 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 expression value, such as '="[Last Name]"', is read and the KeyField name (Last Name) is assigned to the variable vKeyName. The field used as the Collection Object key must contain unique values, and the same field data must also be present in the ComboBox cboName. This ensures that selecting an item from the ComboBox can be used to randomly retrieve the corresponding record from the Collection Object.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

Rec() The array is redimensioned to match the number of data fields on the form. The field values are then read from the source table or query, one record at a time, and stored in the corresponding elements of the Rec() array. Each completed Rec() array is added as a single item in the Collection object, with the Last Name field used as the collection item key (the second parameter of the Collection object’s Add() method). Through this process, all source data records are efficiently loaded into memory within the Collection object.

Note: The source table or query may contain more fields than those placed on the form by the Form Wizard. However, the program only processes the fields whose names appear on the form; any additional fields in the source are ignored. You may freely add or remove fields from the form or rearrange their positions as needed. Just ensure that the Name property of each field remains unchanged and matches a corresponding field in the record source table or query. No modifications to the VBA code are required.

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 a user selects an item from the ComboBox, the selected value is used as the Collection Object Item Key to retrieve the corresponding record and load it into the Record() array.

The array element values are read in the same order they were stored in memory, and the unbound TextBoxes are populated accordingly, based on the order in which their names were read from the form. Since the TextBoxes are locked, their contents cannot be edited.

The Data_View Class Module and accompanying Form Module code can be reused for any form created using this procedure, without modification. Ensure that the ComboBox is named cboName, and the Command Button is named cmdClose.

Data field names are taken directly from the Name property of the TextBoxes created by the Form Wizard in the Detail section of the form.

This approach allows you to create a fully functional Data View Form in just minutes, complete with ready-to-use code.

Demo Database Download

Data_InColl.zip

Streamline Filter By Character Sort

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

Streamlining Form Module Code in Standalone Class Module.

Data Filter by Character and Sort on Form.

Version 1.0 of this Article was originally published in April 2009 and upgraded with a significant change in the Demo Application Version 2.0. In this version, the Event Subroutines are executed from the standalone class module, rather than from the form module.

After realizing the advantages of Event Procedures Coding in Standalone Class Modules, I found it difficult to revert to the traditional, less organized, and time-consuming Form/Report Module VBA Coding procedures.

In traditional coding, modifying the code of a specific event subroutine in a Form Module—especially when dealing with multiple types of controls—usually involves several manual steps.

Open the Form in Design View.
Select the required Control.
Display its Property Sheet.
Select the specific Event Property.
Click on the Build Button to open the Event Procedure.
Write/Modify the Code.
Save the Form with the Code.
Open the Form in Normal View to test the change.

Typically, it takes about eight steps to reach a specific event procedure in the Form Module to write, edit, and save changes. While it’s true that multiple event procedures can be modified once the form is open, steps 2 through 6 must still be repeated for each individual event procedure. This repetitive process is both tedious and time-consuming, especially when performed numerous times during development. At the same time, designing the user interface also consumes significant effort, as both activities often occur in parallel. Furthermore, any code written directly in the Form Module remains locked there and cannot be reused elsewhere, except for public functions written in a Standard Module.

If you’re a beginner VBA programmer, learning the language alongside user interface design is best accomplished through the traditional method. If you’re an experienced developer, I encourage you to experiment with streamlined VBA Event procedure coding in Standalone Class Modules and see firsthand how it compares to the traditional coding style.

This approach can offer valuable insights and significantly boost coding efficiency, saving a substantial amount of time in project development. Moreover, the reusable VBA code in Standalone Class Modules can be easily exported and integrated into other projects, further enhancing productivity.

Streamlined event procedure coding involves more than just moving the code from the form module to the standalone class module. It's about organizing the event procedure code in a structured, concise manner that promotes reusability without duplicating code for multiple objects of the same type in the form module. This approach enhances code maintainability and reduces redundancy, resulting in a more efficient and manageable codebase.

Direct access to the Structured Event Subroutines in the Standalone Class Module eliminates the struggle with the form design view to reach a particular event subroutine. This direct access streamlines the development process, making it easier to locate and modify event procedures without the hassle of navigating through the form's design view.

Example of Structured Event Subroutine Coding:

The BeforeUpdate Event Procedure Code of several TextBoxes can be written within a single BeforeUpdate() Event Subroutine.

Private Sub txt_BeforeUpdate(Cancel As Integer)

'When the BeforeUpdate Event is captured the txt object will have
'the Name of the Object fired the Event
Select Case txt.Name

Case "Quantity"
    'Code
  
  Case "UnitPrice"
    'Code
  
  Case "SaleTax"
    ' Code
  
  Case . . .
  
End Select
End Sub

The seventh episode in this series of articles is a prime example of event subroutine code Reuse and illustrates an organized, structured approach to event procedure coding. By writing just one set of GotFocus and LostFocus Event Subroutines, you can efficiently manage the behavior of 25 or more text boxes on the form when they gain or lose focus. This example offers a straightforward demonstration of how to implement streamlined event procedure coding in a standalone class module, emphasizing code reusability and reduced redundancy.

Microsoft Access controls, such as TextBoxes, Command Buttons, and others, rely on event-defining, event-firing, and event-capturing mechanisms, which form the foundation of streamlined event subroutine coding. I explored these concepts in detail during my presentation to the Access User Groups (Europe) on January 3, 2024. The presentation is available as a YouTube video on the Access User Groups (Europe) channel, titled "Streamlined Event Procedure Coding in Standalone Class Modules," which offers valuable insights into this coding approach.

In our current project 'Filter by Character and Sort', the Customers Form’s record source is derived from the CustomersQ query, which contains multiple records. To enhance user experience and efficiency, we’ll implement a technique that swiftly filters records by allowing users to type the first one or more characters from the customer’s selected Data Field. Utilizing the form’s filter settings, matching records will be quickly identified based on the characters typed into a text box control. This feature will streamline the process of locating specific customer records, improving overall usability.

The Customers Form Image-1 Normal Data View.

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

Customers Form Image-2 with Filtered Data.

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

The yellow-highlighted TextBox serves as the filter input control. Above it, a ComboBox lets the users select the field to search—in this case, the Last Name field. As text is entered into the filter box, the system matches the beginning of the selected field’s value and filters the records accordingly. This setup enables efficient, quick record searches based on starting characters, making it easier to locate specific names that meet the criteria.

In this example, three records are initially filtered, each with a Last Name beginning with the letter G. When you type ‘r’ after ‘G’ in the yellow-highlighted filter box, the first record (which contains ‘Go…’) no longer matches and is removed from the results. This dynamic filtering approach provides precise and efficient record retrieval, updating in real time as users type, and making it easier to quickly locate records that meet the search criteria.

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

When the Backspace key is pressed to remove the last character from the filter TextBox, the data instantly updates to reflect the new filtering criteria based on the remaining characters. If no characters remain, the filter is cleared, and the full dataset is displayed in the form’s detail section. This ensures a seamless, real-time filtering experience that intuitively responds to user input.

The Cls_ObjInit Class Module VBA Code.

Option Compare Database
Option Explicit

Private WithEvents frm As Access.Form
Private WithEvents txt As Access.TextBox
Private WithEvents cmd As Access.CommandButton
Private WithEvents cbo As Access.ComboBox

Dim txt2Filter

Public Property Get m_Frm() As Access.Form
    Set m_Frm = frm
End Property

Public Property Set m_Frm(ByRef vFrm As Access.Form)
    Set frm = vFrm
    
    Call Class_Init
End Property

Private Sub Class_Init()
Const EP = "[Event Procedure]"

Set txt = frm.FilterText
Set cmd = frm.cmdClose
Set cbo = frm.cboFields

With frm
    .OnLoad = EP
    .OnUnload = EP
End With

With txt
    .OnKeyUp = EP
End With

With cmd
    .OnClick = EP
End With

With cbo
    .OnClick = EP
End With

End Sub

Private Sub cbo_Click()
    frm.FilterText = ""
    txt2Filter = ""
    frm.Filter = ""
    frm.FilterText.SetFocus
    frm.FilterOn = False
End Sub

Private Sub txt_KeyUp(KeyCode As Integer, Shift As Integer)
Dim C As Integer, sort As String
Dim L As String

On Error GoTo txt_KeyUp_Err
C = KeyCode

With frm
Select Case C
    Case 8 'backspace key
        txt2Filter = Nz(![FilterText], "")
        If Len(txt2Filter) = 1 Or Len(txt2Filter) = 0 Then
            txt2Filter = ""
            .FilterOn = False ' remove filter
            frm.Recalc
            
        Else
            txt2Filter = Left(txt2Filter, Len(txt2Filter) - 1) 'delete the last character
            If Len(txt2Filter) = 0 Then
                .FilterOn = False ' remove filter
                
            Else 'set filter and enable
                .Filter = "[" & ![cboFields] & "]" & " like '" & txt2Filter & "*'"
                ![FilterText] = txt2Filter
                
                'position cursor position at the end of the text
                If Len(!FilterText) > 0 Then
                    .Section(acFooter).SetTabOrder
                    ![FilterText].SelLength = Len(![FilterText])
                    SendKeys "{END}" 'position cursor at right end of text
                End If
                
                .FilterOn = True
            End If
        End If
       
    Case 37 'right arrow key, prevent text highlighting
        SendKeys "{END}" 'position cursor at right end of text
    
    Case 32, 48 To 57, 65 To 90, 97 To 122 'space, 0 to 9, A to Z, a to z keys
        txt2Filter = txt2Filter & Chr$(C)
        
        'First letter of words to uppercase
        ![FilterText] = StrConv(txt2Filter, vbProperCase)
        SendKeys "{END}"
        GoSub SetFilter
End Select
End With

txt_KeyUp_Exit:
Exit Sub

SetFilter:
With frm
  .Refresh
  If Len(txt2Filter) = 0 Then
        .FilterOn = False ' remove filter
  Else 'set filter and enable
        .Filter = "[" & ![cboFields] & "]" & " like '" & txt2Filter & "*'"
        .FilterOn = True
  
  ' Set sort order
        sort = IIf(!Frame10 = 1, "ASC", "DESC")
        .OrderBy = "[" & !cboFields & "] " & sort
        .OrderByOn = True
  
        .Section(acFooter).SetTabOrder 'Form Footer Section Active
  'position cursor at end of text
        ![FilterText].SelLength = Len(![FilterText])
        SendKeys "{END}"
  End If
End With
Return

txt_KeyUp_Err:
MsgBox Err.Description, , "txt_KeyUp()"
Resume txt_KeyUp_Exit
End Sub

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

There are three controls in the footer of the form, each triggering simple events that run corresponding code—except for the TextBox, which handles a more complex KeyUp() event.

In this scenario, creating separate Wrapper Class Objects for the TextBox, ComboBox, and Command Button is not necessary since only one instance of each exists on the form.

Within the Cls_ObjInit class module, the main object instances are declared in the global section using the WithEvents keyword, enabling Event capture and execution of their respective subroutines. Additionally, a Variant-type variable, txt2Filter, is declared globally, followed by the Form Property procedures.

The Class_Init() subroutine is then called from the Set m_Frm() property procedure after receiving the form object from the Form_Load() event procedure in the form module.

Next, the txt, cmd, and cbo objects are assigned references to their respective controls on the form and enabled with the required events.

The ComboBox Click event selects a field name to serve as the Filter target. This action resets any previously applied filter.
The Command Button Click event closes the form.
The TextBox KeyUp event captures valid keystrokes, builds the input string character by character, and applies it dynamically as a filter to the field selected in the ComboBox.

The txt_KeyUp() Event Subroutine Code.

Private Sub txt_KeyUp(KeyCode As Integer, Shift As Integer)
Dim C As Integer, sort As String
Dim L As String

On Error GoTo txt_KeyUp_Err
C = KeyCode

With frm
Select Case C
    Case 8 'backspace key
        txt2Filter = Nz(![FilterText], "")
        If Len(txt2Filter) = 1 Or Len(txt2Filter) = 0 Then
            txt2Filter = ""
            .FilterOn = False ' remove filter
            frm.Recalc
            
        Else
            txt2Filter = Left(txt2Filter, Len(txt2Filter) - 1) 'delete the last character
            If Len(txt2Filter) = 0 Then
                .FilterOn = False ' remove filter
                
            Else 'set filter and enable
                .Filter = "[" & ![cboFields] & "]" & " like '" & txt2Filter & "*'"
                ![FilterText] = txt2Filter
                
                'position cursor position at the end of the text
                If Len(!FilterText) > 0 Then
                    .Section(acFooter).SetTabOrder
                    ![FilterText].SelLength = Len(![FilterText])
                    SendKeys "{END}" 'position cursor at right end of text
                End If
                
                .FilterOn = True
            End If
        End If
       
    Case 37 'right arrow key, prevent text highlighting
        SendKeys "{END}" 'position cursor at right end of text
    
    Case 32, 48 To 57, 65 To 90, 97 To 122 'space, 0 to 9, A to Z, a to z keys
        txt2Filter = txt2Filter & Chr$(C)
        
        'First letter of words to uppercase
        ![FilterText] = StrConv(txt2Filter, vbProperCase)
        SendKeys "{END}"
        GoSub SetFilter
End Select
End With

txt_KeyUp_Exit:
Exit Sub

SetFilter:
With frm
  .Refresh
  If Len(txt2Filter) = 0 Then
        .FilterOn = False ' remove filter
  Else 'set filter and enable
        .Filter = "[" & ![cboFields] & "]" & " like '" & txt2Filter & "*'"
        .FilterOn = True
  
  ' Set sort order
        sort = IIf(!Frame10 = 1, "ASC", "DESC")
        .OrderBy = "[" & !cboFields & "] " & sort
        .OrderByOn = True
  
        .Section(acFooter).SetTabOrder 'Form Footer Section Active
  'position cursor at end of text
        ![FilterText].SelLength = Len(![FilterText])
        SendKeys "{END}"
  End If
End With
Return

txt_KeyUp_Err:
MsgBox Err.Description, , "txt_KeyUp()"
Resume txt_KeyUp_Exit
End Sub

The Sub KeyUp() Event Subroutine takes only the Key Code from the Keys 0-9, A-Z, and a-z. The Backspace key removes the last character entered into the Filter Text input Textbox. The right-arrow character Code is also valid, which moves the I bar to the END of the Filter text and prevents selecting the full text when the Input text box is refreshed.

The Backspace keypress will truncate the right-most character from the Filter input Text, and the Filter action is refreshed. When the Filter input control is empty, the data filter is reset, and full data is displayed on the Form.

The Form Module Code.

Option Compare Database
Option Explicit

'Global declaration
Private obj As New Cls_ObjInit

Private Sub Form_load()
    Set obj.m_Frm = Me
    Application.SetOption "Behavior Entering Field", 2
End Sub

Private Sub Form_Unload(Cancel As Integer)
    Application.SetOption "Behavior Entering Field", 0
    Set obj = Nothing
End Sub

Filter By Character in ComboBox Items.

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

The screenshot above shows the second form, Customers_Combo, which demonstrates the Filter by Character feature applied to ComboBox items. The KeyUp() event subroutine is almost identical to the filter method used in the first form we reviewed earlier.

For this form, a new class module named Cbo_ObjInit has been introduced. The VBA code for the Cbo_ObjInit class module is provided below:

Option Compare Database
Option Explicit

Private WithEvents frm As Access.Form
Private WithEvents txt As Access.TextBox
Private WithEvents cmd As Access.CommandButton
Private cbo As Access.ComboBox

Dim txt2Filter

Public Property Get m_Frm() As Access.Form
    Set m_Frm = frm
End Property

Public Property Set m_Frm(ByRef vFrm As Access.Form)
    Set frm = vFrm
    
    Call Class_Init
End Property

Private Sub Class_Init()
Const EP = "[Event Procedure]"

Set txt = frm.FilterText
Set cmd = frm.cmdExit
Set cbo = frm.cboCust

With frm
    .OnLoad = EP
    .OnUnload = EP
End With

With txt
    .OnKeyUp = EP
End With

With cmd
    .OnClick = EP
End With


End Sub

Private Sub txt_KeyUp(KeyCode As Integer, Shift As Integer)
Dim i As Integer
Dim SQL As String
Dim SQL1 As String
Dim SQL2 As String


On Error GoTo txtKeyUp_Err
SQL = "SELECT CustomersQ.* FROM CustomersQ ORDER BY CustomersQ.[Last Name];"

SQL1 = "SELECT CustomersQ.* FROM CustomersQ "
SQL2 = "WHERE (((CustomersQ.[Last Name]) Like '" '"Gr*"));

i = KeyCode

Select Case i
    Case 8 'backspace key
        frm.Refresh
        If Len(txt2Filter) = 1 Or Len(txt2Filter) = 0 Then
            txt2Filter = ""
        Else
            txt2Filter = Left(txt2Filter, Len(txt2Filter) - 1) 'delete the last character
        End If
        GoSub SetFilter
    Case 37 'right arrow keys
        SendKeys "{END}"
    Case 32, 48 To 57, 65 To 90, 97 To 122 'space, 0 to 9, A to Z, a to z keys
        txt2Filter = txt2Filter & Chr$(i)
        frm![FilterText] = StrConv(txt2Filter, vbProperCase)
        GoSub SetFilter
End Select

txtKeyUp_Exit:
Exit Sub

SetFilter:
  If Len(Nz(txt2Filter, "")) = 0 Then
    With frm
        .cboCust.RowSource = SQL
        .cboCust.Requery
        .cboCust.SetFocus
        .cboCust.Dropdown
    End With
  Else 'set filter and enable
        SQL = SQL1 & SQL2 & txt2Filter & "*'));"
    With frm
        .cboCust.RowSource = SQL
        .cboCust.Requery
        .cboCust.SetFocus
        .cboCust.Dropdown
    End With
  End If
Return

txtKeyUp_Err:
MsgBox Err.Description, , "txtKeyUp()"
Resume txtKeyUp_Exit
End Sub

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

In the earlier approach, we applied the Form Filter method, where the characters entered into a TextBox served as filter criteria to restrict the records displayed in the form’s record source.

In contrast, the ComboBox method dynamically builds an SQL statement from the input filter text entered into a TextBox. This SQL is then assigned to the ComboBox Row Source, refreshing its contents in real time based on the updated criteria.