Auto-complete combo box...continuation of thread "Combobox as Find As You Type?"

[BenSacheri]

thread702-1228727

I was developing my own way of having an auto-complete combo box class when I came across an idea from user "MajP" that suggested using the recordset property of the combobox instead of fussing with the SQL statement. I've got a big combo box that is slow to query so I was interested in this method. The question I'm having is how to handle a refresh/requery on the form. A typical combo box will refresh when the form refresh event happens. That doesn't work with this class solution. I'm curious if there is a way for the class to recognize a refresh/requery event on the form and have it update the mRsOriginalList recordset.

Here's the scenario where I'm having issues:
1) User opens a form that has a list and a combobox that will filter the list. The combobox holds all the unique values from a column in the list. The list is empty. The combobox is empty (no rows).
2) User clicks a button to import data. Me.Refresh happens after the import and the list becomes populated and the combobox now has values in the dropdown.
3) As I type into the combobox it only finds records matching the first letter and the list does not filter.
4) If I close the form and re-open it, the combo is populated and will auto-complete as expected.

I don't know how to private message MajP. He is probably the most qualified to address this issue. Does he still work with Access/VBA?

 

[MajP]

faq702-6304

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.

'Parmaters:
'  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.
'
'*******START: Form Code*******************
'
' Option Compare Database
' Option Explicit
' Public faytProducts As New FindAsYouTypeCombo
' Form_Open(Cancel As Integer)
'   faytProducts.InitalizeFilterCombo Me.cmbProducts, ProductName", False
' End Sub
'
'******* 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

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
Private Sub mCombo_Change()
  Call FilterList
End Sub
Private Sub mCombo_GotFocus()
   mCombo.Dropdown
End Sub
Private Sub mCombo_AfterUpdate()
  Call unFilterList
End Sub
Private Sub mForm_Current()
  Call unFilterList
End Sub
Private Sub FilterList()
  On Error GoTo errLable
  Dim rsTemp As DAO.Recordset
  Dim strText As String
  Dim strFilter As String
  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
  End If
  mCombo.Dropdown
  Exit Sub
errLable:
  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 unFilterList()
  On Error GoTo errLable
  Set mCombo.Recordset = mRsOriginalList
   Exit Sub
errLable:
  If Err.Number = 3061 Then
    MsgBox "Will not Filter. Verify Field Name is Correct."
  Else
    MsgBox Err.Number & "  " & Err.Description
  End If
End Sub

Public Property Get FilterFieldName() As String
  FilterFieldName = mFilterFieldName
End Property

Public Property Let FilterFieldName(ByVal theFieldName As String)
  mFilterFieldName = theFieldName
End Property

Private Sub Class_Initialize()
    
End Sub

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

Public Sub InitalizeFilterCombo(TheComboBox As Access.ComboBox, FilterFieldName As String, Optional FilterFromStart = True)
   On Error GoTo errLabel
   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]"
   With mCombo
     .SetFocus
     .AutoExpand = False
   End With
   Set mRsOriginalList = mCombo.Recordset.Clone
   Exit Sub
errLabel:
    MsgBox Err.Number & " " & Err.Description
End Sub

The question I'm having is how to handle a refresh/requery on the form.

A form does not have a requery event. A form does not requery on its own. So you may not directly trap that event, but you should be able to trap the event that is causing your form to requery. What causes the form to requery? The class demonstrates trapping a lot of different events. For example the forms on current event should cause the combo to unfilter. You can trap other events from the form or other objects.

I do not really understand your scenario because it is hard to tell what is working and what is not working as desired.

User opens a form that has a list and a combobox that will filter the list. The combobox holds all the unique values from a column in the list.

Not sure what you are describing here. Is it a multi column listbox and a FAYT combo box that filters the listbox? It that is the case then I have the class for a FAYT Listbox. You would have a listbox and a textbox above/below it. May make more sense.

Can you provide the code for populating the list and combo and show where you are instantiating the FAYT combo. My guess is you are instantiating it in the open event, but at that time the combo has no row source and no recordset. Actually suprised that would not error out. So I would think after you click the button to load the combo that is where you should instantiate the FAYT combo instead of the on open event. That may be why when you close and reopen it works.

