Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Classes For All Data Entry Editing Forms

 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:

  1. Use the Form-Wizard to create a Form: Columnar or Tabular Design using any Table/Query as a data source.
  2. Open the Form in Design View.

  3. Display the Form Property Sheet and Select the Other Tab.
  4. Change the Has Module Property Value to Yes.

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

  1. Open the VBA Editing Window (ALT+F11)

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

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

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

  6. Open the Form in Normal View.
  7. 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.

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

  1. 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. 
  2. 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.

  1. Emp_ObjInit
  2. Emp_TxtBox
  3. 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.

Emp_ObjInit

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.

  1. Open the required Form for a few seconds then Close it. 
  2. Open the Immediate Window, type the function name CreateEventTemplate and press Enter Key.

  3. The ClsEventSub_Template Class Module will have the Event Subroutine Template with all the Fields collected from the Form.
  4. 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


  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




Share:

Table Query Records in Collection Object

Class Module and Collection Object.

  1. Create a Data View Form with selected Fields from a Table or Query using the built-in Access Form Wizard.
  2. 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 display the data in the unbound TextBoxes in the Detail Section of the Form.

  3. 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, and use the Query for the Form and the new Field Values for the ComboBox on the Form. 
  4. Create a Command Button in the Footer of the Form with the Name cmdClose and the Caption Close.

  5. Copy the Form1 Module Code (from the attached Demo Database) and Paste it into the Module of the newly created Employees Form's Module.
  6. 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
Records in Collection Object

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.


Demo Database 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
Share:

Streamline Filter By Character Sort

 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 introduces a significant change in the Demo Application in 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 the traditional coding style, to modify a specific event subroutine code in the Form Module, with various types of controls, several steps are typically required. 

  1. Open the Form in Design View.
  2. Select the required Control.

  3. Display its Property Sheet.
  4. Select the specific Event Property.

  5. Click on the Build Button to open the Event Procedure.
  6. Write/Modify the Code.

  7. Save the Form with the Code.
  8. Open the Form in Normal View to test the change.

So, it typically involves eight steps to reach the specific event procedure code to write/edit and save the changes.

While it’s true that multiple event procedures can be modified consecutively once the form is open, steps 2 through 6 still need to be repeated to reach each specific event procedure code. This repetitive cycle can be tedious and time-consuming, as it’s often repeated numerous times throughout the coding process. Additionally, the user interface designing also requires a considerable amount of time and both these events take place side by side. Moreover, the Code that you developed in the Form Module is not reusable and leaves all of them in the Form Module forever, except for the Public Functions in the Standard Module of the Project. 

If you’re a beginner VBA programmer, learning the language alongside user interface design is best accomplished through the traditional method. However, if you’re already an experienced developer, I recommend experimenting with the streamlined VBA event procedure coding in Standalone Class Modules to experience the difference compared to the traditional coding style. It can provide valuable insights and enhance your coding efficiency saving a tremendous amount of Project Development time. Besides that, the reusable VBA Code in the Standalone Class Module can be Exported into other Projects for reuse. 

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 and concise manner, which promotes reusability without the need for 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 need to struggle with navigating through 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 effectively implement the concept of streamlined event procedure coding in a standalone class module, emphasizing code reusability and reduced redundancy.

The Microsoft Access TextBoxes, Command Buttons, and other Controls' various Event-Defining Event-Firing, and Event-Capturing mechanisms, which form the foundation for Streamlined Event Subroutine Coding, are explored in detail in my presentation to the Access User Groups (Europe) on January 3, 2024. The Presentation's YouTube Video is available on Access User Groups (Europe)'s YouTube Channel, providing insights into the streamlined Event Subroutine coding in Standalone Class Modules. You can find the video here: Streamlined Event Procedure Coding in Standalone Class Module.

In our current project of '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.

Customers Form Image-2 with Filtered Data.

