How to filter a listbox? (1 Viewer)

jamlaw

Registered User.
Local time
Today, 11:45
Joined
Mar 19, 2012
Messages
12
I have built an access db and I have a form with a list box on it that lists all my customers.

Cust Id, Last Name, First Name etc

I can scroll down the list and when I double click on the particular customer it will open up another form with their details on it which is what I want.

As I have so many customer records what I'd like to have is a text box at the top of the form that will filter the list by surname, ie if i type 'Jones' only the customers with that surname appear in my list box. I can then double click the record to view their details like I'm already doing.

Anyone got any ideas?

Thanks
 

MarkK

bit cruncher
Local time
Today, 04:45
Joined
Mar 17, 2004
Messages
8,178
There is code here that handles every keystroke and updates an ActiveX Listview, similar I think, to what you want to do. It might be a bit complicated tho.
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 22:45
Joined
Jan 20, 2009
Messages
12,849
Use a subform instead of a listbox. The form supports filtering and can be made to look like a listbox by removing the borders, selector etc.

Another way is to set the recordset of the listbox and apply the filter to the recordset.
 

jamlaw

Registered User.
Local time
Today, 11:45
Joined
Mar 19, 2012
Messages
12
Thank you so much guys.

John Big Booty I used your link/code. I cant thank you enough for this.

Really big thanks
 

gunslingor

Registered User.
Local time
Today, 04:45
Joined
Jan 13, 2009
Messages
50
I had a similar problem and found the ideal solution and wanted to share.

I had a form with a key textbox and a list box with IP addresses. I wanted the list box IP addresses to be filtered based on what key is displayed in the textbox (basically, so the list box only shows the IP addresses for that particular record/device/key). I did all this with VBA and it worked, but it requires changing the rowsource of the list box when the key changes and requerying... this was fairly slow and the form flashes and updates in an unpretty way. So I set out to try to define the rowsource in the properties window instead of dynamically changing it in VB (access always works better when you use these toy controls, don't treat it like a typical SQL DB)... finally solve it thanks to this post http://stackoverflow.com/questions/2871155/filtering-a-listbox-based-on-combobox-selection. A few important points for those with the same issue:
1. The query builder doesn't recognize Me.anything... you have to use collections like Forms![formname]!key_txt_box in the conditions of the listbox row source.
2. everytime the key_txt_box changes, you have to requery the listbox only!

i.e. you can dynamically set the values to be displayed in a listbox by using the query builder and collections, but the listbox must be requeried on every change.

With this method employeed, the flashing and slowness is gone, everything is pretty as hell and works perfectly... this method makes the listbox behavior more like a subform than a typical crappy access listbox (expert users should understand what I'm talking about here).
 

Users who are viewing this thread

Top Bottom