[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 683
  • Last Modified:

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!)

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

   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...
2 Solutions
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.
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.


> 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)?

    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!
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?
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).


Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now