Filtering or finding records as I type in a criteria text box in MS Access

By delaaaa ·
I have a form with a list box and a text box in MS Access. I want to filter the list box, or find records as I type in the criteria text box.

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -


by Tony Hopkinson In reply to Filtering or finding reco ...

you pick up onchange or onkeypress events from the text box, and then refresh the query that drives the list box

Something like

Select Surname from NameTable Where Surname Like !MyTextBox.Text + '%'

(* is an OK wildcard in Access, but don't use it, it's a bad habit as it's not standard SQL)

On a big table this can get very expensive, substantial delays between typing characters.
Like is slow anyway.
If it's always going to be a hundred or so values no problem, if you can go to 1000s, you are going to hit problems. There are ways round them, but they aren't something you want to do unless it's necessary, so try it with a lot of data when testing, if that's a likely.


Collapse -

In response to your Peer Mail

by Tony Hopkinson In reply to Basically

First a bit of TR etiquette, I and many other members wont respond to a PM from someone we don't know, especially when they themselves have peer mail disabled.

Also given the nature of your request, keeping it in public, so others can use it is a good thing.

Right bollocking over.

10,000 entries and you want to do 'type ahead', oh boy.

You could try searching for list box style component that will do/facillitate this with access.

The next solution depends on your data, is there anyway you can narrow things down in a user friendly way.

Say if you were searching for companies, can you put a type in, or a geographical locations, keep all as an option, anything to provide an initial cut on that 10,000 and get it smaller. It's naff but another list box with A-Z, might work out you can do A-B, ... X-Z or some such.

The other way is to preindex, like is slow, and access doesn't have fast text search as such, so you knock up your own.

You need to play about with this, it depends on how many characters you have to type in before you zero in on a specific record, lets say the average was three, for a laugh.
Add`another column, populate it with the first three characters of the target string and bang an index on it.
Drive your list box from that

Select Distinct SurNamePrefix From Myname Where surnameprefix like '!MyListBox.Value + '%'

Then when you get to char 4 flip over to

Select SurName From Myname Where surname like '!MyListBox.Value + '%' and SurnamePrefix = SubString(!MyListBox.Value,1,3)

The only down side is you can't see the full name in list box until you get to the fourth char, but that only impacts someone who is searching through item by item anyway.

Stick a checkbox next to the list box default checked called fast search or some such. Go back to the naff slow way if it's not checked.


Related Discussions

Related Forums