Now the code was built for reuse and portability. The user can get all of this functionality from adding a single line of code to the form and instantiating the class. It was not built to be efficient. I believe that in a DAO recordset when I apply the filter I am creating a new recordset. I believe this is different in ADO, where it actually applies the filter to the open recordset. If you can get this to work, but find it slow, I could try to update the class to work with ADO. Since the combo only returns an dao recordset you would have to first get the recordsource, then get an ADO recordset, then bind that to the combo. A few extra steps, but not too hard.

 

[BenSacheri]

Ugh. I just spent 40 minutes making a reply and lost it when my browser crashed. Is there a chance we could talk directly? Is there a way to get your email address without publishing it in the forum?

Your latest class is better than the one I was using, but there is an issue with the screen flashing when the .Dropdown is called during initialization. I also have some additions that I'd like to have included in what you share on this site.

Ben
Pennsylvania, USA

 

[MajP]

1) I would keep it on the thread. I am very timely because I get email alerts on threads I am following.
2) Whenever doing a long post I usually type it in a text editor first especially when posting code. That avoids losing it if the browser crashes.
3) If you want to share proprietary code/data then use something like drop box
4) You can put screen shots on something like TinyPic and share on the site.

Take a look at this thread. Thread702-1723375 to see an example of a very very long thread that we worked through. Over 200 replies, and pretty complicated coding.

 

[strongm]

>You can put screen shots on something like TinyPic and share on the site

You can directly upload picture to tek-tips these days.

 

[MajP]

Thanks. I should pay more attention.

 

[BenSacheri]

I tried to disable the mCombo.Dropdown in the mCombo_GotFocus() event because I don’t like that the combo drops down every time a user tabs through it. Unfortunately this event appears to be required, or at least the mcombo.Dropdown needs to execute during InitalizeFilterCombo(), otherwise the line Set mRsOriginalList = mCombo.Recordset.Clone generates error 91 “Object variable or With block variable not set”. My work around to disabling the GotFocus Dropdown behavior was to call .Dropdown after the .SetFocus line in the InitalizeFilterCombo(), then the Recordset.Clone doesn’t error. No matter when the .Dropdown method is first called, the side effect is a quick flashing of the dropdown as the form
opens.

My best attempt to reduce the flashing in InitalizeFilterCombo() is:

    Dim i As Long
    With mCombo
        .SetFocus