The TextBox, highlighted with a yellow background, functions as the filter text input control. Above it, a Combo Box allows users to choose the field for text search; in this case, the Last Name field is selected. This will be used to locate matching data. As users type into the filter input TextBox, the system will match the beginning of the selected field value with the entered text and filter the records accordingly. This arrangement allows users to quickly and efficiently search for and filter records based on the starting character(s), making it easier to find specific names that meet the criteria provided.

In this example, three records are initially filtered, each starting with the letter 'G' in the Last Name field. When you type 'r' after 'G' in the filter control with the yellow background, the first record, which contains the letter 'o', no longer matches and disappears from the results. This dynamic filtering approach enables precise and efficient record retrieval, adjusting in real time based on user input. It helps users quickly find records that meet their search criteria.

When the Backspace key is pressed to delete the last character entered from the filter control TextBox, the data dynamically updates to reflect the change in filtering based on the remaining characters. The filter adjusts accordingly, showing records that meet the new criteria. If no characters remain in the filter control, the entire dataset reappears in the form's detail section, allowing users to view all records. This approach creates a seamless and intuitive filtering experience, with real-time updates that respond 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 that fire simple Events that we need to capture and run simple Code, except the TextBox KeyUp() Event. 

The Wrapper Class Object creation is not necessary in this case for TextBox, ComboBox, and Command Button because there is only one instance of these controls on the Form.

In the Global Declaration area, the main Object Instances are declared, qualified with the WithEvents Keyword to enable and capture certain Events and execute the Event Subroutines, in the Cls_ObjInit Class Module. Another Variant Type Variable txt2Filter is also declared in the global area followed by the Form Property procedures.

Next, the Class_Init() Subroutine is called from the Set m_Frm() Property Procedure after receiving the Form Object from the Form_Load() Subroutine in the Form Module.

Next, the txt, cmd, and cbo objects are assigned with the References of these controls on the Form.

Next, the Objects are enabled with the required Events.

The Combobox Click_Event selects a particular Field Name as the target field to filter the records. This Subroutine will reset the filter applied earlier.

The Command Button Click Event closes the Form.

The TextBox, where we enter the Filter Input text, fires the KeyUp() Event, and the valid Key Code received is identified and added to a String, character by character, and used as Filter Criteria at each step in the selected Field in the Combo Box.

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 Keypress removes the last character entered into the Filter Text input Textbox. Right-Arrow character Code is also valid that moves the I bar to the END of the Filter text and prevents highlighting the full text when the Input TextBox 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.

Filter By Character In ComboBox

The above Screenshot is the second Form Customers_Combo implements the 'Filter By Character' in the Combobox Items. The KeyUP() Event Subroutine is almost the same as the first Form Filter method we saw earlier. A new Cbo_ObjInit Class Module is added for this Form. The Cbo_ObjInit Class Module VBA Code is given 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 method, we used the Form Filter method to filter the Form Source Records based on the Filter Criteria characters entered into a TextBox.

The Combobox method builds an SQL Dynamically using the Filter Text entered into a TextBox. The SQL is used as a Row Source of the ComboBox and refreshes the Combobox contents with the changed Criteria.

Download Demo Database


  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
Share:

Streamline Zoom-in Control Data Editing

 Streamlining Form Module Code in Standalone Class Module.

Editing Large Text Box Contents in Zoom-in Control.

This topic was initially Published in August 2007 and titled "Edit Data in Zoom-in Control." In the preceding example, a custom shortcut menu was devised and linked to the form to activate the zoom-in control, enabling the editing of textbox contents with multiple lines of data, akin to the notes field in the Employees Table.

The earlier version of the customized Shortcut Menu for the Employees Form is in the image below for reference.

The built-in Shortcut Menu underwent customization by designing a new button image, highlighted in the Form image by a red oval shape. This button, resembling a CRT screen, is programmed with the necessary code to execute upon clicking. However, the process of designing a new button image within the existing shortcut menu proved to be very difficult.

 Besides that, opening the Zoom Control is a two-step process, first right-click on the TextBox to display the Shortcut Menu then select the Zoom option to open the Zoom-in Form with the TextBox Contents. 

