INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Find as you type combobox class that allows requery

Find as you type combobox class that allows requery

3
(OP)
thread702-1756368: Auto-complete combo box...continuation of thread "Combobox as Find As You Type?"

I wanted to share an update of the fayt class since the discussion closed 6 months ago. I was stumped trying to find a way to reload the records of the combo box with a different query. I have two use cases for needed to requery the combo box. In one case I have cascading comboboxes and my fayt combo needs to adjust based on the selection of another combo. In another case I have a checkbox on the form that says "show all records". The form normally opens to show a subset of records but there are a few occasions when they need to see all records in the table. You could also imagine this checkbox saying "include archived records". All the ways I tried to requery the recordset caused errors. I was at an Access developers conference in Oregon last week and one evening I got some additional brains looking at the issue. At one point we thought we had it fixed and could requery the combo 3 times but on the 4th time it would crash. That had us scratching our heads. Eventually we found a sequence of code that works.

Here are some discoveries that were made:
  • It's best if you don't try to requery the list outside of the class. I'm not sure I can put the issue into words, but there could be a conflict between the recordset of the combobox and the mRsOriginalList recordset in the class that is a clone. The RequeryList method of the class should be used. This way the mRsOriginalList recordset can be reset and a few other tweaks can take place.
  • Changing the RowSource of the combobox does not always cause a requery of the combobox. We had some discussion about this but in testing this class there were many times that the data was not refreshed. The previous version of this class by MajP called the .Dropdown method which would force the list to populate if it was empty, but also caused the screen to flash. I discovered that requesting .ListCount into a variable would do the same thing but not cause the screen to flash. In my testing, both .ListCount and Requery need to be called, otherwise 'Error #91 - Object variable or With block variable not set' would occur.
  • This version supports queries that have references to controls on open forms. Earlier I wrote that "it fails because my RowSource contained a query that had a VBA function in it" but I should have said it was SQL containing things like [forms].[frmXYZ].[txtFilter]. Those references don't work when loading a DAO recordset, but they do work when loading a combobox.
I have used this class with a combobox containing over 12,000 records. The find-as-you-type is quick and does not repeatedly hit the database tables. This class is a big help if you are working with unwieldy list sizes.

If I find any more issues I'll try to update this thread. I was a forum lurker for a long time and I want to make sure I give something back.

Ben


CODE --> VBA

Option Compare Database
Option Explicit

'Class Module Name: FindAsYouTypeCombo
'Purpose: Turn any combobox into a "Find As You Type" 'Combobox
'Created by: MajP
'Demonstrates: OOP, and With Events
'
'Use: To use the class, you need a reference to DAO and code
'similar to the following in a form's module.

' Discussion of this class found here:  http://www.tek-tips.com/viewthread.cfm?qid=1756368
' Ben:  This is where I tried to work out how to refresh the combo box when its
'       RowSource changed.  I eventually worked it out at PAUG 2016 with help from
'       Dirk E. and Thomas M.  In short, don't mess with the combo RowSource
'       outside of this class, and make sure mRsOriginalList is reset at the same time.

'Parmeters:
'  TheComboBox: Your Combobox object passed as an object
'  FilterFieldName: The name of the field to Filter as
'    string
'  FilterFromStart: Determines if you filter a field that
'    starts with the text or if the text appears anywhere
'    in the record.
'  HandleArrows:  Determines if up/down arrow keys stop the
'    scrolling of the dropdown from affecting the filter.
'
'*******START: Form Code*******************
'
' Option Compare Database
' Option Explicit
' PRIVATE faytProducts As New clsFindAsYouTypeCombo
' Form_Open(Cancel As Integer)
'   faytProducts.InitalizeFilterCombo Me.cmbProducts, "ProductName", False, True
' End Sub
'
' If you need to change the RowSource or requery the ComboBox, use this method:
' faytProducts.RequeryList <optional new SQL statement>
'
'******* END: Form Code ******************

Private WithEvents mCombo   As Access.ComboBox
Private WithEvents mForm    As Access.Form
Private mFilterFieldName    As String
Private mRsOriginalList     As DAO.Recordset
Private mFilterFromStart    As Boolean

Private mHandleArrows       As Boolean  ' BS 10/13/2015
Private mAutoCompleteEnabled As Boolean ' BS 10/13/2015

'Public Property Get FilterComboBox() As Access.ComboBox
'    Set FilterComboBox = mCombo
'End Property
'
'Public Property Set FilterComboBox(TheComboBox As Access.ComboBox)
'    Set mCombo = TheComboBox
'End Property
'
'Public Property Get FilterFieldName() As String
'    FilterFieldName = mFilterFieldName
'End Property
'
'Public Property Let FilterFieldName(ByVal theFieldName As String)
'    mFilterFieldName = theFieldName
'End Property
'
'Public Sub DestroyObject()
'    mRsOriginalList.Close
'    Set mRsOriginalList = Nothing
'End Sub


Public Sub InitalizeFilterCombo(TheComboBox As Access.ComboBox, FilterFieldName As String _
    , Optional FilterFromStart = False _
    , Optional HandleArrows As Boolean = True)
   
