Here is BenchmarkCategory table. Notice the BenchmarkCategoryName is not sorted alphabetically when sorted by BenchmarkCategoryID.
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.
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?
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.