how to fill the gaps in Autonumbering field?

B

Ben

Hi,

When a record is deleted, it creates a gap into the autonumbering field.
Is it possible ti fill the gaps without deleting that field and creating
again?

Thanks
ben
 
J

John Vinson

Hi,

When a record is deleted, it creates a gap into the autonumbering field.
Is it possible ti fill the gaps without deleting that field and creating
again?
Click to expand...

No.

This is how autonumbers are designed to work. Their ONLY purpose is to
provide a guaranteed unique key. They are not guaranteed to be
sequential or free of gaps; there will be a gap even if you start to
enter a record and hit <Esc> after the first field to cancel the
record.

If you care about the values of the number, or cannot tolerate gaps...
don't use Autonumber. Instead, use a Long Integer field and fill it
manually, or using VBA code. Even there filling gaps is problematic:
do you really want the second-newest record in your table to be ID
3442, and the newest record to be ID 5, and then the next one 3443 -
just because record 5 got deleted?


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
P

Paul Overway

John Vinson said:
No.
Click to expand...

Whether or not it is worthwhile to do so, you CAN append a record into a
table using a missing Autonumber value. Still, I personally find no reason
to do it.
 
Top