Order of Records in a Combo Box

[JWalcott]

The primary key of the table from which the combobox data is provided is an automated number. The table contains first and last names. Although the records need to be in the alphabetical order of last names, the Last Name cannot be the primary key as there are duplicate names. How can I get the names in the combo box to appear in alphabetical order???

Any help would be appreciated.

 

[dk]

Hi,

Go into form design view and right-click your combobox and choose Properties. This will bring up the properties window. Look for the Row Source which will contain an SQL statement something like this:-

SELECT MyTable.ID, MyTable.LastName, MyTable.FirstName FROM MyTable;

Add an ORDER BY clause at the end of this statement (before the ; ) specifying that you wish to sort by LastName then FirstName e.g.

SELECT MyTable.ID, MyTable.LastName, MyTable.FirstName FROM MyTable ORDER BY MyTable.LastName, MyTable.FirstName;

 

[DAZarlengo]

I use some different approaches. One simple solution is to use a query as the combo box source. You can do all the sorting you like there. Unless told otherwise, the combo box will display records as sorted in the table or query. In complex situations, you can even use a query as the source for another query!

Another approach I use when it makes sense to, is to add another field to the table, format it as integer, and enter numbers in the order I want the combo list sorted. Works every time. Most helpful for small lists that don't change much.

For some larger tables, I copy all the data into Excel, quickly add sequential numbers, delete all the records from the Access table, then use Paste-Append to put the data base. (Make a backup copy of the table first!!)

Let us know how it turns out