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.

 

[DCrake]

Simple, do not use lookups at field level. Use relationships between tables and store PK - FK relationships.

 

[bo_dong]

David,

Have you read my original post carefully and understood my question? I don't see how your answer has any relevancy to my question. Please read the questions three times before you post any replies!

 

[BobMcClellan]

Looks like David's reply answered your question.
Instead of using
select * from Benchmark c order by (select p.BenchmarkCategoryName from BenchmarkCategory p where p.BenchmarkCategoryID = c.BenchmarkCategoryID) desc

USE something Like....
Select b.Column1YouActuallyNeed, b.Column2YouActuallyNeed from Benchmark b inner join BenchMarkCategoryName bcn on b.BenchmarkCategoryID = bcn.BenchmarkCategoryID order by bcn.BenchmarkCategoryName

 

[bo_dong]

The Benchmark table has many columns with IDs (BenchmarkCategoryID, TypeID, CountryID, MarketID...). If I join all of them, then form is not based on a table, but on a view. And I don't want to bring in extra fields into the form.

Anyway, this was just a hack. The best way is to sort the form based on the text values in the ComboBox. Let's focus on this first. Can it be done using VBA?

 

[BobMcClellan]

Again... b.Column1YouActuallyNeed, b.Column2YouActuallyNeed
Create the dataset you want to work with.

 

[DCrake]

Pictures tell a thousand words, you image attachements clearly indicated you were use table level lookups. That is why I commented the way I did. Therefore there was no need to read the full explanation.

 

[bo_dong]

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