INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

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!

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

Jobs

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

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

My Archive

Resources

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