Prevent Access 2007 from auto sorting tables. 1

[Sorwen]

I hope this is a good forum to ask this as it was the only one I could find that dealt with tables. I have a table with 3 columns: City, State, and AreaCode. For some reason Access 2007 is sorting on the AreaCode field. I assume it is in some way because it is a Number field. I need the data to stay in the order it is entered into the table. Does anyone know if there is a option to prevent Access 2007 from auto sorting? I need to turn it off and not just add an auto increment field or something like that.

-I hate Microsoft!
-Forever and always forward.
-My kingdom for a edit button!

 

[dhookom]

It might be an issue where the table has automatically created an index on the AreaCode field since it contains the text "Code" in it. You could try delete the index and remove all filter/sorts or add a new field such as an autonumber field and set the primary key to the new field.

I generally recommend creating a primary key field in every table. Keep in mind users shouldn't be in tables, just forms where you can set the order in the Record Source. I never expect records to appear in an order that I can't specify with values stored in fields.

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

I need the data to stay in the order it is entered into the table
Use a DateTime creation field and sort by this column.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Sorwen]

Thank you both for your replies. dhookom that did it. Once I index set the index property on the field to "No" the data was put back in the order it was entered. Thanks.

-I hate Microsoft!
-Forever and always forward.
-My kingdom for a edit button!

 

[dhookom]

I treat records in tables like children on a school playground. They might enter the playground in a specific order but once they are playing there is no order.

If I asked them to get into a single file line, I would never expect the order to be the order they entered the playground. If however, I asked them to get in order by date and time of birth, they could logically be expected to line up in a specific order.

Age, weight, height, name, etc are all attributes of the child like fields of a table. They can all be used to order the line. The order they entered the playground is not stored with the child or anywhere else so I feel it is not reliable.

Duane
Hook'D on Access
MS Access MVP

 

[Sorwen]

Normally I don't care, but in this instance for the purpose of this table it is easier to go back through the data if it is in the order it was entered. Also by default as I run across data and I have to make a new entry for it is in a sort of order based off how it will show up in tables from another database.

-I hate Microsoft!
-Forever and always forward.
-My kingdom for a edit button!

 

[dhookom]

If you really care, you should do as suggested by PH and create a field [CreateDate] and set its default to Now().

Duane
Hook'D on Access
MS Access MVP