OrderBy on SubForm doesn't sort records

Posted on 2008-11-13
Medium Priority
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
  • 3
  • 2
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 2000 total points
ID: 22956446
Try this in code:

Private Sub Form_Load()

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

End Sub


Author Comment

ID: 22956655
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

DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 2000 total points
ID: 22956914
"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

ID: 31516636
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
ID: 22957866
you are welcome.  


Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

809 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