INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS
Come Join Us!
Are you a
Computer / IT professional?
Join Tek-Tips now!
- 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.
Partner With Us!
"Best Of Breed" Forums Add Stickiness To Your Site
(Download This Button Today!)
Feedback
"...I just wanted to say that you guys RULE, a million thank you's to whoever created, and/or manages this site. KEEP UP THE GOOD WORK..."
Geography
Where in the world do Tek-Tips members come from?
|
Microsoft: Access Forms FAQ
|
List Boxes
|
Filter using a textbox and display results in a listbox.
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
|
|
 |
|
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:
Talk To Other Members- Notification Of Responses To Questions
- Favorite Forums One Click Access
- Keyword Search Of All Posts, And More...
Register now while it's still free!
Already a member? Close this window and log in.
Join Us Close