' Ben:  Added 4th paramenter (optional) to support my preference on how this
'       combo box should 'feel'.  When this parameter is TRUE, using the up/down
'       arrow keys and page up/down in the combobox will stop the fayt filter
'       from adding the first highlighted list item to the filter.

    On Error GoTo ErrorHandler
    
    If Not TheComboBox.RowSourceType = "Table/Query" Then
        MsgBox "This class will only work with a combobox that uses a Table or Query as the Rowsource"
        Exit Sub
    End If
    
    Set mCombo = TheComboBox
    Set mForm = TheComboBox.Parent
    mFilterFieldName = FilterFieldName
    mFilterFromStart = FilterFromStart
    mForm.OnCurrent = "[Event Procedure]"
    mCombo.OnGotFocus = "[Event Procedure]"
    mCombo.OnChange = "[Event Procedure]"
    mCombo.AfterUpdate = "[Event Procedure]"
   
    mHandleArrows = HandleArrows
    If mHandleArrows = True Then
        mCombo.OnKeyDown = "[Event Procedure]"  ' BS 10/13/2015
        mCombo.OnClick = "[Event Procedure]"    ' BS 10/13/2015
    End If
    
    Dim i As Long
    With mCombo
        ' The following was added to handle when delayed RowSource loading has been set up.  BS 1/7/2016
        If .RowSource = "" Then
            .RowSource = .Tag
        End If
        
       i = .ListCount  ' This forces the combo recordset to populate without the screen flashing or forcing Form_Load.  ' BS 5/9/2016

'        .SetFocus       ' This forces Form_Load if it hasn't run yet.
'        i = .ListRows
'        .ListRows = 1   ' Reduce the amount of flashing from the next line.
'        .Dropdown       ' This forces the combo recordset to populate.
'        .ListRows = i

        .AutoExpand = False
    End With
   
' This is an alternative method but it does not work if the RowSource has a
' reference in it to a control on a form.
'    Set mRsOriginalList = CurrentDb.OpenRecordset(mCombo.RowSource, dbOpenSnapshot)
'    Set mCombo.Recordset = mRsOriginalList
       
    Set mRsOriginalList = mCombo.Recordset.Clone
   
    Exit Sub
   
ErrorHandler:
    MsgBox "Error #" & Err.Number & " - " & Err.Description & vbCrLf & "in procedure InitalizeFilterCombo of clsFindAsYouTypeCombo"
    Debug.Print Err.Number, Err.Description
    Exit Sub
'    Resume Next
    Resume
End Sub

Private Sub Class_Terminate()
    Set mForm = Nothing
    Set mCombo = Nothing
    mRsOriginalList.Close
    Set mRsOriginalList = Nothing
End Sub

Private Sub FilterList()

    On Error GoTo ErrorHandler
    
    Dim rsTemp As DAO.Recordset
    Dim strText As String
    Dim strFilter As String
    
    If mAutoCompleteEnabled = False Then
        ' Don't filter when keystrokes like return, up/down, page up/down are entered.  BS 10/15/2015
    '        Beep
        Exit Sub
    End If
    
    strText = mCombo.Text
    If mFilterFieldName = "" Then
        MsgBox "Must Supply A FieldName Property to filter list."
        Exit Sub
    End If
    If mFilterFromStart = True Then
        strFilter = mFilterFieldName & " like '" & strText & "*'"
    Else
        strFilter = mFilterFieldName & " like '*" & strText & "*'"
    End If
    Set rsTemp = mRsOriginalList.OpenRecordset
    rsTemp.Filter = strFilter
    Set rsTemp = rsTemp.OpenRecordset
    
    If rsTemp.RecordCount > 0 Then
        Set mCombo.Recordset = rsTemp
    Else
        ' No records found for this filter.  Alert the user so they don't keep typing.
        Beep
    End If
    
    If Len(strText) > 0 Then
        mCombo.Dropdown
    Else
        ' Don't make the dropdown appear if the user just cleared the field.
    End If
    
    Exit Sub
  
ErrorHandler:
    If Err.Number = 3061 Then
        MsgBox "Will not Filter. Verify Field Name is Correct."
    Else
        MsgBox "Error #" & Err.Number & " - " & Err.Description & vbCrLf & "in procedure FilterList of clsFindAsYouTypeCombo"
    End If
    
End Sub

Private Sub unFilterList()
    On Error GoTo ErrorHandler
    
    Set mCombo.Recordset = mRsOriginalList
    Exit Sub
    
ErrorHandler:
    If Err.Number = 3061 Then
        MsgBox "Will not Filter. Verify Field Name is Correct."
    Else
        MsgBox Err.Number & "  " & Err.Description
    End If
End Sub

Private Sub mCombo_AfterUpdate()
    Call unFilterList
End Sub

Private Sub mCombo_Change()
    Call FilterList
End Sub

Private Sub mCombo_Click()
    ' When a value is selected from the list and populates the box, don't let that
    ' cause the list to be requeried.  BS 10/13/2015
    mAutoCompleteEnabled = False
End Sub

Private Sub mCombo_GotFocus()
'' BS 10/13/2015:  I commented out the next line because I don't like
'   this behavior when tabbing through controls on the form, especially
'   when a couple of combo boxes are vertically stacked.
' This causes the dropdown to load when the SET event initializes, so it must be here unless it's called in InitalizeFilterCombo().
'    mCombo.Dropdown
End Sub

Private Sub mCombo_KeyDown(KeyCode As Integer, Shift As Integer)
' Handle keys that affect the auto-complete feel of the combobox.  BS 10/13/2015

    If mHandleArrows = True Then
