Table Query Records in Collection Object

Class Module and Collection Object.

1. 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 Footer of the form.

      • 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, ensuring that the data cannot be edited.

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 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 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 of the Employees form. 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 finally the Class_Init() The 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 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.

'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, retrieving 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

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 Eleven
12. Streamlining Report Module Code in Class Module
13. Streamlining Module Code Report Line Hiding-13.
14. Streamlining Form Module Code Part-14.
15. Streamlining Custom Made Form Wizard-15.
16. Streamlining VBA Custom Made Report Wizard-16.
17. Streamlining VBA External Files List in Hyperlinks-17
18. Streamlining Events VBA 3D Text Wizard-18
19. Streamlining Events VBA RGB Color Wizard-19
20. Streamlining Events Numbers to Words-20
21. Access Users Group(Europe) Presentation-21
22. The Event Firing Mechanism of MS Access-22
23. One TextBox and Three Wrapper Class Instances-23
24. Streamlining Code Synchronized Floating Popup Form-24
25. Streamlining Code Compacting/Repair Database-25
26. Streamlining Code Remainder Popup Form-26
27. Streamlining Code Editing Data in Zoom-in Control-27
28. Streamlining Code Filter By Character and Sort-28
29. Table Query Records in Collection Object-29
30. Class for All Data Entry Editing Forms-30
31. Wrapper Class Module Creation Wizard-31
32. wrapper-class-template-wizard-v2

Read More

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

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.

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 substantial project development time. 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 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 for struggling 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 effectively implement the concept of 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, offering valuable insights into this coding approach.

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.

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 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 quick and efficient 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 is unnecessary for the TextBox, ComboBox, and Command Button, 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 against 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. Right-arrow character Code is also valid, which moves the I bar to the END of the Filter text and prevents highlighting 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 using the filter text entered into a TextBox. This SQL is then assigned to the Row Source of the ComboBox, refreshing its contents in real time based on the updated criteria.

Download Demo Database

FilterByChar2016.zip

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 Eleven
12. Streamlining Report Module Code in Class Module
13. Streamlining Module Code Report Line Hiding-13.
14. Streamlining Form Module Code Part-14.
15. Streamlining Custom Made Form Wizard-15.
16. Streamlining VBA Custom Made Report Wizard-16.
17. Streamlining VBA External Files List in Hyperlinks-17
18. Streamlining Events VBA 3D Text Wizard-18
19. Streamlining Events VBA RGB Color Wizard-19
20. Streamlining Events Numbers to Words-20
21. Access Users Group(Europe) Presentation-21
22. The Event Firing Mechanism of MS Access-22
23. One TextBox and Three Wrapper Class Instances-23
24. Streamlining Code Synchronized Floating Popup Form-24
25. Streamlining Code Compacting/Repair Database-25
26. Streamlining Code Remainder Popup Form-26
27. Streamlining Code Editing Data in Zoom-in Control-27
28. Streamlining Code Filter By Character and Sort-28
29. Table Query Records in Collection Object-29
30. Class for All Data Entry Editing Forms-30
31. Wrapper Class Module Creation Wizard-31
32. wrapper-class-template-wizard-v2

Read More