[COLOR=#3465A4]        i = .ListRows
        .ListRows = 1  ' Reduce the amount of flashing from the next line.
        .Dropdown      ' This forces the combo recordset to populate.
        .ListRows = i[/color]
        .AutoExpand = False
    End With

My first question is, does the .Dropdown event have to fire in order for the recordset to be populated, or is there another way to force or wait for the recordset to be populated? I'm wondering if the class was initialized in the Form_Load() instead of the Form_Open(), if that would make a difference. I'm calling from Form_Open, but why does it need to be called from there?

For an overall visual reference, here is a sample of the form I am developing. The dropdown of the first combo box is covering the next two.

 

[MajP]

The dropdown thing is weird, I have no idea why that is.
So my guess is the recordset of the combo does not exist until you pull it down the first time. No idea why. The workaround is to create a recordset based on the query in the rowsource instead of referencing the controls recordset property


The fix is to change this line
set mRSoriginallist = mcombo.recordset.clone
to
Set mRsOriginalList = CurrentDb.OpenRecordset(mCombo.RowSource)

Also, you can initialize it anywhere prior to using it, there is no requirement for the onopen event.
You can initialize it even on entry into the control for the first time. Your code would look something like

Public faytProducts As FindAsYouTypeCombo
Private Sub cmbProducts_GotFocus()
  If faytProducts Is Nothing Then
    Set faytProducts = New FindAsYouTypeCombo
    faytProducts.InitalizeFilterCombo Me.cmbProducts, "ProductName", False
  End If
End Sub

Now you can get rid of the dropdown event where you want. So if you follow how you trap an event, you can instead trap the combox's mousedown and/or keypress event instead of the got focus event.

 

[Andrzejek]

MajP,
You do know you can edit your posts now, right

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[BenSacheri]

Thanks for the response to my .Dropdown inquiry. I'll accept "I have no idea" as a good answer (much better than making stuff up).

My next question will be in regards to the AfterUpdate event not firing all the time. I was trying to put together a good test case that consistently failed. (Note, I call the fayt function with the third parameter as FALSE)

Imagine list items like this:

ABC123
BCD124
CDE456
EFG457

And you type "12" into the fayt box. It will show the first two values in the dropdown list. Then press Enter (without selecting from the dropdown). What happens? Sometimes the fayt textbox shows the first value, ABC123, from the filtered list and moves the focus to the next control on the screen, as if you actually selected ABC123. I put a "beep" in my AfterUpdate event and it doesn't fire. I would like the AfterUpdate to call a routine to apply the selected value as a filter to a subform.

Now, trying again, type "CD" into the fayt box. It will show the second two values in the dropdown list, but the CDE456 record will be highlighted. If you press Enter, "CDE456" will show in the textbox, probably because the default behavior of the combo is to move the highlight to a record that begins with what you typed. In this case, the AfterUpdate event seems to fire.

Any thoughts on this?

Ben

 

[MajP]

Thanks Andy. I know now.

 

[MajP]

Ben,

Thanks for the response to my .Dropdown inquiry. I'll accept "I have no idea" as a good answer (much better than making stuff up)

Actually this was probably done on purpose. There is not reason to load the combo until it is selected, so this would save resources on start up. I do the same on complex forms where I set the recordsource of controls after the form loads.

The combobox has a lot of default behavior that is hard to change. A lot of what you describe is standard behavior. FYI, the class does not effect this behvior. If the after update is not firing in those circumstances it would not fire without the class. So the workaround may be to trap the keypress event and check for the "enter".
Debugging Event issues is really hard without seeing it. I call these "timing" issues. Things may happen in a certain order under certain conditions. Without seeing it, it is hard to debug.
You may try the change event instead of the after update event. This may fire more than you want.
Also is this information hierarchical. If so maybe this best way to present this is in a treeview (like a file structure). May be a fast way to find stuff. I have lots of code for that.

 

[BenSacheri]

MajP,

I've made some additions and to your fayt combo class. Please don't take offence. I had these in my own beta AutoComplete code and am used to them. I want to switch to using your class because your recordset method is faster than requerying the database with each keystroke. After using your class in the sample above, I want to add it to some other applications. I have a dropdown at the top of a form for the user to select a record. The list used to have a few hundred items in it that followed a nice naming convention. Now I'm using it with a different client and they have 6,000 records with not much of a standard. Don't worry, I have also created a nice popup dialog to help the user search for records but your fayt class is so cool that I want to see if I can add it to existing combo boxes.

I [highlight #FCE94F]highlighted[/highlight] my changes to your code. My additions boil down to:
[tab]1) Beep when the adds a keystroke so the filter has no matching records.
[tab]2) Allow the user to use the up/down arrow keys while freezing the filter. This feels natural.

I'm curious to know what you (and others) think.

Ben

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.