' BS 10/15/2015:  I'm still not sure if I want this behavior.  At first it felt natural but now I'm not sure it's good.
'        If KeyCode = vbKeyReturn And mCombo.ListCount >= 1 And mAutoCompleteEnabled = True Then 'And mCombo.ListIndex = -1 Then
'            ' If the user pressed Enter and at least one value is in the list
'            ' then pick that item.
'            ' When this code fires sometimes the AfterUpdate event does not.
'            ' How can you force the AfterUpdate to fire?
'            Beep
'            mCombo.value = mCombo.ItemData(0)
'            'Debug.Print "KeyDown: " & mCombo, mCombo.ListCount, mCombo.ListIndex
'            mCombo.SetFocus
'        End If
        
        Select Case KeyCode
            Case vbKeyDown, vbKeyUp, vbKeyReturn, vbKeyPageDown, vbKeyPageUp
                ' When these special keys are hit they begin to select records
                ' from the dropdown list.  Without this, as soon as one record
                ' is selected (by highlighting it) then the entire filter is
                ' set to that item making it impossible to use the keyboard to
                ' scroll down and pick an item down in the list.
                mAutoCompleteEnabled = False
            Case Else
                mAutoCompleteEnabled = True
        End Select
    End If

End Sub

Private Sub mForm_Current()
    Call unFilterList
End Sub

Public Sub RequeryList(Optional pRowSource As String = "")
' This class method only needs to be called when the combobox has a new rowsource,
' like when other controls affect what it should show, or the case of a cascading combobox.
'### BEST PRACTICE ###
' Note that when using the Find-as-you-type combo, if you need to change the RowSource
' you should pass the new rowsource to the RequeryList method and do not try to change
' the source from outside of the class module.  If you make changes outside of the class
' it may appear to work for 3-4 iterations but fail after that.

    Dim i As Long
    
    On Error GoTo ErrorHandler

    DoCmd.Hourglass True
    StatusBar "Refreshing " & mCombo.Name & "..."
    DoEvents
'    Debug.Print mCombo.Name;  ', mCombo.RowSource
    
    If Not mRsOriginalList Is Nothing Then
        mRsOriginalList.Close
    End If
    Set mRsOriginalList = Nothing
    
    If Len(pRowSource) > 0 Then
        mCombo.RowSource = pRowSource
    End If
    
    ' You have to do something here to force the recordset to requery.  Some people
    ' would argue that changing the RowSource forces a requery but I didn't experience
    ' that in this situation.
    i = mCombo.ListCount    ' This forces the combo recordset to populate without the screen flashing or forcing Form_Load.  ' BS 5/9/2016
                            ' Without the line above you will get random errors with the recordset:
                            '     Error #91 - Object variable or With block variable not set
    mCombo.Requery

'    Debug.Print mCombo.Recordset.RecordCount
    Set mRsOriginalList = mCombo.Recordset.Clone
    
' This is an alternative method but it does not work if the RowSource has a
' reference in it to a control on a form.
'    Set mRsOriginalList = CurrentDb.OpenRecordset(pRowSource, dbOpenSnapshot)
'    Set mCombo.Recordset = mRsOriginalList


Exit_Sub:
    DoCmd.Hourglass False
    StatusBar ""

    Exit Sub

ErrorHandler:
    MsgBox "Error #" & Err.Number & " - " & Err.Description & vbCrLf & "in procedure RequeryList of clsFindAsYouTypeCombo"
    GoTo Exit_Sub
    
    Resume
End Sub


Private Sub StatusBar(pstrStatus As String)
' http://www.mrexcel.com/forum/microsoft-access/233681-access-visual-basic-applications-application-statusbar.html

    Dim lvarStatus As Variant
    
    If pstrStatus = "" Then
        lvarStatus = SysCmd(acSysCmdClearStatus)
    Else
        lvarStatus = SysCmd(acSysCmdSetStatus, pstrStatus)
    End If
    
End Sub 

RE: Find as you type combobox class that allows requery

I can't believe this.
I was struggling with this same subject during the last few days. I finally arrived to a conclusion - no other way but to go for a Class.
Imagine my surprise to find this fresh post.
Sceptical at first it turned out that I had to change only 2 lines to make it work - nothing major - just SQL wild cards due to ANSI-92.
GREAT WORK! And just on time (in my case) :)
Still haven't tested it too much but so far surely works.

Thanks a lot.

Below are the 2 small changes i needed to make (for my case: ANSI-92) in the FilterList() Procedure:

CODE --> VBA

Private Sub FilterList()

    On Error GoTo ErrorHandler
    
    Dim rsTemp As DAO.Recordset
    Dim strText As String
    Dim strFilter As String
    
    If mAutoCompleteEnabled = False Then
        ' Don't filter when keystrokes like return, up/down, page up/down are entered.  BS 10/15/2015
    '        Beep
        Exit Sub
    End If
    
    strText = mCombo.Text
    If mFilterFieldName = "" Then
        MsgBox "Must Supply A FieldName Property to filter list."
        Exit Sub
    End If
    If mFilterFromStart = True Then
'        strFilter = mFilterFieldName & " like '" & strText & "*'"
        strFilter = mFilterFieldName & " ALIKE '" & strText & "%'"
    Else
