Link to home
Create AccountLog in
Avatar of Murray Brown
Murray BrownFlag 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

User generated image
Avatar of Daniel Pineault
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.
Avatar of Murray Brown

ASKER

Thanks. I wanted a "Sort Asc" and "Sort Desc" buttons
also the listbox copied data from another control. How would I filter the value list
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
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.
Hi. Thanks. Yes please could you send a sample. This sounds like what I need
ASKER CERTIFIED SOLUTION
Avatar of Mark Edwards
Mark Edwards
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
thanks very much