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?

"William5271" <William5271@discussions.microsoft.com> wrote in message
news:AD555C2B-18A6-447F-AB2D-BFBCCE2FEB0A@microsoft.com...
>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.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Arvin Meyer [MVP]" <a@m.com> wrote in message
news:Obwa8kkCJHA.3432@TK2MSFTNGP05.phx.gbl...
> "William5271" <William5271@discussions.microsoft.com> wrote in message
> news:AD555C2B-18A6-447F-AB2D-BFBCCE2FEB0A@microsoft.com...
>>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.
> --
> Arvin Meyer, MCP, MVP
> http://www.datastrat.com
> http://www.mvps.org/access
> http://www.accessmvp.com
>