auto number field displaying gaps once records are deleted

[sam]

Hi All, Can I reset the auto number increment such that it has no gaps?
For eg, If i delete couple rows the auto numbering is not updated on its own..

it says..
1
2
3
6
9
.......


Please Help

Thanks in Advance

 

[Beetle]

Answered in the general questions group. Please don't
post separately to multiple groups. It makes it harder
for you to find your posts and it duplicates the efforts of
the people who volunteer their time here.

 

[John Spencer]

No, you cannot reset the autonumber so it won't have gaps. Once a number has
been used it is no longer available. If you start to add a record and then
cancel, that number is used up. If you delete a record, the number has been
used and is no longer available.

Autonumber's primary purpose is to generate a unique number for the record
that cannot be duplicated and can be used as a primary key field. It is
rarely appropriate to show that number to a user.

If you need a sequential number with no gaps, you will have to develop some
vba to generate the number. And if you plan on deleting records, your system
will need a way to spot the gaps in the sequence and re-issue the number.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Jerry Whittle]

No. Autonumbers are only for producing a unique number which is very handy as
a primary key. In fact there are other ways to have gaps besides being
deleted.

 

[Jeff Boyce]

Why do you care? Access Autonumbers are generally unfit for human
consumption.

If you need a guaranteed sequential identifier, you'll have to 'roll your
own'...

Regards

Jeff Boyce
Microsoft Office/Access MVP