But, now we have a better method, only right-click on a TextBox to open the Zoom-in Form with the Data from that TextBox. Edit the TextBox contents then click the [Save] Command Button on the Zoom Form to replace the data back into the TextBox.

The New Version of Zoom-in Form Image.

The New Employees Form with the Zoom-in Control with the Notes Field Data is given below:

Besides copying the Data from the Employees Notes TextBox into the large TextBox in Zoom Form it is formatted with the same TextBox formatting attributes on the Employees Form. 

The Main Public Functions.

There are two Public Functions that run this procedure. 

The ZoomOpen() Function.

Public Function ZoomOpen()
'------------------------------------------------------
'Function : Edit Data in Zoom-in Control.
'Author   : a.p.r.pillai
'Date     : 29/07/2007, 26/03/2024
'Rights(c): www.msaccesstips.com
'------------------------------------------------------
Dim varVal, ctl As Control, intFontWeight As Integer
Dim strFont As String, intFontSize As Integer
Dim boolFontstyle As Boolean
Dim lngfontColor As Long, boolFontUnderline As Boolean
Dim bkgColor As Long

On Error GoTo ZoomOpen_Err

Set ctl = Screen.ActiveControl
With ctl
   strFont = .FontName
   intFontSize = .FontSize
   intFontWeight = .FontWeight
   boolFontstyle = .FontItalic
   boolFontUnderline = .FontUnderline
   lngfontColor = .ForeColor
   'bkgColor = .BackColor
End With

   varVal = Screen.ActiveControl.Value
   DoCmd.OpenForm "Zoom", acNormal

With Screen.ActiveForm.Controls("TxtZoom")
   .Value = varVal
   .FontName = strFont
   .FontSize = intFontSize
   .FontWeight = intFontWeight
   .FontItalic = boolFontstyle
   .FontUnderline = boolFontUnderline
   .ForeColor = lngfontColor
   '.BackColor = bkgColor
End With

ZoomOpen_Exit:
Exit Function

ZoomOpen_Err:
Resume ZoomOpen_Exit
End Function

Right-clicking on a TextBox runs this ZoomOpen() Function, copies the TextBox contents into a Variant type Variable, opens the Zoom-in Form, and transfers the copied data into the Large TextBox on the Zoom Form. The original text formatting attribute values are applied in the Zoom-in TextBox Text.

After editing the Text click on the [Save] Command Button to save the changes back into the original Textbox of the Employees Form, and close the Zoom-in Form.

The Zoom-in Form can be dragged and moved to a convenient position in the Application Window.  The Zoom Form will open in Popup and Modal Mode and you must close it to access other Forms. 

The SaveZoomData() Function.

The [Save] Command Button Click Runs the SaveZoomData() Function.  The VBA Code is given below.

Public Function SaveZoomData()
'------------------------------------------------------
'Function : Save Edited Data in the Control
'Author   : a.p.r.pillai
'Date     : 29/07/2007, 26/03/2024
'Rights(c): www.msaccesstips.com
'------------------------------------------------------
Dim vartxtZoom, strControl As String

On Error GoTo SaveZoomData_Err

 vartxtZoom = Forms("Zoom").Controls("txtZoom").Value

 DoCmd.Close acForm, "zoom"
 
 If Screen.ActiveControl.Locked = True Then
   strControl = Screen.ActiveControl.Name
   MsgBox strControl & " is Read-Only, Changes discarded!"
   Exit Function
 Else
    If IsNull(vartxtZoom) = False And Len(vartxtZoom) > 0 Then
        Screen.ActiveControl.Value = vartxtZoom
    End If
 End If
 
SaveZoomData_Exit:
Exit Function

SaveZoomData_Err:
Resume SaveZoomData_Exit
End Function

The SaveZoomData() Function saves the edited data into its Source TextBox. If the Textbox is locked, the edited data cannot be saved.

