form sorting differently than query

[William5271]

I have a form with contact records that is based off of a query. The query is
sorting the data based on two fields fine, however the form will not sort
correctly. It seems they get out of order over time. Why is the sorting
different than the query it is based off of?

 

[Arvin Meyer [MVP]]

I have a form with contact records that is based off of a query. The query
is
sorting the data based on two fields fine, however the form will not sort
correctly. It seems they get out of order over time. Why is the sorting
different than the query it is based off of?

Forms often will save the Order By clause or filter. Microsoft, starting in
Access 97, believed that just because you may want to sort something a
certain way once, you probably want to do it again. Most of us would argue
with that. The easiest way to fix it is to open the form in design view,
open the property sheet and remove any sorting and filtering from those
properties.

The only ways I know to change that include setting user-level security up
(pre Access accdb files) and giving users no design permission on forms;
educating users to return the form to the opening state, and using code in a
closing event to delete and save any filters or Order By clauses.

 

[Allen Browne]

Adding to Arvin's comment, there's a new property in A2007 called
OrderByOnLoad. If you are using the new version, make sure this property is
set to No, as well as clearing it in design view as Arvin suggests.

Results could also be affected by reassigning the form's RecordSource in
your code, or by using the advanced filter.

Or perhaps there's a problem with the data type of the sort field, e.g.
Access is performing a text sort on something you think is a date or number.