Combobox as Find As You Type? 1

[tomh1]

I would like to implement a combobox with "find as you type" functionality similar to that found in application launchers like AppRocket and Colibri (on Windows; LaunchBar or Butler on Mac OS X). These work by dynamically filtering the list as you type, finding all entries that contain the letters you type, in the order you type them, but not necessarily consecutively. For example, in an application launcher, typing "me" brings up a list that would include "Messenger," "NetMeeting," "Microsoft Excel," "Microsoft Access," "Microsoft Powerpoint" and other apps containing first the letter "m" and then the letter "e." Typically these lists "remember" previous selections, so the last item selected moves to the top of the list the next time you repeat the same search.

I would ideally like to find canned code, but any suggestions on how to implement this are welcome.

Thanks.

 

[PHV]

Have a look at the AutoExpand property of the ComboBox object.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[tomh1]

Thank you for your quick response, PH, but AutoExpand doesn't do what I'm looking for. In the example I provide, AutoExpand would jump to the "Messenger" entry, but would not find the other entries ("NetMeeting," "Microsoft Excel," etc.). For example, I might be looking for Excel, but not be sure if it's listed as "MS Excel" or "Microsoft Excel." AutoExpand would not help, while the abbreviation-based filtering provided by programs like AppRocket and Colibri would find the entry.

Thanks again, Tom.

 

[PHV]

What is the combo's RowSource SQL code ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Remou]

Here is a very rough thought:

Private Sub cboCombo_Change()
'Set AutoExpand to No
'Column Count 2
'Second column width=0
Dim strText, strFind
strText = Me.cbocombo.Text
If Len(Trim(strText)) > 0 Then
    For i = 1 To Len(Trim(strText))
       strFind = strFind & "Appname Like '*" & Mid(strText, i, 1) & "*' And "
    Next
    strSQL = "Select AppName, SortOrder from tblApps Where " & _
    Left(strFind, Len(strFind) - 5) & " Order By SortOrder"
    Me.cboCombo.RowSource = strSQL
End If
End Sub

Private Sub cboCombo_LostFocus()
Dim rs As DAO.Recordset
If cboCombo.Column(1) <> 1 Then
    Set rs = CurrentDb.OpenRecordset("Select * From tblApps Order By SortOrder")
    rs.MoveFirst
    i = 2
    Do While Not rs.EOF
        rs.Edit
        If rs!AppName = Me.cboCombo Then
            rs!SortOrder = 1
        Else
            rs!SortOrder = i
            i = i + 1
        End If
        rs.Update
        rs.MoveNext
    Loop
End If

End Sub

 

[tomh1]

PH,

SELECT tName.nameKey, tName.Name, tName.SortOrder FROM tName ORDER BY tName.SortOrder;

Remou,

That's a great start; it does exactly what I want.

Now I just need to figure out how to have the menu portion of the combo stay open as the user types so that the user can see the available (filtered) items in the list...getting shorter as they type.

Thanks!

 

[tomh1]

Ah, I think I found it...

Just insert

me.cboCombo.DropDown

at the end of the if/then statement in Sub cboCombo_Change(), so it looks like:

If Len(Trim(strText)) > 0 Then
    For i = 1 To Len(Trim(strText))
       strFind = strFind & "Appname Like '*" & Mid(strText, i, 1) & "*' And "
    Next
    strSQL = "Select AppName, SortOrder from tblApps Where " & _
    Left(strFind, Len(strFind) - 5) & " Order By SortOrder"
    Me.cboCombo.RowSource = strSQL
    Me.cboCombo.DropDown
End If

Thanks again!

 

[MajP]

I think that this may be a better more reuseable approach using the recordset property of the combobox and the filter property of a recordset. This requires not haveing to mess with different SQL strings, and look a lot easier.

Public rsList As DAO.Recordset

Private Sub cmboFilter_Change()
  Dim rsTemp As DAO.Recordset
  Dim strText As String
  Dim strFilter As String
  strText = cmboFilter.Text
  strFilter = "strLastName like '" & strText & "*'"
  rsList.Filter = strFilter
  Set rsTemp = rsList.OpenRecordset
  rsTemp.Filter = strFilter
  Set rsTemp = rsTemp.OpenRecordset
  Set cmboFilter.Recordset = rsTemp
  cmboFilter.Dropdown
End Sub

Private Sub cmboFilter_GotFocus()
  cmboFilter.Dropdown
End Sub

Private Sub Form_Open(Cancel As Integer)
 cmboFilter.SetFocus
 Set rsList = CurrentDb.OpenRecordset(cmboFilter.Recordset.Name)
End Sub

 

[MajP]

Remove this line in the code:
'rsList.Filter = strFilter

 

[tomh1]

Thank you, MajP. I'm having some difficulties with the Set rsTemp = ... line; Access keeps throwing an error, telling me that it expects two parameters and only found one. I'll keep working on that, though; your approach definitely looks more portable.

I have been able to get Remou's code working exactly the way I wanted it. I'm really very happy with it.

For those who are interested, the full code, heavily commented, can be found below.

Thanks again for your responses!

- Tom