'        strFilter = mFilterFieldName & " like '*" & strText & "*'"
        strFilter = mFilterFieldName & " ALIKE '%" & strText & "%'"
    End If
    Set rsTemp = mRsOriginalList.OpenRecordset
    rsTemp.Filter = strFilter
    Set rsTemp = rsTemp.OpenRecordset
    
    If rsTemp.RecordCount > 0 Then
        Set mCombo.Recordset = rsTemp
    Else
        ' No records found for this filter.  Alert the user so they don't keep typing.
        Beep
    End If
    
    If Len(strText) > 0 Then
        mCombo.Dropdown
    Else
        ' Don't make the dropdown appear if the user just cleared the field.
    End If
    
    Exit Sub
  
ErrorHandler:
    If Err.Number = 3061 Then
        MsgBox "Will not Filter. Verify Field Name is Correct."
    Else
        MsgBox "Error #" & Err.Number & " - " & Err.Description & vbCrLf & "in procedure FilterList of clsFindAsYouTypeCombo"
    End If
    
End Sub 

RE: Find as you type combobox class that allows requery

I got so excited that I couldn't stand proposing another minor change: filtering on several fields:
Just in case anyone is desperately seeking for this somewhere out there.

If you provide a list of field names(separated by semicolon) then the filter string will be applied to any of them.
If Form usage example:

CODE --> vba

Option Compare Database
 Option Explicit

 Private faytClient As New clsFindAsYouTypeCombo

Private Sub Form_Open(Cancel As Integer)
   faytClient.InitalizeFilterCombo Me.ClientID, "NativeName;Alias;Alpha3", False, True
   
End Sub 

I include the complete code although the change is only in the FilterList() procedure:

CODE --> vba

Option Compare Database
Option Explicit

'Class Module Name: FindAsYouTypeCombo
'Purpose: Turn any combobox into a "Find As You Type" 'Combobox
'Created by: MajP
'Demonstrates: OOP, and With Events
'
'Use: To use the class, you need a reference to DAO and code
'similar to the following in a form's module.

' Discussion of this class found here:  http://www.tek-tips.com/viewthread.cfm?qid=1756368
' Ben:  This is where I tried to work out how to refresh the combo box when its
'       RowSource changed.  I eventually worked it out at PAUG 2016 with help from
'       Dirk E. and Thomas M.  In short, don't mess with the combo RowSource
'       outside of this class, and make sure mRsOriginalList is reset at the same time.

'Parmeters:
'  TheComboBox: Your Combobox object passed as an object
'  FilterFieldName: The name of the field to Filter as
'    string
'  FilterFromStart: Determines if you filter a field that
'    starts with the text or if the text appears anywhere
'    in the record.
'  HandleArrows:  Determines if up/down arrow keys stop the
'    scrolling of the dropdown from affecting the filter.
'
'*******START: Form Code*******************
'
' Option Compare Database
' Option Explicit
' PRIVATE faytProducts As New clsFindAsYouTypeCombo
' Form_Open(Cancel As Integer)
'   faytProducts.InitalizeFilterCombo Me.cmbProducts, "ProductName", False, True
' End Sub
'
' If you need to change the RowSource or requery the ComboBox, use this method:
' faytProducts.RequeryList <optional new SQL statement>
'
'******* END: Form Code ******************

Private WithEvents mCombo   As Access.ComboBox
Private WithEvents mForm    As Access.Form
Private mFilterFieldName    As String
Private mRsOriginalList     As DAO.Recordset
Private mFilterFromStart    As Boolean

Private mHandleArrows       As Boolean  ' BS 10/13/2015
Private mAutoCompleteEnabled As Boolean ' BS 10/13/2015

'Public Property Get FilterComboBox() As Access.ComboBox
'    Set FilterComboBox = mCombo
'End Property
'
'Public Property Set FilterComboBox(TheComboBox As Access.ComboBox)
'    Set mCombo = TheComboBox
'End Property
'
'Public Property Get FilterFieldName() As String
'    FilterFieldName = mFilterFieldName
'End Property
'
'Public Property Let FilterFieldName(ByVal theFieldName As String)
'    mFilterFieldName = theFieldName
'End Property
'
'Public Sub DestroyObject()
'    mRsOriginalList.Close
'    Set mRsOriginalList = Nothing
'End Sub


Public Sub InitalizeFilterCombo(TheComboBox As Access.ComboBox, FilterFieldName As String _
    , Optional FilterFromStart = False _
    , Optional HandleArrows As Boolean = True)
   
' Ben:  Added 4th paramenter (optional) to support my preference on how this
'       combo box should 'feel'.  When this parameter is TRUE, using the up/down
'       arrow keys and page up/down in the combobox will stop the fayt filter
'       from adding the first highlighted list item to the filter.

