Searching a listbox

[chrismid259]

Hi,
I'm trying to search and display results in a listbox. All I have is a form with a text box where a surname will be input named "txtFilterSurname", a listbox which display records from the client table called "ClientList" and a button.

What I want to be able to do is input a surname in the textbox, click search and the record appear in the listbox. I've tried some methods already but none seem to be working properly.

Thanks for any help.
Chris.

[tcoombes]

Chris try this


Sub Search_Click()

Dim MySurname As String
Dim Mysql As String


If Not IsNull(Me![Surname]) Then ' Read the surname from the Surname Box on the Form Assumes your Surname boc is on this form

MySurname = Me![Surname]

Else
Exit Sub

End If

Mysql = "SELECT name, other fieds separated by commas, last field no comma FROM name of your table or query WHERE Surname = '" & MySurname & "'" & "ORDER BY whatever "
' The Variable for the surname needs to have these ' charachters because it is a text variable

' Assumes you have a listbox called MyList
Me.MyList.RowSource = Mysql

' Every time you enter the new surname the list box rowsource changes to only the records belonging to the surname

End Sub