Reset AutoNumber

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How do you get AutoNumber to reset itself once you delete a record? Say that
I have a series of records in a table:

1, 2, 3, 4, 5…

But I then delete record #5 from the table, and later add a new record to
the table. Shouldn't Access re-use the number "5" ? Instead, Access is
giving me a record sequence like this:

1, 2, 3, 4, 6…
 
This is normal behaviour for Access.

Once an Autonumber entry has been assigned to a record, it can never be used
again for a different record, so as you have found, if a record is deleted
then another one added there is a gap in the Autonumber sequence.

This isn't usually a problem, as the Autonumber field is designed only to
provide a unique key for each record and is not intended to be used by the
user.
However, it is unfortunately quite common to see databases written where
people have used the Autonumber as part of their record data, maybe to
provide a serial number to a paper form etc, and they then have gaps in their
numberiing sequence.

If this is your situation, then I'm afraid you will have to live with gaps
in your sequence until you devise another way to number your forms.

HTH

Neil
www.nwarwick.co.uk
 
Back
Top