Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Microsoft: Access Forms FAQ

List Boxes

Filter using a textbox and display results in a listbox. by nim180
Posted: 17 Apr 08

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 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

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

Hope that helps,

Back to Microsoft: Access Forms FAQ Index
Back to Microsoft: Access Forms Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close