how to fill the gaps in Autonumbering field?

[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

 

[Joseph Meehan]

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

I suggest you may not want to use Autonumber for that use. Autonumbers
are designed to provide unique numbers. It in not designed to provide
numbers in order and for a number of reasons may not do so. As a result
using them in any application where the user sees the numbers is likely to
end up with confusion.

There are other ways of providing the numbers you want depending on the
particual application.

 

[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?

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

 

[Paul Overway]

No.

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.