Solved: textbox to filter contents of Listbox

[insomnai]

Hi guys. I have a question for you that you guys may be able to help with!

my example is as follows.

tblStreets with one field 'Streets'

frmStreets with one textbox 'txtStreetFind' and one listbox 'lstStreetFound'

'lstStreetFound' gets its data from 'tblStreets' and lists them for all to see.

What I would like to do is dynamically filter the 'lstStreetFound' contents based on what I type in 'txtStreetFind'...

Ideally I would like the listbox to change for every letter typed in the textbox, is this the afterupdate event or something else?

I have done some searching but I've had no real luck.

Hope you can help!

Best wishes all

insomnai

[OBP]

Asking for it to change after every letter you type is a bit tricky, getting it to Filter a list in the Listbox (NOT a Combo box) when you hit enter or Tab is easy and uses the After Update Event.
The simplest way to do this is to create a Query based on your tblStreets and in the Street Column's Criteria Row enter the following
forms![frmStreets]![txtStreetFind]
In the txtStreetFind field's After update Event Procedure type in
me.lstStreetFound.requery

It may be possible to have it do the same thing as you enter each letter by placing the Requery code in the txtStreetFind field's "On Key Press" event procedure instead.

However there is a "Find" version of a Combo box that will quickly find Records on your Form if the Form is actually based on the table.

[insomnai]

OBP you're a star. Thank you very much. Now I need to look into the difference between a combobox and a listbox...

Ok, next port of call is to pass the selection from the listbox back to the textbox I was searching with in the first place.

I know of course that I could just use a combobox and make the selection then pass it on but I want to know how to do this...

Many thanks

[OBP]

How do you mean pass it back to the text box?
If you have a list of streets in the listbox and select one why would you want to put it in the textbox, which should already contain it?
By the way I forgot to mention that you can do Pattern matching on your Query by using
like "*" & forms![frmStreets]![txtStreetFind] & "*"

It will find the textbox text anywhere in a Streetname.

[insomnai]

aaah that sounds more like it thank you OBP... much appreciated.

yeah I know what you mean, it is a little more work but in this case, i just wanted to know how to do it.

textbox contents to find filtered street names, then double click streetname in listbox to pass that exact text back to the textbox we originally typed in so that when I press 'add' I'm just sending the data from the text boxes.

for simplicity i know a combo box can be used to just select the street names and then pass that information onto a subform datatable thingy. (yes, i'm very technical...)

many thanks again OBP

oh, before I forget, you mention making a query to do this work... as a complete beginner, how is this done? and how can i add the second bit you added to it with the like "*" etc....

cheers.

[OBP]

On the Queries tab click on the "Create a query using the Wizard", select your table and the Field(s) that you want. The wizard does the rest and then opens the Query for you.
Click on the Query design view and then type or copy the
like "*" & forms![frmStreets]![txtStreetFind] & "*"
in to the first Criteria Row (where it says Criteria on the left) in the Streets Column.

[insomnai]

You're the man, thank you for your time buddy!

Insomnai

[insomnai]

ok, I seem to have this working but unfortunately i can't seem to get the requery to be dynamic on changing the text in the text box. it only seems to work if I leave a value in the textbox, change the view to design and back to form view again. this way the listbox is filtered to the text in the textbox, it just doesn't update as I thought it would....

is the requery bit right on the after update event?

[OBP]

Can you post a zipped copy of what you have have or email it to me?
It could just need to be me.requery, I need to see how your form and fields are structured.

[insomnai]

Here is the zip file of my sample which I was working on. Hope it makes sense.

Thanks

[insomnai]

I've managed it, many thanks. I just wrote in the Row Source of the listbox lstStreetFound:

SELECT tblStreets.[Street or Area] FROM tblStreets WHERE tblStreets.[Street or Area] LIKE "*" & [txtStreetFind] & "*"

created the after update event for the txtStreetFind

lstStreetFound.Requery

This seems to have done the trick.

Haven't found one that updates after each keystroke but that is for later....

[OBP]

That works well. I couldn't find a single letter as you type one either.
Did you look at those Threads I private mailed you?

[insomnai]

Yes OBP i've taken a look at those, i'm working my way through to see if there is a little hint or trick I can adapt for my needs...

the Select bit i've used for my row source, can that be typed into the criteria section of a query? so that the row source can be the query instead?

Cheers

[OBP]

Yes you can, it works great for searching as well.

[insomnai]

OBP could you explain that bit please? That it does great for searching? Another question on this one, how do I expand the search in this one, lets say the tblStreets has 'Street or Area' and 'Town' as a field, how do I adapt this search to look through both fields and show the results in the lstStreetFound box? I know how to set the listbox up to show the columns but it's just the search.....

Thanks

[OBP]

Add the Town to the query and on the next line down in the criteria add the same
like "*" & forms![frmStreets]![txtStreetFind] & "*" in that new Column.
If you put it on the same row and it will "AND" the filter i.e. they both have to evaluate to "yes" but having them on seperate rows allows either or to meet the filter.

Here is an example of advanced Searching.

[insomnai]

Thank you very much for that OBP it's pointed out a few things that are very useful.