Thank you for the resource June7! I was able to get my combo box to behave as I wanted with the code from http://allenbrowne.com/ser-32.html. Here is the code that I ended up using, with my combox box called cmbListLit and desired searchable RowSource being a string called Lit_Cite from qryTaskListNames (primary key = SurveillanceID). I also modified the code a bit to automatically drop the combo box down once the RowSource was populated (minimum of 3 characters denoted by conLitMin):
Code:
Option Compare Database
Dim strLitStub As String
Const conLitMin = 3
Function ReloadLit(strLit As String)
'Code from: http://allenbrowne.com/ser-32.html
Dim strNewStub As String ' First chars of cmbListLit.Text
strNewStub = Nz(Left(strLit, conLitMin), "")
' If first n chars are the same as previously, do nothing.
If strNewStub <> strLitStub Then
If Len(strNewStub) < conLitMin Then
'Remove the RowSource
Me.cmbListLit.RowSource = "SELECT SurveillanceID, Lit_Cite FROM qryTaskLitNames WHERE (False);"
strLitStub = ""
Else
'New RowSource
Me.cmbListLit.RowSource = "SELECT SurveillanceID, Lit_Cite FROM qryTaskLitNames WHERE (Lit_Cite Like ""*" & _
strNewStub & "*"") ORDER BY Lit_Cite;"
Me.cmbListLit.Dropdown
strLitStub = strNewStub
End If
End If
End Function
Private Sub cmbListLit_Change()
Dim cbo As ComboBox ' cmbListLit combo box.
Dim sText As String ' Text property of combo box.
Set cbo = Me.cmbListLit
sText = cbo.Text
Select Case sText
Case " " ' Remove initial space
cbo = Null
Case Else ' Reload RowSource data.
Call ReloadLit(sText)
End Select
Set cbo = Nothing
End Sub
Private Sub Form_Current()
Call ReloadLit(Nz(Me.cmbListLit, ""))
End Sub