Private Sub Combo0_Change()
' Function Description:
' Filter a combo box list as the user types, similarly to how application
' launchers like Colibri, AppRocket and LaunchBar opperate.
    ' e.g. if the list contains the names of U.S. Presidents, and
    ' the user types "gw," then the resulting SQL WHERE clause will
    ' look like "Name Like '*g*w*'" and the resulting list
    ' will include George Washington, George H. W. Bush and
    ' George W. Bush, among others.
    ' The order is preserved, so that typing "wg" creates an SQL WHERE
    ' clause like "Name Like '*w*g*'" and the resulting list would
    ' include George Washington but not the Bushes.
    
' This is accomplished by grabbing the text typed by the user in the
' combo box's edit field, creating an SQL SELECT statement from it,
' and finally applying that SQL statement to the combo box's
' .RowSource property.

' Form design settings:
'  Set AutoExpand to No
'  Column Count 3
'  Keyed on column 1 (record primary key)
'  Showing column 2 (user-readable data) column 2 width > 0
'  First and Second column width=0

Dim strText, strFind

' Get the text that the user has typed into the combo box editable field.
strText = Me.Combo0.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 of the SQL
    ' SELECT statement.
    strFind = "Name Like '"
    For i = 1 To Len(Trim(strText))
        If (Right(strFind, 1) = "*") Then
            ' When adding another character, remove the
            ' previous "*," otherwise you end up with
            ' "*g**w*" instead of "*g*w*."
            ' This has no apparent impact on the user, but
            ' ensures that the SQL looks as intended.
            strFind = Left(strFind, Len(strFind) - 1)
        End If
        strFind = strFind & "*" & Mid(strText, i, 1) & "*"
    Next
    strFind = strFind & "'"
    
    ' Create the full SQL SELECt string for the combo box's
    ' .RowSource property.
    strSQL = "SELECT tName.nameKey, tName.Name, SortOrder FROM tName Where " & _
    strFind & " ORDER BY SortOrder;"
    
    '' NOTE: to remove the order requirement, such that typing "wg"
    '' and "gw" return the same results, the SQL WHERE clause needs
    '' to look like "Name Like '*w* AND *g*'."
    '' The code above should be changed as follows:
    ''For i = 1 To Len(Trim(strText))
    ''   strFind = strFind & "Name Like '*" & Mid(strText, i, 1) & "*' And "
    ''Next
    ''
    ''strSQL = "SELECT tName.nameKey, tName.Name, SortOrder from tblApps Where " & _
    ''Left(strFind, Len(strFind) - 5) & " Order By SortOrder"
    
    ' Filter the combo list records using the new SQL statement.
    Me.Combo0.RowSource = strSQL
    
Else
    ' Show the entire list.
    strSQL = "SELECT tName.nameKey, tName.Name, tName.SortOrder FROM tName ORDER BY tName.SortOrder; "
    Me.Combo0.RowSource = strSQL
End If

' Make sure the combobox is open so the user
' can see the items available on list.
Me.Combo0.Dropdown

End Sub

 

[PHV]

And what about this (ac2k or above) ?

Private Sub Combo0_Change()
Me!Combo0.RowSource = "SELECT nameKey,Name,SortOrder FROM tName WHERE Name Like '*" _
& Join(Split(StrConv(Me!Combo0.Text & "", vbUnicode), Chr(0)), "*") & "' ORDER BY 3"
Me!Combo0.Dropdown
End Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MajP]

Tom,
See if this fixes the code I gave you. I am going to encapsulate this idea in a Class Module so in order to give any control this functionality all you will have to do is instantiate a variable. All of the methods and events will be generic to the class.

Private Sub Form_Open(Cancel As Integer)
 cmboFilter.SetFocus
 Set rsList = cmboFilter.Recordset.clone
End Sub

 

[MajP]

If anyone is interested take a look at this. I plan to submit it as FAQ after some testing. It will turn any combobox into a filterable combobox. I filter a little differently. If I type "J" it shows all things beginning with a "J", if I type "Jo" is shows all things beginning with Jo, etc. In order to turn a combo box into a filterable combobox add a reference to DAO then:

Public fcFilterCombo As FilterCombo

Private Sub Form_Open(Cancel As Integer)
  Set fcFilterCombo = New FilterCombo
  'replace "cmboFilter" with your combo box
  Set fcFilterCombo.FilterComboBox = Me.cmboFilter
  fcFilterCombo.FilterFieldName = "strLastName"
End Sub

Now drop this code into a Class Moduel called, "FilterCombo"

Option Compare Database
Option Explicit

Private WithEvents mCombo As Access.ComboBox
Private WithEvents mForm As Access.Form
Private mFilterFieldName As String
Private mRsOriginalList As DAO.Recordset
Public Property Get FilterComboBox() As Access.ComboBox
  Set FilterComboBox = mCombo
End Property
Public Property Set FilterComboBox(theFilterComboBox As Access.ComboBox)
  Set mCombo = theFilterComboBox
  Set mForm = theFilterComboBox.Parent
  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
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
  strFilter = mFilterFieldName & " like '" & strText & "*'"
  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

 

[tomh1]

PH and MajP, thank you!

Combining your two solutions provides a more portable solution than the one I posted above. It works great and is fast. Very nice.

 

[akasmia]

tomh1

I think it's a very good piece of code. I have tried this code in one of my combo boxes. It keeps asking me to enter SortOder!. What I am doing wrong?.

Appreciate your help

Ak

 

[akasmia]

Never mind I found it in the SQL statement... works great !
Thanks !

AK