Help with Autonumber

[Guest]

Hello,

I need help with autonumber. The database I'm working in is new to me and
I'm not sure how to correct this problem. I have just started updating and
want to enter new records but Autonumber goes from 97 to 52301478. It also
isn't really including this new number into the records as it still says at
the bottom record 97 of 97. I can't for the life of me correct it and it's
posing a big problem to my office. PLEASE someone help!

Thanks,
stephanie

 

[Norman Yuan]

Search this group for "AutoNumber", an issue has been asked over and over,
day by day. You would get a lot answers. Basicall, you should not care what
the next autonumber access gives you. If you care, you should not use it. On
the other hand, since it jumps so huge leap, you may be in the place to
compact your database.

 

[ThePandaManCan]

Stephanie,

If I understand correctly... it seems to me as if you have 97
records already. Your AutoNumber would detect this and select "98" as
your next numbered record. Does this make sense?

A record number will be different from the primary key (the
identifying data) for the record. For example, a record number 4 in a
database could have a primary key value of autonumber 8. Additionally,
you can overwrite the autonumber (if it is your primary key) as long as
the value you enter is UNIQUE.

Does this help or have I gone off track?

-ThePandaMan

 

[Guest]

Autonumber does not guarantee sequential numbers, just unique numbers. If
you need sequential numbers, you need to implement some way of creating them.
There are many approaches, and you can do a search here for autonumber and
find some solutions. Here is one example
http://support.microsoft.com/kb/q191253/

 

[Jeff L]

If you look at the design of your table, select the autonumber field.
Down below where you see the General Tab, there should be a field
called New Values. Is that set to Random? If it is, then that's why
you are getting the wacky numbers. Unfortunately, Access will not
allow you to change it back to Increment. You would have to delete the
field and create a different AutoNumber field. It would be wise to
make a backup of your database before you do any of this.

 

[Guest]

Stephanie:

Don't rely on an autonumber for sequential numbering; its not what they are
for, only to guarantee uniqueness. If you really need sequential numbering
compute your own. In a single user database its easy, just look up the last
number and add 1. Do this in the BeforeInsert event procedure of the data
entry form with something like:

Me.MyNumber = DMax("MyNumber", "MyTable") + 1

This is not reliable in a multi-user environment on a network however, as
conflicts can arise. One way to get around this is to store the last number
in an external database which is opened exclusively in code to get the next
number. I've posted a demo at the following link. This also allows you to
reset the start point at which numbering resumes when the next record is
added:


http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=23839&webtag=ws-msdevapps


Ken Sheridan
Stafford, England

 

[Larry Daugherty]

Hi Steph,

You've gotten some good ideas from other posts but the most
significant thing I see is that you don't seem to be able to add
records: you say that the record count stays at 97.

Allen Browne has dug into busted Autonumbers. Maybe this will help:

http://allenbrowne.com/ser-40.html

HTH

 

[Guest]

PandaMan,

Would you mind explaining a little further. This is really foreign to me
and having to fix someone else's database when I just want to get my work
finished is extremely frustrating to me. I am almost at the point of
throwing the damn computer out the window.

Your post is the only one that comes close to what I am conceiving my
problem to be. The autonumber is set to not create duplicates and it's set
to incremental. I, with my very limited knowledge of Access, cannot
understand why it would jump from 97 to a six digit number. Unique number or
not, why would it go incremental until 97 and then go crazy?

If you can, please explain what you mean and with maybe directions a child
could understand for me? I'd REALLY appreciate it.

Thanks,
stephanie

 

[John Vinson]

An Autonumber IS NOT A 'RECORD NUMBER'.

It's not guaranteed to be sequential; autonumbers will always have
gaps.

I don't know why your table has such a huge increment, unless perhaps
you have Replicated the database (which makes all autonumber fields
into Random increment) - but you should treat the autonumber field as
it is designed: a guaranteed unique, meaningless identifier. Most
developers keep autonumbers concealed from user view, and use them
*only* as unique identifiers and linking fields - for which purpose
52301478 serves just exactly as well as 98.

If you want sequential gapless numbers, don't use an autonumber - use
a Long Integer field and maintain it using VBA code. This can be
tricky if you ever have to delete records.

John W. Vinson[MVP]

 

[Guest]

The quick fix is to copy the table (structure only) , copy the data to the
new table, delete or rename the old table (I prefer to just rename in the
short term), rename the new table, and correct any relationships. This
should get the autonumber to increment properly for the short term, but you
will have gaps and discrepancies again and again. The best is not to rely on
Autonumber, but to program a way to get a sequential numbers like everyone
has suggested. Autonumber can never be relied upon to match the record
number.