Autonumbers

[David]

When I set up my Customer database 3 yrs ago the record
number corresponded to the Customer ID which was set to
autonumber. At some point and I am not sure what, the
record number no longer tracks the Customer ID, in fact
it seems to be floating. The customer ID is still set to
autonumber but every time I load the database a
particular customer record will have a different record
number. For example when I go the end of the records
which is number 1666, customer ID 415 appears, and not
customer ID 1666. I may have deleated some records but
probably less than 10. I find this irritating because I
can no longer find a record simply by entering the ID
number in the record field, I can't even get close to the
number - I have to search in the customer ID field. Is
there any way I can correct this?

 

[Van T. Dinh]

1. The main (only?) purpose of an AutoNumber Field in to provide uniqueness
to each Record in the Table.

The AutoNumber Field WILL develop gaps since when Records are deleted, the
AutoNumber values are not recovered. Even when you abandon the entry of a
new Record, the AutoNumber value allocated to this incomplete / abandoned
new Record is not recovered. Thus, the AutoNumber Field value will NOT
track the Record Number (my guess is that you meant the number X in "Record
X of XX Records" in the Navigation Bar).

2. The current Record Number X is simply an ordinal position of the Record
in the Recordset of the Datasheet / Form. If you have a Sorting order using
Field(s) other than the AutoNumber Field, the order of the Records (i.e. the
Record Numbers of Records) in the Recordset will almost certainly be
different from the AutoNumber Field values.

3. Note that Records in a Table are stored unordered and the database
engine will retrieve Records the most efficient way it thinks. If you want
to sort Records in particular order, you need to explicitly set the Sort
order. Access seems to sort Records according to the PrimaryKey Field
(probably your AutoNumber Field is the PK Field) if no Sort order is
specified. However, this is not guaranteed. In large Tables in SQL Server
Back-End, I often see that Records are retrieved in no particular order if
no Sort order is specified.

It may not be easy to fix this, especially if you have "related" Records.
You will also need to think of using something else rather than AutoNumber
Field.

 

[Jeff Boyce]

David

To follow on what Van has already offered, it seems possible that you are
using a table view of your data. To get a "sorted" view, create a query and
use a form, based on that query.

Besides, forms offer a rich event environment, where tables simply don't
provide that kind of control.