Subform's OrderBy is being ignored

Posted on 2006-05-03
Last Modified: 2008-03-17
Working with Access 2000 front-end and SQL Server 2000 back-end...

I have a subform that is resistant to ordering its records by a certain value.  Please read on.

I have a view (qryBrandLots) which combines data from two different tables (LOT and AUCTION).  By default the results are ordered by a value called LotNum, which comes from the LOT table.

qryBrandLots is the record source for a datasheet form (DataForm).  DataForm's OrderBy property is set to qryBrandLots.LotNum, and the Unique Table property is set to LOT.  DataForm is itself used as a subform on another form (MainForm), and MainForm is opened from elsewhere by a command button with the following command in its OnClick event:

   DoCmd.OpenForm stDocName, acNormal, , stFilter

When MainForm opens, the DataForm subform displays the properly filtered records, but they are NOT ordered by LotNum.  Rather, they appear to be ordered by EbayNum -- which is the key value of the AUCTION table that's referenced in the underlying query.

I don't want to have to order the records in the subform AFTER it opens.  How can I get it to pay attention to its own OrderBy property?

Question by:Galisteo8
    LVL 12

    Expert Comment

    > I have a view (qryBrandLots) which combines data from two different
    > tables (LOT and AUCTION).  By default the results are ordered by a
    > value called LotNum, which comes from the LOT table.
    Does this query have the Order By statement in it? maybe you could post the query?

    LVL 14

    Accepted Solution

    in the subform, instead of specifying "qryBrandLots" as its record source, specify this:

       SELECT * FROM qryBrandLots ORDER BY LotNum

    and leave the subform's OrderBy empty (this way, you don't have to deal with the "OrderByOn" property, which must be on for the filter to work and which can only be set programmatically).

    LVL 8

    Author Comment


    I'll try your suggestion.  Will post the query for perusal if that doesn't do the trick.
    LVL 8

    Author Comment

    Thanks, that got it.  Should have thought of that!


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    760 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

    8 Experts available now in Live!

    Get 1:1 Help Now