Filter using a textbox and display results in a listbox.

Ive seen quite a few posts about filtering using a textbox to populate a listbox. I use the following to do this.

Create a textbox, listbox and a command button on a form. On the onclick event of the button place the following code, replacing "TEXTBOX NAME and LISTBOX NAME with the name of yor textbox and listbox.

***********************************************************

Dim strSQL As String, strOrder As String, strWhere As String
Dim dbNm As Database
Dim qryDef As QueryDef
Set dbNm = CurrentDb()

'The statement below refers your table you are gathering information for and the required fields, replace "tblSaveLogon" with you table name and the information after "." with all the required fields you need. In my example below i have 4 fields which i want to appear in the list box.

strSQL = "SELECT tblSaveLogon.Username, tblSaveLogon.Details, tblSaveLogon.Date, tblSaveLogon.Time FROM tblSaveLogon"

strWhere = "WHERE"

'The statement below is to "ORDER" the results by which ever field you require, in my example its the field "Time"

strOrder = "ORDER BY tblSaveLogon.Time;"

If Not IsNull(Me.TEXTBOX NAME) Then

'replace "tblSaveLogon.Username" with which ever field your trying to filter with. For example if you want to filter by someones name it would be TABLENAME.name.

strWhere = strWhere & " (tblSaveLogon.Username) Like '*" & TEXTBOX NAME & "*' AND"
End If

strWhere = Mid(strWhere, 1, Len(strWhere) - 5)

Me.LISTBOX NAME.RowSource = strSQL & " " & strWhere & "" & strOrder

**********************************************************

Then go back to your form and go to the listbox control.
Change the COLUMN COUNT, if you need 4 fields to appear in the listbox then change it to 4, in your case it may be just be the one so it would be 1.

COLUMN COUNT - 1
COLUMN HEADS - NO (unless you want to show the field header, the field header is the header of each column in the table)

Below shows the width of each field you require, so if its 4 fields it would be something like

COLUMN WIDTH - 1";1";1";1" (you can make the field larger if you wish)

BOUND COLUMN - refers to your "FILTER FIELD" to bound, so if you want to filter by "name" and you have 4 fields that populate in the listbox and "name" is field number 2 then BOUND COLUMN - 2

That should do it.

If you want to count how many fields came up on your search, create another textbox and put this code at the bottom of your onclick event on your button. This code is used if you have a Column header, if you dont have a column header just delete the "- 1" from the code

Me.TEXTBOX NAME = Me.LISTBOX NAME.ListCount - 1
If (Me.LISTBOX NAME.ListCount = 0) Then
Me.TEXTBOX NAME = 0
End If


Hope that helps,
Nim180