In both the above Functions we used the Screen Object to address the Active Form or Active TextBox Control without using their object names directly, like Screen.ActiveForm, Screen.ActiveControl that has the Focus.

As I stated earlier all you need to do is right-click on the TextBox to open the Zoom-in control and present the TextBox contents in the Zoom Window for editing. 

If you insist on a Shortcut Menu to click and open the Zoom Control, I designed a small Macro-based Shortcut Menu. It can be inserted into the 'Shortcut Menu Bar'  Property of the Form or the same Property of any other Control on the Form. If inserted into the Form's Property the Shortcut Menu will appear when you Right-click anywhere on the Form, not necessarily on a Control like TextBox.

1. The Macro Shortcut Menu Options.

There are two Shortcut Menu options we planned to display in the Macro. 

  1. Open Zoom
  2. Cancel

The macro Commands for the Shortcut Menu Bar are listed in the McrZoom Macro Image shown below:

There are two Options in the Macro Image given above. The first option Runs the ZoomOpen() Function that opens the Zoom Form with the active TextBox control's Text Data in the Zoom Control for editing. The second option simply Cancels the Right-Click Event.

2. Create the Menu Macro.

We need to create a Menu Macro and insert the Menu Options Macro: McrZoom into the Menu Macro. The Menu Macro Image is given below:

The Menu Macro name is McrControlShortcut.

The 'Shortcut Menu Bar' Property of Form and Controls.

The Menu Macro can be inserted into the Shortcut Menu Bar Property of the Form or in the same Property of the Controls on the Form.

When added to the Form Property the Menu appears wherever you Right-Click on the Form. When added to a specific Control's Shortcut Menu Bar Property the Menu appears for that Control only.

Most controls on the form have the 'Shortcut Menu Bar' property, allowing you to insert a menu macro name to display the shortcut menu. When inserted into the TextBox's property, you can even right-click on the child label of the TextBox to bring up the shortcut menu.

Normally, on the OnClick Event Property of a Command Button or a TextBox, we can insert a Macro or a Public Function Name that executes it directly, when it receives a Mouse Button Click.

Despite the 'Shortcut Menu Bar' property expecting a menu bar, it directly executes the macro or function name inserted into this property when the control receives a Right-click Event. Additionally, it briefly displays a small empty menu bar.

Examples:

  1. Text0.Shortcut Menu Bar = "Macro2"
  2. Text0.Shortcut Menu Bar = "=ZoomOpen()"

We will experiment with both methods. Created the Macro Menu as above for easy implementation as well.

This time in the Streamlining of VBA Code we don't have any Object-level Wrapper Classes except the Interface Class Cls_Objinit, where we can experiment with both methods of running the Zoom Control usage. 

The Zoom Form with txtZoom TextBox.

The Zoom Form Image is given below for Reference.

The Zoom Form has two Command Buttons. One to Save the Edited data into its Source Textbox and the other to Cancel the operation. Both Command Button Clicks Subroutines are written in the Form Module only

Option Compare Database
Option Explicit

Private Sub cmdSave_Click()
  Call SaveZoomData
End Sub

Private Sub cmdCancel_Click()
  DoCmd.Close acForm, "Zoom"
End Sub

The Interface Class Module Cls_ObjInit

Option Compare Database
Option Explicit

Private frm As Access.Form

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

Public Property Set m_Frm(ByRef vForm As Form)
  Set frm = vForm
  
  Call Class_Init
End Property

Private Sub Class_Init()
Dim opt As String

opt = "McrControlShortcut"

'opt = "=ZoomOpen()" 'Call Function directly on Right-Click

frm.ShortcutMenu = True 'True by default
Call SetupControls(opt)
       
End Sub

Private Sub SetupControls(ByVal strOpt As String)
Dim ctl As Control

For Each ctl In frm.Controls
  Select Case ctl.ControlType
