Avatar of Murray Brown
Murray Brown
Flag for United Kingdom of Great Britain and Northern Ireland asked on

Access VBA Useful ways to sort/search a ListBox

Hi

I have a ListBox that has thousands of items in it. Each item contains several numbers and caharcters.
I want to put a few buttons in to do useful searching and sorting. Can anyone suggest a few ways to do this? As you can see in the image the list contains several preceding numbers

1
VBA

Avatar of undefined
Last Comment
Murray Brown

8/22/2022 - Mon
Daniel Pineault

Personally, with a list like that, I'd add a textbox which would filter the combo box down to a manageable list.

You mention buttons, what did you have in mind?  If you have categories, ... then you could include combo boxes that when the selection changes it filters the listbox.
Murray Brown

ASKER
Thanks. I wanted a "Sort Asc" and "Sort Desc" buttons
Murray Brown

ASKER
also the listbox copied data from another control. How would I filter the value list
Your help has saved me hundreds of hours of internet surfing.
fblack61
Scott McDaniel (EE MVE )

Use a query to fill the listbox, and add an ORDER BY clause. By default, queries are sorted ASCending, so:

SELECT * FROM SomeTable ORDER BY SomeField DESC

To remove that sort:

SELECT * FROM SomeTable ORDER BY SomeField
Mark Edwards

Value List as rowsources for controls is what you use when you DON'T want/need to perform actions like sort and filter on the list.  That is best left to SQL/Table/Query rowsources.
Value List are usually strings delimited  by a semi-colon between entries (see what your list is using).  The number of columns determine how many items in the Value List string are on a row (don't forget to include hidden columns).
If there is only one column on the listbox and each item in the value list is a row in the listbox, then you can easily run the value string thru the Split() function to bust it into an array.
You can then put the array into a table.
You can then create an SQL statement with the table and apply any kind of sorting/filtering you want to do.
Google "Access VBA Split function" and "Using Access VBA Arrays" as training exercises.
If it gets over your head, I'll send you a sample.
Another option is to cycle thru the listbox entries and append them to a table.
Murray Brown

ASKER
Hi. Thanks. Yes please could you send a sample. This sounds like what I need
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
Mark Edwards

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Murray Brown

ASKER
thanks very much