Sorting on field other than primary key

[usmcgrunt]

Changing the Lookup to a TextBox does *not* change the value in the field. Back it up first so you will feel confident. http://www.btabdevelopment.com/ts/removelookups

Wow, it is amazing the links you have to all of these common NOVICE programming issues.

[RuralGuy]

Uhhh...that's what I was at one time.

[usmcgrunt]

Oh no, your not using Lookup Fields in tables are you? http://www.mvps.org/access/lookupfields.htm
All they do is confuse the programmer.

Mission Accomplished. All lookup fields removed from tables and data remains intact. Wheww!

[RuralGuy]

Now I need to look back to see why we got on this topic in the first place.

[RuralGuy]

Ah...ha. Look back at my post #12. Oops!

[usmcgrunt]

Ah...ha. Look back at my post #12. Oops!

Ah yes! give me a minute and I will explain what I did. It worked but I am not sure it is the most efficient way. To be brief, it involved creating a query based on a table and then sorting the query by a non-primary key. But I also had to reference a different query in my form for the combobox to sort correctly.

More details in a minute.

I have to fix a table first. I had a field that was formatted as text but I needed number so I lost all my data. Luckily I saved it to an excel file so I am fixing it and then will import it.

[usmcgrunt]

Ok, so what I did was base the subform in question off of one query and then I based the combobox off another. Now when I click on the combo box the records are ordered by the Reference Number, an AlphaNumberic value, and not the primary key (autonumber). So the values sort from A to Z just the way I needed.

Here are the details of the queries:

qryReferenceData (all fields from tblReferenceData & tbleReferenceList) See my first post. This is the recordsource for the subform.

qryReferenceList (just the field from tbleReferenceList sorted by RefNum) See my first post. This is the recordsource for the combobox in the subform.

Please tell me I learned something since I did not learn how to use the OrderBy clause.

Leaving work now. So I may not reply back for 30 minutes or so.

[RuralGuy]

There is a lot to be said for "It works the way I wanted". If that is the case, who am I to make any changes. You done good! Mark it Solved if you are ready.

[usmcgrunt]

SOLVED it its!

[RuralGuy]

Taa Daa! Another satisfied customer.