Search List Box with Text Box

[dalton]

Okay guys, you've been great with all the help so far. Got a new question for you.

I have a list box pulling information from a table. It include fields for last name, first name, primary number, secondary number, and id; of course with different names. I display first and last name, but the rest are hidden. What would be ideal is if I could begin typing someones first or last name in the text box and it would just start either narrowing the list, like in itunes, or would start skipping down the list and would be highlighted. For example. If I was searching the number for walmart, i could go to the text box and type "wal" and it might highlight "wallace, james"; and if I added and "m" to that, it would skip down to "walmart".

I would settle for autofill and click a search command button when I'm done, and it takes me to that entry in the list box. I copied some code off the internet. Don't know if it'll work, but here is what I got.

Private Sub txtSearch_AfterUpdate()
Dim srchstrng As String
srchstrng = Me.txtSearch.Value
SQL = " SELECT Phone.last_name, Phone.first_name FROM Phone_"
WHERE (((Phone.last_name) Like '*" & srchstrng & "*'));"
Form.RecordSource = SQL
DoCmd.RunCommand acCmdRefreshPage
End Sub

It says there is a problem with the where clause. Thanks.

[pbaldy]

A couple of thoughts. To have it narrow the list as you type, you would need to use the Change event of the textbox. Then you would also need to use the .Text property of the textbox for your value. You'd want to set the row source of the listbox, not a form's record source. Finally, your concatenation from line to line is off building the SQL. You might want to keep it all on one line to get it working, then work on splitting up the string for readability.

[Jim Doherty]

Have a look at this app I knocked up in Access 2000 for the 'reducing' listbox side of things.... it might give you a few ideas. I created it basically as a demo search mechanism related to a listbox with the 'added' feature that anything you type in the textbox to search the list gets added to the table so that it is there next time you search a sort of 'googley' type of thing but no where near as powerful obviously. It involves the afterupdate event of the textbox and on change events essentially along with sql building

Also You might want to have a look at Allen Brownes website and have a look at his 'Find As You Type' demo db he has made available for download http://allenbrowne.com/AppFindAsUType.html

[DanHolland]

first time using this forum, hoping someone might be able to help...

i have a form with a text box and list box to search for customers on my database (type in the text box and it filters the list & double click to open a separate form with the customers details) but in my list box i have 2 columns 'Business name' then 'Trade/Site Name' but i dont get all the site names in the list box but they are there in the query i used to make this form, anyone know how to fix this?

Thanks in advance

[pbaldy]

Are you getting some of the site names? If not, check the column widths property of the listbox, along with the column count property.

[DanHolland]

Are you getting some of the site names? If not, check the column widths property of the listbox, along with the column count property.

yeah i get some of them but not all, no idea why :/

[pbaldy]

Odd. Can you post the db here?