Sorting a Combobox List Having a Table as its Source 1

[BlaineR]

Hi,

I have a dropdown box listing Agents from the Agent table. The user selects one from the list and the rest of the Agent's information automatically populates the form. The dropdown box lists the Agent's last name, first name, and title. So far, so good.

Problem is, right now the dropdown list that appears in the dropdown is sorting by the Agent's unique identifier, the key field in the Agent table. Not very user friendly, obviously, when they have to scroll through 500 agents to find the one they want.

My question is, how do I get the dropdown list to sort by the Agent last name?

I thought if I sorted the Agent table by last name, that would do it. But apparently that didn't work (I might have done something wrong, though).

Thanks for your time and help!

Blaine

 

[Rolliee]

Blaine,

Your combo box has as its data source something that looks like this:

"SELECT field1,field2,field3 from table1;"

if you change that to:

"SELECT field1,field2,field3 from table1 ORDER BY field2;"

you will see the cbo ordered by field 2.

Those fields may be named table1.field1,table1,field2 ...

but that is just a more precise way of coding - and better but more complex for the teaching mode.

R

 

[mrf1xa]

Go to the properties box for your combo. In the row source line, click to the right of the white spacve and you will get the build button with 3 dots on- click this and you will see the query grid. Find the row you want and in the 'sort' row slect 'Ascending' or 'descending' to suit. Close the grid and say yes when it asks if you want to save changes to SQL statement.

This should do it.

Nigel
Didn't someone say work is supposed to be fun? They didn't have computers then I guess....

 

[rdodge]

You can't really tell a combo box how to sort the data directly, but what you can do is create a query to have all the same fields and records as the table, then to sort by last name, then by first name. Once you have done this, you can then bind the form to the query.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.

 

[BlaineR]

Rolliee, Nigel, Ronald:

Thanks very much for your insights and direction. I've printed off your posts and will work through them this weekend. I'm very thankful to have this forum and many kind folks like yourselves as a resource.

Regards,
Blaine

 

[BlaineR]

Hi,

For the sake of completeness, just wanted to report back that things worked out great. I started with Nigel's suggestion, as it seemed easiest. I just clicked on the three dots which took me to the query grid, where I simply clicked on the sort cell, saved the query and voila. Exactly what I wanted.

Thanks again!

Blaine