How to sort a listbox whose "Row Source Type" is "Field List"

Hello Experts,

I have a list box in my Form. The "Row Source Type" of this listbox is set to "Field List" and during run time the list box shows the headers/fields of a query. The query has many fields and hence I would like the values appearing in the listbox to appear in sorted order. Please help.

Please let me know if you need any further information. Many thanks for your help.

LVL 12
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

lee555J5Connect With a Mentor Commented:
Restated: Sort the run-time "Field List" entries in the listbox?
If so, the only way I know to do that would be to use an SQL SELECT statement as the listbox's .RowSource and order the fields the way you want them.
For example, tblExample has fields fldA1, fldB1, fldA2 (notice the order)
If I set listbox.RowSource = tblExample, my run-time listbox entries will be
If I set listbox.RowSource = SELECT fldA1, fldA2, fldB1 FROM tblExample;, my run-time entries will be
It's manual, and therefore not ideal, but it works. :-)
BTW, using the ORDER BY clause as in "SELECT * FROM tblExample ORDER BY blah;" won't work because ORDER BY sorts records, not fields.

Using a table or query object as the .RowSource leaves you at the mercy of the field order in the object.
Dan Van VleetConnect With a Mentor BankerCommented:
Please be more specific, I know you cannot make a query from a data set like that, but you could probably make it into its own field..

For example, in one database I made, I had the tables: Employees, Products, and Transactions.

So, on the transactions form, there was a listbox of employees and products, in this way, you would be able to create a query of transactions, based on which employee or which product (or both) was in the transaction, instead of just an numerical list of transaction #s..

Hope this helps, if not, be more specific, include your database if possible, and let me know what you're trying to accomplish.

sstampfAuthor Commented:
Thanks for taking out time to read my question and post comments. I would consider the answer to be "What you are looking for is not possible".
All Courses

From novice to tech pro — start learning today.