Filtering a ComboBox based on .text entered

[Ian Chappel]

I am using the following code, which filters (=modifies the RecordSource of)
the Combo based on text entered:

Works fine, except it looks unsightly as all other instances of the Combo on
the Continuous form or Datasheet "lose" their text. Is there any way I can
avoid this (I suspect not!) or a better way of doing this, apart from
creating a separate control (on Mainform?)? The "SpliceIn" function merely
inserts the additional WHERE clause at the correct point in my strSQL:

___________________________________________________________________

Public Sub FilterComboLike(frm As Form, strComboSQL As String, strComboMatch
As String)
'Call from _Change event of ComboBox
' frm = Me
' strComboSQL = ComboBox's RecordSource (enter as a Const at Form
module-level)
' strComboMatch = ComboBox's 1st visible column's RecordSource (enter in
_Change sub)

Dim strEntered As String
Dim strFilterSQL As String
Dim strJoiner As String

' Use AND if there's already a WHERE
If InStr(strComboSQL, " WHERE ") > 0 Then strJoiner = " AND " Else
strJoiner = " WHERE"

Const iLen As Integer = 3 'characters entered before filtering starts
With frm.ActiveControl
'.RowSourceType = "Table/Query"
strEntered = .Text
If Len(strEntered) >= iLen Then
strFilterSQL = strJoiner & strComboMatch & " Like '*" &
strEntered & "*'"
.RowSource = SpliceIn(strComboSQL, strFilterSQL, " ORDER BY
") & ";"
'Debug.Print .RowSource
.Dropdown
Else
.RowSource = strComboSQL
End If
End With

End Sub

___________________________________________________________________


Also, when .ListCount=1 then it would be nice to select that one item and
TAB to the next field in the form - I can't work out how to do this - I need
the opposite of DropDown.

Thanks!