Search Facility

[CEB]

Hi Folks,
I have been trying to create a search list on a form. I used the example on this forum but still can't get it to work.
I have a text box to accept input.
I have a list box to display info.
I have the following code in the onchange event of the text box:
_________________
Private Sub txtSearch_Change()

Dim txtSearchString As Variant
Dim strSQL As String

txtSearchString = Me.txtSearch.Text

strSQL = "SELECT DISTINCT Personal Details.LastName, Personal Details.FirstName, Personal Details.Parish Number FROM Personal Details "
strSQL = strSQL & "WHERE ((Personal Details.LastName) Like '" & txtSearchString & "*') "
strSQL = strSQL & "ORDER BY Personal Details.LastName"

Me.SearchList.RowSource = strSQL
Me.SearchList.Requery

End Sub
_________________________________

The row source of the list box has the following:
______________________________________
SELECT [Personal Details].[LastName], [Personal Details].[FirstName], [Personal Details].[Parish Number] FROM [Personal Details];
__________________________________
When I run the for the listbox is populated with data from the presonal details table (however it's not in alphabetical order as in the table itself?) but when I enter a letter into the text box the information in the list box disappears. Any obvious suggestion as to whats wrong with it?
Thanks,
Colin

 

[lobodava]

Try this code:

Private Sub txtSearch_Change()
Dim txtSearchString As Variant
Dim strSQL As String

txtSearchString = Me.txtSearch.Text & ""

strSQL = "SELECT DISTINCT LastName, FirstName, [Parish Number] FROM [Personal Details] "
strSQL = strSQL & "WHERE (LastName Like '" & txtSearchString & "*') "
strSQL = strSQL & "ORDER BY LastName"

Me.SearchList.RowSource = strSQL
End Sub

It is better to avoid spaces in fields and tables names

 

[CEB]

Thanks very much, thats works perfectly. It was recking my head!!! ,
I much appreciate the reply,
Colin