'Parmaters:
'  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.
[highlight #FCE94F]'  HandleArrows:  Determines if up/down arrow keys stop the
'    scrolling of the dropdown from affecting the filter.[/highlight]
'
'*******START: Form Code*******************
'
' Option Compare Database
' Option Explicit
' [highlight #FCE94F]PRIVATE[/highlight] faytProducts As New clsFindAsYouTypeCombo
' Form_Open(Cancel As Integer)
'   faytProducts.InitalizeFilterCombo Me.cmbProducts, ProductName", False[highlight #FCE94F], True
[/highlight]' End Sub
'
'******* 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
[highlight #FCE94F]
Private mHandleArrows As Boolean         ' BS 10/13/2015
Private mAutoCompleteEnabled As Boolean  ' BS 10/13/2015[/highlight]

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

Private Sub mCombo_Change()
    Call FilterList
End Sub

Private Sub mCombo_GotFocus()
[highlight #FCE94F]''' 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[/highlight]
End Sub

Private Sub mCombo_AfterUpdate()
    Call unFilterList
End Sub

Private Sub mForm_Current()
    Call unFilterList
End Sub

Private Sub FilterList()
  On Error GoTo errLable
  Dim rsTemp As DAO.Recordset
  Dim strText As String
  Dim strFilter As String
  
[highlight #FCE94F]    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[/highlight]
  
  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
[highlight #FCE94F]    Else
        ' No records found for this filter.  Alert the user so they don't keep typing.
        Beep[/highlight]
    End If
    
  mCombo.Dropdown
  Exit Sub
errLable:
  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 unFilterList()
  On Error GoTo errLable
  Set mCombo.Recordset = mRsOriginalList
   Exit Sub
errLable:
  If Err.Number = 3061 Then
    MsgBox "Will not Filter. Verify Field Name is Correct."
  Else
    MsgBox Err.Number & "  " & Err.Description
  End If
End Sub

Public Property Get FilterFieldName() As String
  FilterFieldName = mFilterFieldName
End Property

Public Property Let FilterFieldName(ByVal theFieldName As String)
  mFilterFieldName = theFieldName
End Property

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

Public Sub InitalizeFilterCombo(TheComboBox As Access.ComboBox, FilterFieldName As String, Optional FilterFromStart = True _
    [highlight #FCE94F], Optional HandleArrows As Boolean = True[/highlight])
   
[highlight #FCE94F]' 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
'       aarow keys and page up/down in the combobox will stop the fayt filter
'       from adding the first highlighted list item to the filter.[/highlight]
   
   On Error GoTo errLabel
   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]"
   
[highlight #FCE94F]   mHandleArrows = HandleArrows
    If mHandleArrows = True Then
        mCombo.OnKeyDown = "[Event Procedure]"  ' BS 10/13/2015
        mCombo.OnClick = "[Event Procedure]"    ' BS 10/13/2015
    End If[/highlight]
    
[highlight #FCE94F]    Dim i As Long
[/highlight]    With mCombo
        .SetFocus
[highlight #FCE94F]        i = .ListRows
        .ListRows = 1   ' Reduce the amount of flashing from the next line. ' BS 10/13/2015
        .Dropdown       ' This forces the combo recordset to populate.      ' BS 10/13/2015
        .ListRows = i
[/highlight]        .AutoExpand = False
    End With
   
   Set mRsOriginalList = mCombo.Recordset.Clone
   Exit Sub
errLabel:
    MsgBox Err.Number & " " & Err.Description
[COLOR=#C4A000]    Debug.Print Err.Number, Err.Description[/color]
End Sub

[highlight #FCE94F]
Private Sub [b]mCombo_KeyDown[/b](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 [b]mCombo_Click[/b]()
    ' 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
[/highlight]

 

[MajP]

I've made some additions and to your fayt combo class. Please don't take offence.

Definitely no offense, it is serving its exact purpose.
1) The FAQ is demonstrating how to use a custom class to extend Access, encapsulate function, and make code easy to reuse. Imagine adding manually FAYT combos across your applications without the ability to reuse this class. Would require a lot of code writing. I think you get it
2) Demonstrates how to trap events in a custom class. I would guess about 5% of Access developers understand how to do it. I have read probably tens of thousands of posts on this thread, and I have never seen anyone else use this. Once you understand it, it is very powerful in advancing what you can do in Access and vba. I should have demonstrated raising a custom event. Obviously you get it with adding your own events. I always use the mouse (never tab), so I forget about testing or including features to support tabbing.
3) Demonstrate some uses of DAO recordsets and the ability to return or bind a recordset from a combo box. I could not make a general reuseable code if working with the recordsource. It would be extremely difficult to parse and change the where clause without knowing specifics about the query. With the filter I need to know nothing about the query.

Again, the use of the recordset was not for efficiency/speed but for the above objectives. I am actually surprised it is faster. The filter on a DAO recordset creates a new recordset when open. ADO, however, does not and should be faster. I tried it with ADO. I could bind the unfiltered ADO recordset, and I could filter the recordset. But for some reason I could not bind the filtered recordset without an error.

You may want to add some properties as well.
You could add a read only property to the class to return the current filter. You could add a property to change the filter direction, to allow the user to change if they want to filter "*text", or "*text*". You can only set it in the beginning.

I will post the FAYT listbox. This works with a multi column listbox and a textbox. You can define a column to search or match any column. So instead of your subform you could simply have a listbox. Then you can find your record by typing and narrowing the list.

 

[BenSacheri]

I'm glad to have this exchange with you. I have been a full time developer in VB6/VBA since around 1998. I have to admit that I have hardly ever used classes. It used them some in the beginning but generally don't see the need for them. In this case having the ability to trap events is necessary.

I'll share below the AutoComplete code I wrote before I found yours. I developed a method to parse and use the existing query that works most of the time. Basically if the Rowsource begins with "SELECT" and does not contain "UNION" then I have code to insert a filter into the WHERE clause, even if one does not exist. If the Rowsource contains UNION or is based on a query object, I just replace the Rowsource with "SELECT * FROM (" & ROWSOURCE & ")" and continue to use my parsing routine.

I like your Recordset method better since it doesn't have to requery with every keystroke. Prior to this I'm not sure if I ever noticed that a combobox had a recordset.

I'm having a new issue. I have one form that has a checkbox that will toggle the Rowsource of the combo box. I need to make sure that the mRsOriginalList is updated when this happens. Can you recommend a good way to do this? Here's my method that isn't working.

Private Sub chkToggle_AfterUpdate()
    
    If Not faytFindTool Is Nothing Then
        ' Remove the Find-as-you-type capabilities of the Find combo box before changing the row source.
        Debug.Print cboFindTool.ListCount & " vs. " & cboFindTool.Recordset.RecordCount
        Set faytFindTool = Nothing
    End If

    If chkToggle = True Then
        Me.cboFindTool.RowSource = "SELECT Tool_ID, Tool_No, ... WHERE X=YYY"
    Else
        Me.cboFindTool.RowSource = "SELECT Tool_ID, Tool_No, ... WHERE X=ZZZ"
	End If
		
    ' Restore Find-as-you-type capabilities to the Find combo box.
    faytFindTool.InitalizeFilterCombo cboFindTool, "Tool_No", False, True
 
 End Sub

Based on the toggle, I either have 11 records or none. The problem I'm having is that after toggling the (typically) third time the cbo.ListCount may be 11 but the cbo.Recordset.RecordCount will be 1284. In the InitalizeFilterCombo code the line Set mRsOriginalList = mCombo.Recordset.Clone will fail with Error #3420: Object invalid or no longer set. I spent a while on this and I'm stumped.



- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
I'm sharing the code below as an example of my earlier method of an AutoComplete combobox that is not based on a class or a recordset. As long as I can get the class method to work consistently with a recordset I won't be using this method.

Public Sub ComboAutoComplete_Change(cbo As ComboBox, strSearchField As String)
'
' Purpose:
'   This will filter the combobox as the use types into it.
'   This search as-you-type capability is known as "auto-complete", i.e. "to search within
'   a controlled vocabulary for items matching a given character string."
'
' Requirements:
'   A combobox control.
'   The name of a field in the RowSource that will be filtered.  It does not have
'       to be the same as the display field.
'   The .RowSource should be a SQL statement or a named query, not a value list.
'   The .Tag property of the ComboBox should be available.  It does not need to contain
'       a copy of the RowSource.
'   Set .AutoExpand to No
'   Set .LimitToList to Yes (but not required)
'
' Use:
'   Call this function from the _Change event of a combobox.
'   Call ComboAutoComplete_KeyDown() from the _KeyDown event.
'   In the _Click() event set mAutoCompleteEnabled = False
'
' Ben Sacherich - October 2015
'

    Dim strText     As String
    Dim strWhere    As String
    Dim strSQL      As String
    Dim i           As Long
    
    On Error GoTo ErrorHandler

    If mAutoCompleteEnabled = False Then
        ' Don't update the combobox items when the user is using the arrow up/down keys.
        Exit Sub
    End If
    
    ' On the first call, check to see if the Tag property is available.
    ' The Tag property will be used on subsequent calls to this routine.
    ' A module variable could be used if we never have more than one ComboAutoComplete active at a time.
    If cbo.Tag = "" Then
        If Left(cbo.RowSource, 6) = "SELECT" And InStr(1, cbo.RowSource, "UNION") = 0 Then
            ' Standard SQL SELECT statement that does not contain UNION.
            ' This *should* be able to parse the statement.
            cbo.Tag = cbo.RowSource
        Else
            ' RowSource must be a query object
            cbo.Tag = "SELECT * FROM (" & cbo.RowSource & ")"
        End If
    End If
    
    ' Start with the original query every time this event fires.
    strSQL = cbo.Tag
    
    ' Get the text that the user has typed into the combo box editable field.
    strText = cbo.Text
    
    ' If the user has typed something in, then filter the combobox
    ' list to limit the visible records to those that contain the
    ' typed letters.
    ' Otherwise (if the field is blank), the user has deleted whatever
    ' text they typed, so show the entire (unfiltered) list
    If Len(Trim(strText)) > 0 Then
        ' Show the list with only those items containing the typed letters.
        
        ' Create an SQL query string for the WHERE clause
        strWhere = "[" & strSearchField & "] Like '*" & strText & "*'"
        
        ' Create the full SQL SELECT string for the combobox .RowSource property.
        
        '--- Find the correct place to insert the WHERE clause.
        i = InStr(1, strSQL, "WHERE")
        If i > 0 Then
            ' Insert within existing WHERE clause.
            strSQL = Mid(strSQL, 1, i + 4) & " (" & strWhere & ") AND " & Mid(strSQL, i + 5)
        Else
            i = InStr(1, strSQL, "GROUP BY")
            If i > 0 Then
                ' Insert before GROUP BY clause.
                strSQL = Mid(strSQL, 1, i - 1) & "WHERE (" & strWhere & ") " & Mid(strSQL, i)
            Else
                i = InStr(1, strSQL, "ORDER BY")
                If i > 0 Then
                    ' Insert before ORDER BY clause.
                    strSQL = Mid(strSQL, 1, i - 1) & "WHERE (" & strWhere & ") " & Mid(strSQL, i)
                Else
                    ' The statement does not contain WHERE or ORDER BY
                    strSQL = strSQL & " WHERE (" & strWhere & ") "
                End If
            End If
        End If
        
        ' Filter the combo list records using the new SQL statement.
        cbo.RowSource = strSQL
        
        If cbo.ListCount = 0 Then
            ' Indicate that no match was found.
            Beep
        End If
        
    Else
        ' Show the entire list.
        cbo.RowSource = strSQL
    End If
    
    ' Make sure the combobox is open so the user can see the items available on list.
    cbo.Dropdown

    Exit Sub

ErrorHandler:

    MsgBox "Error #" & Err.Number & " - " & Err.Description & vbCrLf & "in procedure ComboAutoComplete()"

End Sub


Public Sub ComboAutoComplete_KeyDown(cbo As ComboBox, KeyCode As Integer, Shift As Integer)
' Handle keys that affect the auto-complete feel of the combobox.

    If KeyCode = vbKeyReturn And cbo.ListCount >= 1 And mAutoCompleteEnabled = True Then ' And cbo.ListIndex = -1 
        ' If the user pressed Enter and at least one value is in the list
        ' then pick that item.
        cbo = cbo.ItemData(0)
    End If
    
    Select Case KeyCode
        Case vbKeyDown, vbKeyUp, vbKeyReturn, vbKeyPageDown, vbKeyPageUp
            mAutoCompleteEnabled = False
        Case Else
            mAutoCompleteEnabled = True
    End Select

End Sub


'------------------------------------------------------------------------------ AutoComplete Begin ----
' Code needs to be in these three events 
Private Sub cboFindTool_Change()
    ComboAutoComplete_Change Me.ActiveControl, "Tool_No"
End Sub

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

Private Sub cboFindTool_KeyDown(KeyCode As Integer, Shift As Integer)

    ' Handle keys that affect the auto-complete feel of the combobox.
    ComboAutoComplete_KeyDown ActiveControl, KeyCode, Shift

End Sub
'------------------------------------------------------------------------------ AutoComplete End ----

 

[MajP]

First I noticed that your code did not incorporate the fix

The fix is to change this line
set mRSoriginallist = mcombo.recordset.clone
to
Set mRsOriginalList = CurrentDb.OpenRecordset(mCombo.RowSource)

That should stop the issue with the dropdown. I recommend using that. Then you can reuse that idea and add a public method

Public Sub RequeryList()
  Set mRsOriginalList = CurrentDb.OpenRecordset(mCombo.RowSource)
end sub

And call it like
faytFindTool.RequeryList

However, I am curious because I would have thought your method would work.

 

[BenSacheri]

With great hope I tried adding your method:

Public Sub RequeryList()
Set mRsOriginalList = CurrentDb.OpenRecordset(mCombo.RowSource)
end sub

but it fails because my RowSource contained a query that had a VBA function in it, which can't be executed from VBA. I changed the query to be very simple and I'm able to get the line to run but the combo box list never changes. I have to admit I gave up after reviewing it for an hour with a fellow programmer but at the time I was really tired. I'm still looking for a solution to requerying the combo box, like in the case where it is cascading from the input of another control.

 

[MajP]

I do not know your specifics but you should be able to have a function call. Worked for me with native vba calls.