Sorting a Combo Box List

[Guest]

I posed this question some hours ago but, although apparentely accepted, it seems to have disappeared, so I am afraid I must ask it again.

I have a form with a number of unbound fields, each of which consists of a combo box bound to a table as its record source. Each combo box is associated with a different table, displaying the first two columns of the latter. However, although the first column of each of the source tables is sorted in alphabetical order, this seems to be ignored by the combo boxes, which display their options in almost random order. I cannot see how to change this. Each of the selection lists is quite long and the inability to sort it is therefore particularly inconvenient.

 

[fredg]

I posed this question some hours ago but, although apparentely accepted, it seems to have disappeared, so I am afraid I must ask it again.

I have a form with a number of unbound fields, each of which consists of a combo box bound to a table as its record source. Each combo box is associated with a different table, displaying the first two columns of the latter. However, although the first column of each of the source tables is sorted in alphabetical order, this seems to be ignored by the combo boxes, which display their options in almost random order. I cannot see how to change this. Each of the selection lists is quite long and the inability to sort it is therefore particularly inconvenient.

Change the row source of the combo box from the Table to a Query.
Sort the query as you wish.

Click on the Row Source property line.
Then on the 3 dot button that appears.
Select the table in the dialog box.
Drag the fields you want onto the grid.
Sort the fields as needed.
Access sorts from left to right, so if you need more than one sort
field, make sure they're ordered correctly in the grid.
Save the Query.
That should do it.

 

[tina]

instead of using the table as the combo box's rowsource, use an SQL
statement, as

SELECT FirstFieldName, SecondFieldName FROM TableName ORDER BY FieldName

substituting the appropriate table and field names in the statement. you can
type this directly into the Row Source property line, or use the builder
button (...) at the end of the line to build it in the QBE design grid.

hth

I posed this question some hours ago but, although apparentely accepted,

it seems to have disappeared, so I am afraid I must ask it again.

I have a form with a number of unbound fields, each of which consists of a

combo box bound to a table as its record source. Each combo box is
associated with a different table, displaying the first two columns of the
latter. However, although the first column of each of the source tables is
sorted in alphabetical order, this seems to be ignored by the combo boxes,
which display their options in almost random order. I cannot see how to
change this. Each of the selection lists is quite long and the inability to
sort it is therefore particularly inconvenient.

 

[Guest]

Thank you, Tina and Fred. That was the perfect solution. By using the properties box to change the source table to a query, Access has left me with a set of SQLs, so I suppose I could regard that as a hybrid Tina-Fred solution. I am now left wondering why I did not think of that myself! The answer will probably explain why I will continue to use the News Groups to ask questions rather than to answer them. In the mean time, I will rate this as a 100% successful response. I am very grateful.