Solved

# What sort order is used when the OrderByOn property of a form is set to False?

Posted on 2006-05-11
222 Views
What determines the sort order when the OrderByOn property of a form is set to False?
0
Question by:Milewskp

LVL 65

Accepted Solution

A form with .OrderByOn = False will display records in whatever order is provided to it by it's .RecordSource table or query.
0

LVL 1

Author Comment

I have two cases that behave differently. Both are forms based on a query, which in turn is based on a table:

1. The form displays records per the sort order of the query.
2. The form displays records in the natural (unsorted) order of the table, in spite of the fact that the query has a sort order.
0

LVL 1

Author Comment

Oops,
I take that back - in the second case, the form was based on the table.

Final question:
What if OrderByOn =True and OrderBy = "" ? Same result as OrderByOn = False
0

LVL 65

Expert Comment

You'd have to test it; either a runtime error would occur when the form opens, or nothing happens.
Obviously, if you want to de-activate this set .OrderByOn = False and .OrderBy = ""
0

LVL 16

Expert Comment

If the Orderby = "" this has the same effect as setting the value of OrderByOn = False.

Rick
0

LVL 20

Assisted Solution

I believe the recordset (table or query) rules when the form/report does not 'override' the Order.  Once you've ORDERBYON = TRUE and select a new order - THEN the form/report overrides the table/query sorting.

Scott C
0

LVL 1

Author Comment

I've done some testing on this and here is what I've seen:
If the OrderByOn property of a form is false, then:

- If the form is based on a table, the form is sorted by the table's natural order ( Note: the records will only appear in the same order as the table if the Order By property of the table is blank).

- if the form is based on a query, then the records will be sorted per the ORDER clause of teh query  (Note: the records will only appear in the same order as the query if the Order By property of the query is blank). If the query has no ORDER clause, then the form is sorted by the natural order of the table(s) that the qeury is based on.

- OrderBy = "" gives the same result as OrderByOn = False (I agree with Rick).

0

LVL 16

Assisted Solution

Milewskp,

Your tests are correct although I might be able to offer one additiona point of insight...

If the form is based on a table (not a query) the records are sorted in the table's natural order (as you stated).  What you may not know is what determines that "natural order".  Records in the table are at first arranged in the order in which they are added.  Once a Compact and Repair has been done, however, the records are resorted in the order defined by the table's Primary Key.  If a Compound Key is used then the first field listed is the primary sort and the 2nd field listed is the secondary sort.  Comopund keys are often used, (in conjuction with periodic compact and repairs) to improve performance.  Ie: Say you have a table called "tblOrderDetail" that has an AutoNumber (Unique) field named OrderDetailID and a Foreign Key named CustomerID.  If your primary key included CustomerID (Listed first) and then OrderDetailID (listed 2nd) records would be lumped together by CustomerID and then secondarily sorted by the OrderID field each time a compact and repair was done.  While new records being added will temporarily throw some records out of the nice tidy grouping the grouping that does exist is enough to boost performance when you go to look at those records in a sub form since most of what the sub form is looking for will be found all in one place.  Primary Key's can also be sorted by Ascending or Descending Order by editing the Indexes for the table, (just another little trick to get things to naturally sort in the order you'd most like to see them.

Asside from that Table and/or Queries determine the sort order when either of two conditions are true...
OrderBy = "" OR OrderByOn = True

The only time OrderBy will overide recordsorces (table or query) sort order is when both of two conditions are true...
OrderBy = "Field1 DESC, Field2, Field3"    (a valid list of fields in the order in which you want them sorted - descending sorts can be done as illustrated here)
AND
OrderByOn = True

If either condition is not met then we are back to giving precedence of order to the Table and/or Query (whichever was used in the Form's recordsource)

Hope that helps clear things up for you.

*******************************

Another very important detail to take not of is the reaction that Parent and Child Sub Form's have on each other when setting the value of     OrderByOn = False.

If you run the following line of code...
Me.OrderByOn = False
From either the Parent and/or Sub form you are likely to be suprised and anoyed to learn that the OrderByOn property has been set to False on All Parent and Sub form's involved within all forms involved.  This is not what I'd call one of Microsoft's brighter moments but that is how it works so be aware and watch out, setting OrderByOn = False on any form (Parent or Sub form) clears the deck accross the board and this can be a real problem if that is not what you want.   Just though you should know.

RIck
0

LVL 1

Author Comment

Rick, you've gone above and beyond the call of duty! Thanks for that info. (Where did you manage to find it - trial and error, or is it documented?)
0

LVL 16

Expert Comment

You're most welcome for the info.  For the most part the information that I posted (about 75% of it) is documented but very little is to be found all in one place  To trace those parts that are documented I'd have to trace you back to over a dozen sources of documentation that details various pieces to the overall puzzle.  The other 25% (such as clearing the Order By Property on any form. Parent, sub. etc - doesn't matter - having the unexpected result of removing the filter from all forms, well this kind of stuff was found by experience, (many years of it).

Hope it helps.

Rick
0

## Featured Post

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.