How to sort a continuous form by combo lookup text?

I have a "products" table displayed on a continuous form.  Each product is tied to a department: the form uses a combo box to tie department IDs to a list of departments (nice, basic normalization!)

Product
   ID = 1   Name = "Charmin"                         DeptID=1
   ID = 2   Name = "Bunny Luv Peeled Carrots" DeptID =2

Dept
   deptID = 1   deptName = "Dry Goods"
   deptID = 2   depName = "Produce"  etc.

The form recordSource is PRODUCT: the combo box controlsource is "deptID", with a rowsource query "select deptID, deptName from dept". The combo is bound to the first field, with field widths 0;1")
OF course, data is entered into products in a random order.

Q: is there some way to sort the data initially by the DEPTNAME rather than the NUMERIC VALUE stored -- and still be able to edit the data?  I thought I'd seen some nifty way to do this with an unbound dlookup, but  darned if I can recreate it...  When I change the form recordsource from PRODUCT to a join, the data is no longer editable...
ayurkowskiAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

puppydogbuddyCommented:
if you are referring to the combo box, all you have to do is select asc in the sort row of the dept name column in query grid for the combo box.
jjafferrCommented:
if I understand you correctly,
you have continuous form, with different fields, and you want to sort this form using a certain field,

if this is the case, then follow these steps:
1. open the Form properties,
2. right click on the field you want to sort, then select ascending,
3. now look at the properties > "order by"
4. copy this this,
5. properties > "on load" , select [Event Procedure], then click on the button with 3 little dots, it will take you to VBA,
6. write this there
me.orderby="What you copied"
'                  ^                      ^  make sure it is between the quotes
7. save.

jaffer

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
harfangCommented:
Hi,

> Q: is there some way to sort the data initially by the DEPTNAME rather than the NUMERIC VALUE stored -- and still be able to edit the data?

Yes. If you simply use the sort [AZ] button while in the combo box, you will get the sort order you want. In the form's properties, you will see something like this:

    Order By: Lookup_<name of combo>.deptName

This will create a "light" join to the lookup table and sort using the designated field. However, you seem to have another problem:

> When I change the form recordsource from PRODUCT to a join, the data is no longer editable...

Is this query updatable (provided I have the right table and field names)?

    SELECT DISTINCTROW Product.*
    FROM Product LEFT JOIN Dept
    ON Product.deptID = Dept.deptID
    ORDER BY depName;

If it's not, make sure that deptID is the PRIMARY KEY field of Dept. Theoretically, a unique index (no duplicates) should be enough, but in this case it should be the key. Also make sure that the data types are the same, e.g. Autonumber to long integer, or three-character text to three-character text...

In Access, a joined query is updatable, provided the join is made on key fields (primary key to foreign key), or at least on fields having unique indexes.

Once it works and you have added depName to the query, you can use it as sort order, as I did above.

Good luck!
(°v°)
ayurkowskiAuthor Commented:
Excellent answers, gentlemen. NOT the way I remember seening it done, but a new and neat trick that I'll add to the toolbox. JJaffer gets the major points for the full and complete answer; however, 50 points and kudos to puppydogbuddy as he correctly pointed out that you need to assign the sort order while the form is LIVE, rather than in design mode.

I don't suppose one of you guys would be interested in explaining why, after setting the sort order, the form's referenced "order by" becomes "lookup_<name of the combo box object>.<name of the retrieved field>.
I assume this is some type of join happening "in the backgroud", but... where is this defined?
harfangCommented:
I think I answered that already ;)

Yes, it's a join happening in the background, referencing the table or query of the combo, in a light sort of join. It uses tools like DISTINCTROW and LEFT JOIN to ensure that the main form's records remain editable (see again my example above).

Cheers!
(°v°)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.