'bobsan42: try to provide a field names list with ; separator
'
    On Error GoTo ErrorHandler
    
    If Not TheComboBox.RowSourceType = "Table/Query" Then
        MsgBox "This class will only work with a combobox that uses a Table or Query as the Rowsource"
        Exit Sub
    End If
    
    Set mCombo = TheComboBox
    Set mForm = TheComboBox.Parent
    mFilterFieldName = FilterFieldName
    mFilterFromStart = FilterFromStart
    mForm.OnCurrent = "[Event Procedure]"
    mCombo.OnGotFocus = "[Event Procedure]"
    mCombo.OnChange = "[Event Procedure]"
    mCombo.AfterUpdate = "[Event Procedure]"
   
    mHandleArrows = HandleArrows
    If mHandleArrows = True Then
        mCombo.OnKeyDown = "[Event Procedure]"  ' BS 10/13/2015
        mCombo.OnClick = "[Event Procedure]"    ' BS 10/13/2015
    End If
    
    Dim i As Long
    With mCombo
        ' The following was added to handle when delayed RowSource loading has been set up.  BS 1/7/2016
        If .RowSource = "" Then
            .RowSource = .Tag
        End If
        
       i = .ListCount  ' This forces the combo recordset to populate without the screen flashing or forcing Form_Load.  ' BS 5/9/2016

'        .SetFocus       ' This forces Form_Load if it hasn't run yet.
'        i = .ListRows
'        .ListRows = 1   ' Reduce the amount of flashing from the next line.
'        .Dropdown       ' This forces the combo recordset to populate.
'        .ListRows = i

        .AutoExpand = False
    End With
   
' This is an alternative method but it does not work if the RowSource has a
' reference in it to a control on a form.
'    Set mRsOriginalList = CurrentDb.OpenRecordset(mCombo.RowSource, dbOpenSnapshot)
'    Set mCombo.Recordset = mRsOriginalList
       
    Set mRsOriginalList = mCombo.Recordset.Clone
   
    Exit Sub
   
ErrorHandler:
    MsgBox "Error #" & Err.Number & " - " & Err.Description & vbCrLf & "in procedure InitalizeFilterCombo of clsFindAsYouTypeCombo"
    Debug.Print Err.Number, Err.Description
    Exit Sub
'    Resume Next
    Resume
End Sub

Private Sub Class_Terminate()
    Set mForm = Nothing
    Set mCombo = Nothing
    mRsOriginalList.Close
    Set mRsOriginalList = Nothing
End Sub

Private Sub FilterList()

    On Error GoTo ErrorHandler
    
    Dim rsTemp As DAO.Recordset
    Dim strText As String
    Dim strFilter As String
    
    Dim strTemp As String
    Dim iii As Integer
    
    If mAutoCompleteEnabled = False Then
        ' Don't filter when keystrokes like return, up/down, page up/down are entered.  BS 10/15/2015
    '        Beep
        Exit Sub
    End If
    
    strText = mCombo.Text
    
    
    If mFilterFieldName = "" Then
        MsgBox "Must Supply A FieldName Property to filter list."
        Exit Sub
    End If
    
    For iii = LBound(Split(mFilterFieldName, ";")) To UBound(Split(mFilterFieldName, ";"))
            strTemp = Split(mFilterFieldName, ";")(iii)
            
            If strTemp <> "" Then
                If strFilter <> "" Then
                    If mFilterFromStart = True Then
                '        strFilter = mFilterFieldName & " like '" & strText & "*'"
                        strFilter = strFilter & " OR " & strTemp & " ALIKE '" & strText & "%'"
                    Else
                '        strFilter = mFilterFieldName & " like '*" & strText & "*'"
                        strFilter = strFilter & " OR " & strTemp & " ALIKE '%" & strText & "%'"
                    End If
                Else
                    If mFilterFromStart = True Then
                '        strFilter = mFilterFieldName & " like '" & strText & "*'"
                        strFilter = strTemp & " ALIKE '" & strText & "%'"
                    Else
                '        strFilter = mFilterFieeldName & " like '*" & strText & "*'"
                        strFilter = strTemp & " ALIKE '%" & strText & "%'"
                    End If
                End If
            End If
    Next iii
    
    Set rsTemp = mRsOriginalList.OpenRecordset
    rsTemp.Filter = strFilter
    Set rsTemp = rsTemp.OpenRecordset
    
    If rsTemp.RecordCount > 0 Then
        Set mCombo.Recordset = rsTemp
    Else
        ' No records found for this filter.  Alert the user so they don't keep typing.
        Beep
    End If
    
    If Len(strText) > 0 Then
        mCombo.Dropdown
    Else
        ' Don't make the dropdown appear if the user just cleared the field.
    End If
    
    Exit Sub
  
ErrorHandler:
    If Err.Number = 3061 Then
        MsgBox "Will not Filter. Verify Field Name is Correct."
    Else
        MsgBox "Error #" & Err.Number & " - " & Err.Description & vbCrLf & "in procedure FilterList of clsFindAsYouTypeCombo"
    End If
    
End Sub

Private Sub unFilterList()
    On Error GoTo ErrorHandler
    
    Set mCombo.Recordset = mRsOriginalList
    Exit Sub
    
ErrorHandler:
    If Err.Number = 3061 Then
        MsgBox "Will not Filter. Verify Field Name is Correct."
    Else
        MsgBox Err.Number & "  " & Err.Description
    End If
End Sub

Private Sub mCombo_AfterUpdate()
    Call unFilterList
End Sub

Private Sub mCombo_Change()
    Call FilterList
End Sub

Private Sub mCombo_Click()
    ' When a value is selected from the list and populates the box, don't let that
    ' cause the list to be requeried.  BS 10/13/2015
    mAutoCompleteEnabled = False
End Sub

