Losing Order By in a Form

[whdyck]

I'm using MS Access 2003.

Probably a bonehead question, but I'm puzzled about something. When I enter a Record Source and an Order By property for a form, I expect it to retain those settings. However, if I open the form and sort the records differently, the Order By property is changed to the new sort order. Even if I close the app and reopen, it still displays the revised Order By. If I put the Order By in the Record Source (as a SQL statement), it simply adds the new sort order to the Order By property, which seems to override the one in the Record Source.

So if I wish to always sort in a particular order when the form opens, do I have to put something in the Open event? (and in the Close event to restore it in case the user has sorted another way after opening?)

Or what is best practice for sorting?

Thanks.

Wayne

 

[RainLover]

You mentioned Record Source twice as though they were different things.

A form can only have one Record Source at a time. The sort oder should be contained in that. Delete any reference to sort order in the properties if that is what you have done. Same with filters. Get rid of them and use the Record Source.

 

[whdyck]

You mentioned Record Source twice as though they were different things.

A form can only have one Record Source at a time. The sort oder should be contained in that. Delete any reference to sort order in the properties if that is what you have done. Same with filters. Get rid of them and use the Record Source.

I was talking about the *contents* of the Record Source being different.

Case 1

Record Source:
tblLiaStageDetail

Order By:
OrderReferenceNumber, ShippingWeek, LiaStatusCode, LastUpdateDateTime

When I open the form and sort on-the-fly by LastUpdateDateTime, I see the following when I then open the form properties:

Record Source:
tblLiaStageDetail

Order By:
tblLiaStageDetail.LastUpdateDateTime

Case 2

Record Source:
SELECT * FROM tblLiaStageDetail ORDER BY OrderReferenceNumber, ShippingWeek, LiaStatusCode, LastUpdateDateTime

Order By:
(blank)

When I open the form and sort on-the-fly by LastUpdateDateTime, I see the following when I then open the form properties:

Record Source:
SELECT * FROM tblLiaStageDetail ORDER BY OrderReferenceNumber, ShippingWeek, LiaStatusCode, LastUpdateDateTime;

Order By:
LastUpdateDateTime

The next time I open the form, it displays with the user-selected sort order. But I want it to always open with the sort order I predefined.

Even if I put the sort order in the record source (as you suggested), the sort order does not survive any changes to sort order made by the user. But I want the form to always open with the same sort order.

Wayne