order by

[Bart]

I am a little perplexed about the method for setting the default sort order
in a form. I have two forms in different databases that are almost identical
(one is an early backup of the other). In the backup version the sort is
working fine using the "order by" property. The same form in the current
database isn't. I have made a few minor modifications to the form such as tab
order but these forms are very close to the same. I have looked at the
underlying tables and tried sorting those but that does not seem to have any
effect either. I even tried creating new forms based on the same tables and
find that I can't get those to sort correctly either. Not in the current
database nor in the backup. Not by sorting the underlying table nor by
specifying the "order by" property for the form.

I know this is going to turn out to be something very simple but right now
it is driving me nuts.

Can someone set me straight on what the problem is?

thanks,

Paul

 

[Allen Browne]

Paul, the best method is to create a query, and use the Sorting row of the
query to specify the sort order you want. Then set the RecordSource property
of the form to this query.

The form's OrderBy property works only if you turn OrderByOn on as well.
Typically that would be done in code (e.g. in Form_Open), but the query is
better.

If you don't specify any sort order (query or OrderBy), Access typically
presents the records in the order of their primary key, though theoretically
the order is undefined.

 

[Chris Reveille]

Try basing the form on a query. Do the dorting in the query

 

[Bart]

Thanks for that Allen. I am curious as to why you suggest using a query over
setting OrderByOn to true. Is it just that the the OrderByOn toggle is a bit
out of the way or is there something more to it?

thanks,

Paul

 

[Allen Browne]

Query does it directly.

OrderBy is less obvious (so not as easy to maintain), and requires code to
turn it on, and there's more chance of inconsistencies between versions
(e.g. if a later version decides to store the OrderByOn property and turn it
on by default.)

So, the query meets the KIS principle.