Auto Numbering

[Lori]

Why would the auto number feature change from incremental to random when no
one else has access to the tables but me. ALL of my ID fields have changed
and I didn't change them???

 

[Gina Whipp]

Lori,

Autonumber has a mind of it's own. It could change for many reasons a few
of which are... records deleted will increment and 'use' the number even
though the record was deleted or 'backed-out'. and then of course there's
corruption What's important is that if you *need* that number to be
incremental then you should create your own. MyPrimaryKeyField =
DMax("YourIDField", "YourTable") + 1 on the Before_Update of the form on
which you wish to happen is one of the many ways but does not take into
account multiple users, there are other functions for that.

Autonumber should only be used when you do't care what the number is as long
as it's unique.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Michael J. Strickland]

Why would the auto number feature change from incremental to random
when no
one else has access to the tables but me. ALL of my ID fields have
changed
and I didn't change them???

Do you mean the field type (in table design) is changed from Auto Number
to Number or do you mean the fields is still Auto number but the values
are not in sequence?

If you sort on a field, your auto number values will be out of order.

If you delete records, your auto number values will have gaps in them.

One way to restore sequential auto-numbering is to copy the table
(structure only) to a new table. Then copy all records from the old
table by selecting all fields except the Auto Number field (click on
field name to right of auto number field, hold shift, and click on last
field name). Then hit Ctl-C (copy). Minimize the table, and open the new
empty table. Again select all but the auto number fields and hit Ctl-V
(paste).

Your original table records should be pasted in with new, sequential,
auto-numbers.



--

 

[Gina Whipp]

Michael,

You are right she can do that but actually it's almost a waste of time as
this will most likely happen again and again. The other problem would be if
she has related tables. Since it sounds like she cares about the Autonumber
I don't think this is the best choice. (Personal opinion)

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[Armen Stein]

Why would the auto number feature change from incremental to random when no
one else has access to the tables but me. ALL of my ID fields have changed
and I didn't change them???

Did you try to implement Access Replication? I'm not an expert at it,
but I think it switches AutoNumbers to Random.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Tony Toews [MVP]]

Did you try to implement Access Replication? I'm not an expert at it,
but I think it switches AutoNumbers to Random.

I'm no expert either but yes invoking replication does switch
autonumber to random.

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/