Private Sub mCombo_GotFocus()
'' BS 10/13/2015:  I commented out the next line because I don't like
'   this behavior when tabbing through controls on the form, especially
'   when a couple of combo boxes are vertically stacked.
' This causes the dropdown to load when the SET event initializes, so it must be here unless it's called in InitalizeFilterCombo().
'    mCombo.Dropdown
End Sub

Private Sub mCombo_KeyDown(KeyCode As Integer, Shift As Integer)
' Handle keys that affect the auto-complete feel of the combobox.  BS 10/13/2015

    If mHandleArrows = True Then
' BS 10/15/2015:  I'm still not sure if I want this behavior.  At first it felt natural but now I'm not sure it's good.
'        If KeyCode = vbKeyReturn And mCombo.ListCount >= 1 And mAutoCompleteEnabled = True Then 'And mCombo.ListIndex = -1 Then
'            ' If the user pressed Enter and at least one value is in the list
'            ' then pick that item.
'            ' When this code fires sometimes the AfterUpdate event does not.
'            ' How can you force the AfterUpdate to fire?
'            Beep
'            mCombo.value = mCombo.ItemData(0)
'            'Debug.Print "KeyDown: " & mCombo, mCombo.ListCount, mCombo.ListIndex
'            mCombo.SetFocus
'        End If
        
        Select Case KeyCode
            Case vbKeyDown, vbKeyUp, vbKeyReturn, vbKeyPageDown, vbKeyPageUp
                ' When these special keys are hit they begin to select records
                ' from the dropdown list.  Without this, as soon as one record
                ' is selected (by highlighting it) then the entire filter is
                ' set to that item making it impossible to use the keyboard to
                ' scroll down and pick an item down in the list.
                mAutoCompleteEnabled = False
            Case Else
                mAutoCompleteEnabled = True
        End Select
    End If

End Sub

Private Sub mForm_Current()
    Call unFilterList
End Sub

Public Sub RequeryList(Optional pRowSource As String = "")
' This class method only needs to be called when the combobox has a new rowsource,
' like when other controls affect what it should show, or the case of a cascading combobox.
'### BEST PRACTICE ###
' Note that when using the Find-as-you-type combo, if you need to change the RowSource
' you should pass the new rowsource to the RequeryList method and do not try to change
' the source from outside of the class module.  If you make changes outside of the class
' it may appear to work for 3-4 iterations but fail after that.

    Dim i As Long
    
    On Error GoTo ErrorHandler

    DoCmd.Hourglass True
    StatusBar "Refreshing " & mCombo.Name & "..."
    DoEvents
'    Debug.Print mCombo.Name;  ', mCombo.RowSource
    
    If Not mRsOriginalList Is Nothing Then
        mRsOriginalList.Close
    End If
    Set mRsOriginalList = Nothing
    
    If Len(pRowSource) > 0 Then
        mCombo.RowSource = pRowSource
    End If
    
    ' You have to do something here to force the recordset to requery.  Some people
    ' would argue that changing the RowSource forces a requery but I didn't experience
    ' that in this situation.
    i = mCombo.ListCount    ' This forces the combo recordset to populate without the screen flashing or forcing Form_Load.  ' BS 5/9/2016
                            ' Without the line above you will get random errors with the recordset:
                            '     Error #91 - Object variable or With block variable not set
    mCombo.Requery

'    Debug.Print mCombo.Recordset.RecordCount
    Set mRsOriginalList = mCombo.Recordset.Clone
    
' This is an alternative method but it does not work if the RowSource has a
' reference in it to a control on a form.
'    Set mRsOriginalList = CurrentDb.OpenRecordset(pRowSource, dbOpenSnapshot)
'    Set mCombo.Recordset = mRsOriginalList


Exit_Sub:
    DoCmd.Hourglass False
    StatusBar ""

    Exit Sub

ErrorHandler:
    MsgBox "Error #" & Err.Number & " - " & Err.Description & vbCrLf & "in procedure RequeryList of clsFindAsYouTypeCombo"
    GoTo Exit_Sub
    
    Resume
End Sub


Private Sub StatusBar(pstrStatus As String)
' http://www.mrexcel.com/forum/microsoft-access/233681-access-visual-basic-applications-application-statusbar.html
    Dim lvarStatus As Variant
    If pstrStatus = "" Then
        lvarStatus = SysCmd(acSysCmdClearStatus)
    Else
        lvarStatus = SysCmd(acSysCmdSetStatus, pstrStatus)
    End If
End Sub 

RE: Find as you type combobox class that allows requery

(OP)
@bobsan42: Thanks for sharing you enthusiasm and your code enhancement.

@All: I'm trying to make this a little better and would like some help. In the mCombo_KeyDown() event you'll see have some commented out code that handles the Enter/Return key. After using this class for a while I really think it should take action when I type a filter and press Enter. That's the way the regular ComboBox works, even when only a partial match is found. The problem is finding a way to force the AfterUpdate() event to fire on the control on the form. If you have any ideas on how to do this, please let me know.

RE: Find as you type combobox class that allows requery

Can you explain a little clearer the current behavior and the desired behavior?

RE: Find as you type combobox class that allows requery

(OP)
After I type a few characters I may have reduced the list to a few records which are displayed in the dropdown. In the current behavior, if I press Enter, it gives me an error because I haven't selected an item from the list. The desired behavior is to have it select the first item in the list when I press Enter.

