Learn how to use and navigate the new features included in Bootstrap 4, the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first websites.
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
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
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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.
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.
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
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.
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.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial