OrderBy on SubForm doesn't sort records

Posted on 2008-11-13
Last Modified: 2013-11-28
The AllowFilter property of the subform is set to "Yes".  The subform's RecordSource property points to a query that has "CBEQ" as one of the cols.  The form's OnOrder property is "CBEQ DESC".

The rows don't display in CBEQ DESC order.  

 I know I can add the sort to the query, but different cols can be selected by a control on the main form and thus change the displayed sort order.  So I need to be able to change the reference in the OnOrder.

The form in its saved state has the OnOrder property set as described above and after opening the form the records aren't displaying in the correct order.   I tried leaving off the DESC, but it still displays in the same order.

Any suggestions of how to fix this problem?
Question by:katzwhite
    LVL 75

    Assisted Solution

    by:DatabaseMX (Joe Anderson - Access MVP)
    Try this in code:

    Private Sub Form_Load()

    me.OrderBy =  "CBEQ DESC"
    Me.OrderByOn = True                 '*** you need this

    End Sub


    Author Comment

    I don't see an OrderByOn property in either the main form or the subform.  I only have record source, Filter, Order By & Allow Filters.. In both forms the Allow filters property is set to Yes

    I'm not using VBA on the initial opening fo the form as the default sort is set per the subforms properties.  This isn't working.  When I get this to work, I'll try the VBA  to allow different sort orders.

    LVL 75

    Accepted Solution

    "I don't see an OrderByOn property in either the main form or the subform."

    That property is not on the Property sheet.  From the Help File:

    "For reports, the OrderByOn property must be set to Yes to apply the sort order specified by the object's OrderBy property.

    **For forms, select the field by which you want to sort the records and either click the appropriate Sort button on the toolbar, or point to Sort on the Records menu and click the appropriate command on the submenu. ***** You can also set the OrderByOn property for either forms or reports by using Visual Basic."

    AFAIK ... you will need to set the OrderByOn property in code.  That's the only way I've got it to work.


    Author Closing Comment

    I had read the Help, but somehow decided "OrderByOn" was for reports only since I couldn't find it on the form property sheet.  Thanks for the assist.

    Best wishes
    LVL 75

    Expert Comment

    by:DatabaseMX (Joe Anderson - Access MVP)
    you are welcome.  


    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Join & Write a Comment

    In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

    728 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

    20 Experts available now in Live!

    Get 1:1 Help Now