Autonumber Question

[Lauren Pintauro]

I have a table in Access 2007 with a field called "Entry #" set up as an
AutoNumber field to give a new entry in increments. The last entry # was
1345. When I try to enter a new record the next autonumber it gives me is
55582 and so on. Nothing has been changed in design.

How can I fix this ?

 

[John W. Vinson]

On Mon, 7 Jul 2008 08:37:04 -0700, Lauren Pintauro <Lauren

I have a table in Access 2007 with a field called "Entry #" set up as an
AutoNumber field to give a new entry in increments. The last entry # was
1345. When I try to enter a new record the next autonumber it gives me is
55582 and so on. Nothing has been changed in design.

How can I fix this ?

By not using autonumbers.

An autonumber has one purpose, and one purpose only: to provide a meaningless
unique key. Gaps (even large gaps like yours) can happen if you cancel an
entry or run an append query; the numbers can even become random if you
replicate the database.

If you want a human-meaningful sequential number you'll need to use a Long
Integer field and write code to increment it yourself. Search the forum for
"Custom Counter" (http://groups.google.com advanced search would be a good
choice).

 

[Lauren Pintauro]

I don't know if this qualifies as an append query- but I imported records
from Excel where I took the last entry number in my Access Table and filled
that number down a few rows in Excel(e.g., 1390-1399), then imported the
records. I have done this in the past and when I entered a new record in
Excel it just took the next no. (e.g. 1400).

Could this have caused the gap ?

 

[boblarson]

Lauren, yes it could cause it. Now, to re-emphasize what John has said -
Autonumbers really should not be displayed to the user, or used in such a
way, other than just to generate a unique number. Any other use will only
come back to burn you at some point in time (as you have found out). Now,
you can display them for the user or whomever if you don't care what number
it is, just that it is unique (remembering that they can actually be negative
numbers as well - normally when random is selected or replication is used,
but it has been known where it moves from incremental to random unexpectedly
due to something someone does within the database).

--
Bob Larson
Access MVP
Access World Forums Administrator
Utter Access VIP

Tutorials at http://www.btabdevelopment.com

__________________________________

 

[Lauren Pintauro]

Thank you - is there any way to fix this now or should we just continue on
with the new autonumber Access is giving us (55589)?

And in the future let the system assign autonumbers when importing from
Excel ?

(I was just told that I should never import an autonumber-to let the system
assign it)--although I have imported autonumbers in the past and there has
been no problem at all....

 

[John W. Vinson]

Thank you - is there any way to fix this now or should we just continue on
with the new autonumber Access is giving us (55589)?

Well... you *could* delete all the records above the gap; compact the database
to reset the autonumber seed (won't work for all versions of Access); and
reload the data from Excel using an append query. Compact once more after
doing so.

And in the future let the system assign autonumbers when importing from
Excel ?

Best is just not to expose autonumbers. If nobody sees them, then nobody gets
agitated when there's a gap.

(I was just told that I should never import an autonumber-to let the system
assign it)--although I have imported autonumbers in the past and there has
been no problem at all....

You can certainly run an Append query to append long integer values into an
autonumber field, if the data you're importing contains unique and useful
values.