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

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

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

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

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

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

Read More