How to sort a continuous form by combo lookup text?

Posted on 2006-04-11
Last Modified: 2010-07-27
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...
Question by:ayurkowski
    LVL 38

    Assisted Solution

    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.
    LVL 27

    Accepted Solution

    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.

    LVL 58

    Expert Comment


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

    Author Comment

    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?
    LVL 58

    Expert Comment

    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

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
    Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
    Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
    In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now