I thought I had developed a solution for the mCombo_KeyDown() event but it had a side-effect of causing the FilterList to run one more time with the full value from the combo. The test data I was working with contained the # character which the filter didn't like. I ran out of time the day and put it aside for a few days. Just now I had the idea of setting ( mAutoCompleteEnabled = False ) which appears to make it work. I haven't tested it heavily but it does pass the previous failure scenario.

Let me know if you have any thoughts on my implementation.

CODE --> VBA

Private Sub mCombo_KeyDown(KeyCode As Integer, Shift As Integer)
' Handle keys that affect the auto-complete feel of the combobox.  BS 10/13/2015
' BS 8/4/2016:  Pressing Enter will now pick the first item in the list.

    On Error GoTo ErrorHandler

    If mHandleArrows = True Then
		' BS 10/15/2015:  I'm still not sure if I want this behavior.  At first it felt natural but now I'm not sure it's good.
        If KeyCode = vbKeyReturn And mCombo.ListCount >= 1 And mAutoCompleteEnabled = True Then 'And mCombo.ListIndex = -1 Then
            ' If the user pressed Enter and at least one value is in the list
            ' then pick the first item.
            
            If Not IsNull(mCombo.ItemData(0)) Then
                mCombo.value = mCombo.ItemData(0)
                Debug.Print mCombo.ListCount, mCombo.value, mCombo.Text
                mAutoCompleteEnabled = False '  BS 8/4/2016:  This will prevent this FilterList event from running one more time.
                mCombo.Text = mCombo.Text ' This forces the _AfterUpdate and _Change events to fire.  I couldn't find a better way.
            End If
        End If
        
        Select Case KeyCode
            Case vbKeyDown, vbKeyUp, vbKeyPageDown, vbKeyPageUp, vbKeyReturn
                ' When these special keys are hit they begin to select records
                ' from the dropdown list.  Without this, as soon as one record
                ' is selected (by highlighting it) then the entire filter is
                ' set to that item making it impossible to use the keyboard to
                ' scroll down and pick an item down in the list.
                mAutoCompleteEnabled = False
            Case Else
                mAutoCompleteEnabled = True
        End Select
    End If

Exit_Sub:
    Exit Sub

ErrorHandler:
    If Err.Number = 2118 Then ' You must save the current field before you run the Requery action.
        ' I'm not sure why this error happens.  It is the result of the above line:  mCombo.Text = mCombo.Text
        Resume Next
    Else
        MsgBox "Error #" & Err.Number & " - " & Err.Description & vbCrLf & "in procedure mCombo_KeyDown of clsFindAsYouTypeCombo", , "clsFindAsYouTypeCombo"
    End If
    GoTo Exit_Sub
    Resume Next
    Resume

End Sub 

RE: Find as you type combobox class that allows requery


Some queries will choke on text that contains a single apostrophe or a number sign. To handle the single apostrophe there is a line of code
strText = Replace(strText, "'", "''")
to replace a single quote with a double quote in the sql.
If the query fails on strings with # signs you could try adding below the above code
strText = Replace(strText, "#", "[#]")

From what I read this may have some issues with pass through queries, but since this is using DAO should be OK. I would have expected this to be an issue, but for some reason it works fine for me with or without the extra code.


I will have to look at your code and try it. Since you added additional features it now works differently than the original version which would select when you typed.

RE: Find as you type combobox class that allows requery

(OP)
I have incorporated the previous suggestion by MaJP and have also made an improvement to the Enter key behavior. See the updated functions that follow.

CODE --> VBA

Private Sub FilterList()
' Create a filter string based on what has been typed so far and update the dropdown list.
' NOTE: The DAO filter supports special search criteria that you should be familiar with.
'       Typing [*] in the combobox will find all items in the list that contain the asterisk.
'       The link below has a good explanation and comparison between DAO, ADO, and T-SQL.
'       http://www.techrepublic.com/article/10-tips-for-using-wildcard-characters-in-microsoft-access-criteria-expressions/
  
    On Error GoTo ErrorHandler
    
    Dim rsTemp      As DAO.Recordset
    Dim strText     As String
    Dim strFilter   As String
    Dim strTemp     As String
    Dim iii         As Integer
    
    If mAutoCompleteEnabled = False Then
        ' Don't filter when keystrokes like return, up/down, page up/down are entered.  BS 10/15/2015
        Exit Sub
    End If
    
    If mFilterFieldName = "" Then
        MsgBox "Must Supply A FieldName Property to filter list."
        Exit Sub
    End If
    
    ' Get the characters that have been typed so far.
    strText = mCombo.Text
    
    ' BS 8/6/2016:  Below added per suggestion from MajP.  http://www.tek-tips.com/viewthread.cfm?qid=1765353
    ' Some queries will choke on text that contains a single apostrophe or a number sign.
    strText = Replace(strText, "'", "''") ' replace a single quote with a double quote in the SQL.
    ' If the query fails on strings with # signs the code below should fix it.
    strText = Replace(strText, "#", "[#]")
        
    ' BS 7/27/2016:  Incorporated delimited filter feature developed by bobsan42, http://www.tek-tips.com/viewthread.cfm?qid=1765353
    ' This allows you to apply the filter to multiple columns in the combobox.
    For iii = LBound(Split(mFilterFieldName, ";")) To UBound(Split(mFilterFieldName, ";"))
        strTemp = Split(mFilterFieldName, ";")(iii)
        
        If strTemp <> "" Then
            If mFilterFromStart = True Then
                'strFilter = strFilter & " OR " & strTemp & " ALIKE '" & strText & "%'" ' For ANSI-92 filtering
                strFilter = strFilter & " OR " & strTemp & " LIKE '" & strText & "*'"   ' For Access Jet filtering
            Else
                'strFilter = strFilter & " OR " & strTemp & " ALIKE '%" & strText & "%'"
                strFilter = strFilter & " OR " & strTemp & " LIKE '*" & strText & "*'"
            End If
        End If
    Next iii
    If Len(strFilter) > 0 Then
        ' Remove the ' OR ' prefix
        strFilter = Mid(strFilter, 5)
    End If
    
    Set rsTemp = mRsOriginalList.OpenRecordset
    rsTemp.Filter = strFilter
    Set rsTemp = rsTemp.OpenRecordset
    
    Set mCombo.Recordset = rsTemp
    If rsTemp.RecordCount = 0 Then
        ' No records found for this filter.  Alert the user so they don't keep
        ' typing, but don't stop them in case they are pasting or entering special criteria.
        Beep
    End If
    
    If Len(strText) > 0 Then
        mCombo.Dropdown
    Else
        ' Don't make the dropdown appear if the user just cleared the field.
    End If
    
