More Autonumber

[Steve]

Can the Autonumber field be reset? Someone accidently created a number
10 times higher than we need or want to use by inserting an extra digit.
Now, as we increment, the number is much too high. I have deleted the
offending entry, but the next number still comes up.

Steve
Please reply to group

 

[Joseph Meehan]

Can the Autonumber field be reset? Someone accidently created a
number 10 times higher than we need or want to use by inserting an
extra digit. Now, as we increment, the number is much too high. I
have deleted the offending entry, but the next number still comes up.

Steve
Please reply to group

I suggest you may not want to use Autonumber for that use. Autonumbers are
designed to provide unique numbers. It in not designed to provide numbers
in order and for a number of reasons may not do so. As a result using them
in any application where the user sees the numbers is likely to end up with
confusion.

There are other ways of providing the numbers you want depending on the
particual application.

Yes it is possible to reset, but are you really sure you want to. It
appears you have just proven that Autonumber is not right for you.

 

[Wayne Morgan]

First, an autonumber shouldn't be used for anything except a way to uniquely
identify a record. If you want a number that has significance, you need to
create you own.

Compacting the database will reset the autonumber to one increment (you can
have it count by other than one) more than the highest number in use.

 

[Ken Snell [MVP]]

Wayne, in ACCESS 2000 and up, compacting will not reset the autonumber so
long as there are any records in the table. The next record will still get
the "next higher than the last number used" number.

 

[Steve]

Unfortunately, the database is not my own, so I don't think I can modify
it very easily. It is written in Access 97, which I can at least access
with my copy of A2K, but cannot change any of the objects.

Am I stuck? I know the Autonumber field shouldn't be used for "real"
information, and I don't write any of my own routines that way, but I'm
trying to work within a system I have no control over...
Thanks for your replies
Steve

 

[John Vinson]

Can the Autonumber field be reset? Someone accidently created a number
10 times higher than we need or want to use by inserting an extra digit.
Now, as we increment, the number is much too high. I have deleted the
offending entry, but the next number still comes up.

Steve
Please reply to group

This is one of many reasons that Autonumbers should generally not be
used in any way that exposes them to the user. They will ALWAYS have
gaps. If you'll be presenting the value to users, it's best to use a
manually or programmatically incremented Long Integer value instead.

Since autonumber fields cannot be edited, I wonder how the user was
able to do this? With an Append query?

Depending on your version of Access, you may be able to delete the
too-high entry (or entries) and Compact the database.

John W. Vinson[MVP]

 

[Steve Schapel]

Steve,

Well, in accordance with Ken's advice, if it is Access 97, simply
compacting the database should fix it.

Another approach to re-setting the seed is provided by Allen Browne at
http://members.iinet.net.au/~allenbrowne/ser-40.html