Access ADP Form Sort on ComboBox Text

[bo_dong]

Here is BenchmarkCategory table. Notice the BenchmarkCategoryName is not sorted alphabetically when sorted by BenchmarkCategoryID.

[See Picture 1]

Here is another table Benchmark with a BenchmarkCategoryID column. I built a form on the table and added a ComboBox displaying the BenchmarkCategoryName. Because users would like to see Names instead of IDs.

[See Picture 2]

My question is how to sort the ComboBox column based on the names, not on IDs? So it looks like the below picture, not the above picture?

[See Picture 3]

I did the above picture by changing the RecordSource on the Benchmark table to

select * from Benchmark c order by (select p.BenchmarkCategoryName from BenchmarkCategory p where p.BenchmarkCategoryID = c.BenchmarkCategoryID) desc

But this hack doesn't work in SubForms. Using Profiler, I saw ADP sending broken queries to SQL Server.

[kennejd]

You can write a query or view for the recordsource of the combobox....then put your desired sort in the query.

[bo_dong]

I don't want to sort the values in the ComboBox. I want to sort the Main Form based on the text values in the ComboBox.

[kennejd]

You need to change the query of your form to include the tables that are bound to the combobox....using the appropriate join.

[bo_dong]

My question then is how to make picture 2 look like picture 3 without changing RecordSource?

[kennejd]

Create a query:
select field1, field2
from table1 t
inner join table2 l
on l.id = t.id
order by l.lupfield