Solved Search as you type inside a Combo Box (1 Viewer)

[Romio_1968]

Is there any way to search for matches inside the row source of a combo based 'Like *something*'?

To be more clear:
Let say I have some strings in the combo row source, The default behavior is to search as you type from the begining of the string.
I wonder if I can change this behavior.

For example, if I type Apple in the combo, to shorten (limit) the list to all strings that contain the text apple inside
Apples and other fruits
The storry of mr. Applegate
If nothing is selected, then it will offer the entire row source to choose from.

Thank You

 

[theDBguy]

Hi. Do a search here for @MajP "FAYT" demo.

 

[Josef P.]

An example that uses NotInList to filter: https://github.com/josef-poetzl/access-examples/blob/main/usability/ComboboxFilterControl.zip
... This eliminates the need to filter after each character entry.

 

[Romio_1968]

An example that uses NotInList to filter: https://github.com/josef-poetzl/access-examples/blob/main/usability/ComboboxFilterControl.zip
... This eliminates the need to filter after each character entry.

I don't know how it is supposed to work but it doesn't.
And moreover, it falls into runtime error

 

[Josef P.]

Hi,
is there perhaps a reference wrong?
I had not necessarily set the ADODB reference in the sample file. (Is now removed.)

This is how it should look: https://access-codelib.net/videos/codelib/comboboxfiltercontrol/ComboboxFilterControl.mp4

 

[MajP]

Find as we typed

Part of my form is to enter customer's info based on Combo Box. My combo is working if we type the beginning part of the customer name. How can we make it even 3 characters in the middle of the name, can auto find the closest names?. If any sample of database, could you uploaded it here please...

www.access-programmers.co.uk

 

[arnelgp]

An example that uses NotInList to filter: https://github.com/josef-poetzl/access-examples/blob/main/usability/ComboboxFilterControl.zip
... This eliminates the need to filter after each character entry.

although sometimes an Error occurs, and when it errors the Class is destroyed?
yet the code is compact.

i think the error comes when you Close the recordset, then assign a "new" recordset (on a closed recordset).
you can leave the recordset or just set it to Nothing:

   With m_ComboBox

      If .RowSourceType = "Value List" Then
         .RowSource = GetValueListFromRecordset(rst)
      Else
         If .Recordset Is rst Then
            Exit Sub
         ElseIf Not .Recordset Is Nothing Then
            If Not (.Recordset Is m_DataRecordset) Then
               '.Recordset.Close
               'arnelgp
               Set .Recordset = Nothing
            End If
         End If
         Set .Recordset = rst
      End If
     
   End With

 

[Josef P.]

Thanks for looking!
I now remove the recordset with Combobox.RowSource = vbNullstring.

Private Sub SetComboboxDataSource(ByRef rst As Object)

   With m_ComboBox

      If .RowSourceType = "Value List" Then
         .RowSource = GetValueListFromRecordset(rst)
      Else
         If .Recordset Is rst Then
            Exit Sub
         ElseIf Not .Recordset Is Nothing Then
            If Not (.Recordset Is m_DataRecordset) Then
               .Recordset.Close ' To close or not to close, that is the question.
            End If
         End If
         .RowSource = vbNullString 'Remove ComboBox.Recordset
         Set .Recordset = rst
      End If
      
   End With
  
End Sub

 

[arnelgp]

not tested but
i think the .RowSource is the SQL string, so if you remove it, then how can we get the recordset?

it errors when you Close the recordset, then assign to it rst.
i think when you Closed an object, there is no way to assign anything to it.
like if you use ADODB.Connection, when you close it, then you can't assign
any ConnectionString to it. you need to Open another Connection.

 

[Josef P.]

My suspicion is that there may be problems if RowSource and the later bound recordset do not match.
With Set .Recordset = rst I set the new reference. Thus the possibly existing recordset should go away.
I will add Set .Recordset = Nothing as well just to be safe.

Unfortunately, I do not succeed in generating a runtime error in my example. Therefore I simply exclude all problems known to me so far.

 

[apr pillai]

This example filters data by character matching data from the left-side, check whether it helps to solves your issue.

Filter by Character and Sort

Learn Advanced Microsoft Access Programming Techniques, Tips and Tricks

www.msaccesstips.com