Why have two fields (FullName and Search) with same data?
Okay, fixing your code, ran into a conundrum. You want to search anywhere in FullName which means AutoExpand must be turned off. If user clicks on list to select item Change event is triggered but combobox no longer has focus causing .Text property to error. And I can't get to work without .Text. Using down arrow to select item from dropdown list does not cause issue.
Allen's code matches strings starting from first character.
So finally got this to run without error:
Code:
Option Compare Database
Option Explicit
Function ReloadCombo(sSuburb As String)
'search as you type
If Len(sSuburb) = 0 Then
Me.SearchCombo.RowSource = "SELECT ClientID, FullName FROM CustomerT WHERE False"
Else
Me.SearchCombo.RowSource = "SELECT ClientID, FullName FROM CustomerT WHERE FullName LIKE '*" & _
Replace(sSuburb, "'", "''") & "*' ORDER BY FullName;"
End If
End Function
Private Sub Form_Current()
Call ReloadCombo(Nz(Me.SearchCombo, ""))
End Sub
Private Sub SearchCombo_Change()
If Me.SearchCombo Is Screen.ActiveControl Then
Call ReloadCombo(Nz(Me.SearchCombo.Text, ""))
Me.SearchCombo.Dropdown
End If
End Sub
Note the first two lines at top of code. Every module should have them in header. I find it odd the first line is missing. It should be there by default when module is created. The second line is optional by default. Set to automatically include with new module: from the VBE > Tools > Options > check Require Variable Declaration.
Also note use of Replace() function because some of your names have apostrophes. Apostrophes are considered special characters by SQL and will cause error unless the character is 'escaped' to force SQL to see it as a normal text character.