Case acTextBox
    'ctl.ShortcutMenuBar = StrOpt 'For all TextBoxes
            
      Select Case ctl.Name 'Only selected TextBoxes
          Case "Title", "Address", "Notes"
          
            ctl.ShortcutMenuBar = strOpt
            
      End Select

  End Select
Next
End Sub

Private Sub Class_Terminate()
Dim opt As String

opt = ""
Call SetupControls(opt)

End Sub

The Cls_ObjInit Interface Class has the declaration of a Form object in the Global Area followed by the Property Procedures for the Form.

After receiving the active Form Object in the frm Property call the Class_Init() Subroutine. The Class_Init() and Class_Terminate() Subroutines use a common Subroutine SetUpControls() only to set the 'Shortcut Menu Bar' Property in the Employees Form with the macro-based Shortcut Menu or to run the ZoomOpen() Function directly. The Class_Terminate() Subroutine resets the Shortcut Menu Bar Property. So the change happens dynamically with the use of the Standalone Class Module Cls_ObjInit

With a small change in the Class_Init() Subroutine of the Cls_ObjInit Class, we can either set the Shortcut Menu Bar Property of all the TextBoxes (or any other Control) or only selected TextBox controls on the Form. 

The Trial Runs.

In the Class_Init() Subroutine we plan to Call the SetUpControl() Subroutine to set the 'Shortcut Menu Bar' Property of the TextBox Control with two different settings alternately to try it out and learn how both works. Check the following lines of Code:

opt = "McrControlShortcut"

'opt = "=ZoomOpen()" 'Call Function directly on Right-Click

frm.ShortcutMenu = True 'True by default

The second line of code is kept disabled.  The line frm.ShortcutMenu = True is the default setting of the Form. If the Shortcut Menu doesn't appear as expected check this Property of the Form and correct the setting.

Before invoking the SetupControl() subroutine, the parameter variable "Opt" is initialized with the macro menu name "McrControlShortcut". The subroutine will then assign the menu macro name to the Textbox Control's 'Shortcut Menu Bar' property. Upon executing the code with this option, the shortcut menu will appear when the TextBox receives the right-click event. Selecting the first option 'Open Zoom' from the shortcut menu executes the Public function "ZoomOpen()". The "Cancel" option in the menu cancels the right-click event.

When the second option in the Class_Init() Subroutine, is now kept disabled, when used the Right-Click Event directly executes the Function ZoomOpen().

Similarly, the SetupControl() Subroutine has two options for your preferred use. The default method is to assign the 'Shortcut Menu Bar' Property to only a few selected TextBoxes, where we expect an overflow of Data beyond the boundary of the TextBox(s). The selected fields are Title, Address, and Notes Fields. Can be added with more Fields based on requirements.

If we plan to implement it on all the TextBoxes on the Form then the 'ctl.ShortcutMenuBar = StrOpt can be enabled and the following lines of VBA Code can be removed.

      Select Case ctl.Name 'Only selected TextBoxes
          Case "Title", "Address", "Notes"
          
            ctl.ShortcutMenuBar = strOpt
            
      End Select

The Employees Form Module VBA Code.

Option Compare Database
Option Explicit

Dim Cl As New Cls_ObjInit

Private Sub Form_Load()
Set Cl.m_Frm = Me
End Sub

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

The Interface Class Cls_ObjInit is declared and Instantiated in the global declaration area of the Employees Form. In the Form_Load() Event Procedure the Current Form Object is passed to the Form Property Procedure and from there the Class_Init() Subroutine is called to Set the Shortcut Menu Bar Property of the TextBoxes in the Employees Form.

This trick works on the Tabular and Datasheet Forms too. Two Demo Forms, Tabular and Datasheet Employees Forms are also provided in the Demo Database.

Download the Demo Database.



  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
Share:

PRESENTATION: ACCESS USER GROUPS (EUROPE)

Translate

PageRank

Post Feed


Search

Popular Posts

Blog Archive

Powered by Blogger.

Labels

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