AutoNumber jumps numbering

[Gogo]

Database has record 59 showing, tabing to enter data returns
autonumber to 134. What happened to cause this, how would I fix it?

[Fred Zuckerman]

"Gogo" <ep****@cableone.netwrote in message
news:d6**********************************@u12g2000 prd.googlegroups.com...

Database has record 59 showing, tabing to enter data returns
autonumber to 134. What happened to cause this, how would I fix it?

1. Adding new records, then deleting them.
2. Begining to add a new record, then "undo-ing"
Fred Zuckerman

[Rick Brandt]

Gogo wrote:

Database has record 59 showing, tabing to enter data returns
autonumber to 134. What happened to cause this, how would I fix it?

AutoNumber's only specification is that they be unique. If you have
requirements beyond that then don't use an AutoNumber.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[Alan Forsyth]

Part of your fix is to Compact the database.
"Fixing" the numbers is a more complex question.

Gogo <ep****@cableone.netwrote in news:d6f05294-1959-44de-b5c0-
38**********@u12g2000prd.googlegroups.com:

Database has record 59 showing, tabing to enter data returns
autonumber to 134. What happened to cause this, how would I fix it?

[Tony Toews [MVP]]

Alan Forsyth <No*@iHome.nzwrote:

>Part of your fix is to Compact the database.

If the BE MDB is in A97 format then compacting will lower the next autonumber value
to 1 higher than the current highest number. However in A2000 the autonumber seed is
only reset to 1 if there are no records in the table and isn't reset to the highest
number present in the table.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

[Alan Forsyth]

"Tony Toews [MVP]" <tt****@telusplanet.netwrote in
news:n1********************************@4ax.com:

Alan Forsyth <No*@iHome.nzwrote:

>>Part of your fix is to Compact the database.

If the BE MDB is in A97 format then compacting will lower the next
autonumber value to 1 higher than the current highest number. However
in A2000 the autonumber seed is only reset to 1 if there are no
records in the table and isn't reset to the highest number present in
the table.

Tony

Thanks, Tony. Yes, I use A97 and mo, I didn't know about the A2000
behaviour.

Alan

[Gogo]

On May 22, 4:02*am, "Rick Brandt" <[e-mail]:

Gogo wrote:

Database has record 59 showing, tabing to enter data returns
autonumber to 134. *What happened to cause this, how would I fix it?

AutoNumber's only specification is that they be unique. *If you have
requirements beyond that then don't use an AutoNumber.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt * at * Hunter * dot * com

Using 2003 I've tried compact and repair, no success. You've
mentioned is autonumber to result in unique numbers? I don't
understand how to change ClientIDNumber field to get the correct
autonumbering to return. Help

[Gogo]

On May 22, 4:02*am, "Rick Brandt" <[e-mail]:

Gogo wrote:

Database has record 59 showing, tabing to enter data returns
autonumber to 134. *What happened to cause this, how would I fix it?

AutoNumber's only specification is that they be unique. *If you have
requirements beyond that then don't use an AutoNumber.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt * at * Hunter * dot * com

Hey Rick: I just figured it out. to get AutoNumbering corrected. I
added a new field Client_ID_Number as AutoNumber, deleted the
incorrect field, saved structure and data with this new field. It
worked. Thanks,
Gogo

[Les Desser]

In article
<92**********************************@t12g2000prg. googlegroups.com>,
Gogo <ep****@cableone.netSat, 31 May 2008 17:55:49 writes

>On May 22, 4:02*am, "Rick Brandt" <[e-mail]:

>Gogo wrote:

Database has record 59 showing, tabing to enter data returns
autonumber to 134. *What happened to cause this, how would I fix it?

AutoNumber's only specification is that they be unique. *If you have
requirements beyond that then don't use an AutoNumber.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt * at * Hunter * dot * com

Using 2003 I've tried compact and repair, no success. You've
mentioned is autonumber to result in unique numbers? I don't
understand how to change ClientIDNumber field to get the correct
autonumbering to return. Help

Why does it bother you that numbers are missing?

Every time you start a new record (the pencil mark appears in the record
selector) and you then cancel by pressing Escape, you lose a number.

Rick is saying that the specification of an Autonumber is that it is
unique and there is no guarantee there will not be gaps.

In practice, if you specify an AN as type "Increment" it should increase
one by one, but it is very easy to lose numbers - but so what?
--
Les Desser
(The Reply-to address IS valid)

[Larry Linson]

"Gogo" <ep****@cableone.netwrote

On May 22, 4:02 am, "Rick Brandt" <[e-mail]:

Gogo wrote:

Database has record 59 showing, tabing to enter data returns
autonumber to 134. What happened to cause this, how would I fix it?

AutoNumber's only specification is that they be unique. If you have
requirements beyond that then don't use an AutoNumber.
Rick Brandt, Microsoft Access MVP

Hey Rick: I just figured it out. to get AutoNumbering corrected. I
added a new field Client_ID_Number as AutoNumber, deleted the
incorrect field, saved structure and data with this new field. It
worked. Thanks, Gogo

Hey, Gogo...

No, you did NOT figure it out. Knowledgeable people have explained to you,
over and again, that Autonumbers (even using "Increment") are not
_specified_ to be monotonically increasing. How many times do they have to
repeat it before you understand that you have recreated a series without the
gaps, but you have not "corrected the problem" BECAUSE THERE WAS NOT A
PROBLEM?

Autonumbers are for internal use as surrogate keys, and for joining related
tables. They are not for display to humans, because of what you
experienced. And, the probability is very high that you will experience it
again.

If you are using Autonumbers as surrogate keys, and you have more than a
'flat file' design, those numbers will appear in related tables as Foreign
Keys. So what's happened to the linkage between your main table where you
arbitrarily replaced the Field and any related tables that used the original
Field (where you saw the gap) as Foreign Key? You certainly did not say
that you'd done anything to "fix" those Foreign Keys.

They say, "What a tangled web we weave when first we practice to deceive,"
but the same applies to "What a tangled web we weave when we insist on
something being what it is NOT, and then resort to workarounds to try to
make it so."

Give it up, Gogo, baby. And, don't come back here whining when you find
another gap in your "carefully fixed Autonumber" -- which you certainly will
in the circumstances others have described, and in some others not so
well-defined and publicized.

Larry Linson
Microsoft Office Access MVO