Exit_Sub:
    Exit Sub
  
ErrorHandler:
    If Err.Number = 3061 Then
        MsgBox "Will not Filter. Verify Field Name is Correct." & vbCrLf & vbCrLf _
            & "mFilterFieldName = " & mFilterFieldName
    Else
        MsgBox "Error #" & Err.Number & " - " & Err.Description & vbCrLf & "in procedure FilterList of clsFindAsYouTypeCombo"
    End If
    GoTo Exit_Sub
    Resume Next
    Resume
    
End Sub 

I am adjusting the Enter key behavior because I noticed that if I typed "f36" and pressed Enter, I was expecting it to pick the first record that began with "F36", which is how the default behavior of the standard Combo Box.



The new functionality will pick the highlighted record if there is one, otherwise it picks the first record in the list. This also fixes an issue where typing "f36" and then using the up/down arrow keys and pressing Enter on a record was ignoring the selection and always picking the first record in the list.

CODE --> VBA

Private Sub mCombo_KeyDown(KeyCode As Integer, Shift As Integer)
' Handle keys that affect the auto-complete feel of the combobox.  BS 10/13/2015
' BS 8/4/2016:  Pressing Enter will now pick the first item in the list.
' BS 11/1/2016: Pressing Enter will now pick the first item in the list as long as
'       a record is not highlighted because it BEGINS WITH the entered text, in which
'       case it will pick that record.

    On Error GoTo ErrorHandler

    If mHandleArrows = True Then
        
        Select Case KeyCode
            Case vbKeyReturn
                ' Pick the first item in the list when this key is pressed.  Comment out this Case if you don't want this feature.
            
                mAutoCompleteEnabled = False
                If mCombo.ListCount >= 1 And Len(mCombo.Text) > 0 Then
                    ' There are values in the list and the user has entered at least one key before this one.
                    If mCombo.ListIndex > -1 Then
                        ' A record was found that BEGINS WITH the text entered and the default nature
                        ' of the combo box has caused that row to be selected.  Pick that item.
                        mCombo.value = mCombo.ItemData(mCombo.ListIndex)
                    Else
                        ' Pick the first item in the list.
                        mCombo.value = mCombo.ItemData(0)
                    End If
                    ' This forces the _AfterUpdate and _Change events to fire and run code that may update the form.
                    ' This seems awkward but I couldn't find a better way to trigger the event from here.
                    mCombo.Text = mCombo.Text

                End If
            Case vbKeyDown, vbKeyUp, vbKeyPageDown, vbKeyPageUp, vbKeyReturn
                ' When these special keys are hit they begin to select records
                ' from the dropdown list.  Without this, as soon as one record
                ' is selected (by highlighting it) then the entire filter is
                ' set to that item making it impossible to use the keyboard to
                ' scroll down and pick an item down in the list.
                mAutoCompleteEnabled = False
            Case Else
                mAutoCompleteEnabled = True
    End If

Exit_Sub:
    Exit Sub

ErrorHandler:
    If Err.Number = 2118 Then ' You must save the current field before you run the Requery action.
        ' I'm not sure why this error happens.  It is the result of the above line:  mCombo.Text = mCombo.Text
        Resume Next
    Else
        MsgBox "Error #" & Err.Number & " - " & Err.Description & vbCrLf & "in procedure mCombo_KeyDown of clsFindAsYouTypeCombo", , "clsFindAsYouTypeCombo"
    End If
    GoTo Exit_Sub
    Resume Next
    Resume

End Sub 

RE: Find as you type combobox class that allows requery

You added some behaviors that the user may or may not want. Instead of having the user edit the class you should have properties to set the behavior. For example you could make a property to determine what to do when the user hits the return key. In this case the property could be a simple boolean. Make the default true, but allow the user of the class to set it to false.

CODE

Case vbKeyReturn
   If me.ReturnPickFirst then 
               ' Pick the first item in the list when this key is pressed.  Comment out this Case if you don't want this feature.
     code here
  end if 

Same for filtering multiple columns.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close