• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 269
  • Last Modified:

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

What determines the sort order when the OrderByOn property of a form is set to False?
0
Milewskp
Asked:
Milewskp
  • 4
  • 3
  • 2
  • +1
3 Solutions
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
A form with .OrderByOn = False will display records in whatever order is provided to it by it's .RecordSource table or query.
0
 
MilewskpAuthor Commented:
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
 
MilewskpAuthor Commented:
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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
Rick_RickardsCommented:
If the Orderby = "" this has the same effect as setting the value of OrderByOn = False.

Rick
0
 
clarkscottCommented:
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
 
MilewskpAuthor Commented:
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
 
Rick_RickardsCommented:
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
 
MilewskpAuthor Commented:
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
 
Rick_RickardsCommented:
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

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now