Access 2000 auto number not giving right master record number.

[Guest]

New database generates sequential master record number for each new entry.
All went well for first 74 entries.(1-74) When entering what should've been
75-80, auto number on subform table started generating huge non-sequential
numbers. Can this be fixed without starting over with new database?

 

[Guest]

Autonumber in un-reliable for sequential numbering because when a new record
is opened a number is used even if the record is not saved.

You can rename the table, copy the table structure as the orignal name and
append by sorting on the field.

BUT if it is your primary key field the records in the other related tables
must be updated also.

 

[Guest]

Thanks for the quick response Karl. I'm not sure what you mean by "append by
sorting on the field". I'm pretty new at this. I copied a database I received
from a fellow officer for tracking info on street contacts, he did a great
job. I redesigned it to inventory my CD and sheetmusic collections. All was
fine until I replicated it so I could use it in a briefcase file. I tried to
enter a new record on the replica and got the wacky out of sequence numbers.
When I updated with the original, it corrupted it. I just read something
about resizing the autonumber field for replicas. I'm guessing I can only do
that on the original and then do a new replica? Or maybe start over with a
new original, Wudda ya think? Thanks again for the fast help!

 

[Steve Schapel]

Musicmanboggs,

Autonumber data type fields have two Values options, Increment and
Random. In the case of database replication, they *must* be Random, you
have no choice. In any case, even with Increment values, you are
basically trying to use an Autonumber field for an inappropriate
purpose, they are not intended for use as "master record number"... not
if you want the master record number to always be sequential.

 

[Guest]

Thanks Steve, by bouncing back and forth between you guys and the on board
Access help, I might actually learn how to do this right!

 

[Guest]

What I meant by "append by sorting on the field" was if you want the records
to be in the same order then you need to sort them as you append to the new
table.
Like this --
OLD NEW
1 1
2 2
through
73 73
74 74
77 75
79 76
80 77