"Autonumber" problems

[Pietro]

I have a tabel with the field "code" that's an autonumber field , it happens
that the users may start to add a new record then they cancel it, so we have
some missed codes...

How can i reset the "Autonumber" field ?

 

[Rick Brandt]

I have a tabel with the field "code" that's an autonumber field , it
happens that the users may start to add a new record then they cancel
it, so we have some missed codes...

How can i reset the "Autonumber" field ?

If you care about the value in any way other than it being unique then don't use
an AutoNumber. There are numerous, completely normal situations in Access that
will cause a gap to be produced in an AutoNumber sequence (even large gaps).

If you need an unbroken sequence of numbers then you need to use a standard
number field and use code to assign the value in the form used to add records.

If you Google these groups on "Custom AutoNumber" or similar you will see that
this is discussed ALL the time and you will find many examples of rolling your
own numbers.

 

[Arvin Meyer [MVP]]

Autonumbers should only be use to guarantee a unique record. You cannot
reset them without deleting the field and recreating it. You can only do
that if there no other tables linked to that number. If there are other
tables, you must change the datatype to Long Integer, then create a new
autonumber field, then create a matching foreign key field for each table
and use an update query to add the key value. If you don't understand that,
you probably shouldn't attempt to do it.

In the future you can build your own autonumbering system that is resetable.
One such would be to use a Default Value property = to:

=DMax("[IDField]","YourTable")+1

Even that has its share of problems. Two users could start at the exact same
time, one user deletes after a second has started.

That brings up another question. Why do you need absolute sequences? If you
were using printed paper codes, and 1 was started and discarded or otherwise
spoiled, you'd be in the same predicament.

 

[TedMi]

Autonumbers should only be use to guarantee a unique record.

But they do nothing of the sort, if by "unique" you mean unique information,
as opposed to unique data. And most business applications are concerned with
information, not data. You can enter the same identical information multiple
times, and Access will happily accept it, assigning different autonumber
values to each. To ensure uniqueness of *information*, you need a unique
index on some other field(s) that contain information meaningful to the
application. Autonumbers are used to uniquely *identify* a record, but it
will also uniquely identify multiple copies of the *same* information.

My advice on when to use autonumbers: You should use them only if it makes
NO DIFFERENCE to the app's business rules if autonumbers are assigned
sequentially or randomly.

 

[Larry Linson]

Arvin is too nice a person to "slap you down" for adding an "if by..." to
his post so that you can then argue against the strawman you built rather
than what he wrote (which is, indeed, correct as written).

But I am perfectly willing to be blunt -- your post did not add to the
discussion at hand (which dealt with the nature of Autonumbers, unique but
not necessarily monotonically increasing), and did not increase your stature
in the community.

Larry Linson
Microsoft Access MVP

 

[Arvin Meyer [MVP]]

An autonumber with a unique index will always guarantee a unique record.
Nothing guarantees that data will not be duplicated in multiple rows. You
need to run code to check bit by bit every value in every field to do that.
After all, different people can and do, have the same name, live at the same
address, and share the same phone number. Your advice on autonumbers is
really advice on Primary Keys. The key needs to be independent of the data,
except in very rare cases where the data can also be a key, sure as State or
Country codes.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[TedMi]

... But I am perfectly willing to be blunt -- your post did not add to the
discussion at hand (which dealt with the nature of Autonumbers, unique but
not necessarily monotonically increasing), and did not increase your stature
in the community.

Hey, Hey, Hey, why all this hostility. My message was not intended to insult
anyone, only to record my many years of experience as a DB consultant, when I
would get messages from naive users along the lines of:
"Autonumbers are supposed to ensure uniqueness, so I have created a table
with fields AutoNumber, STCode, STName - So how come I find in it multiple
instances of ON Ontario, or AK Alaska ??? Why doesn't the the AutoNumber
prevent that?"
Yes, obvious to you and me, but not to the questioner. So I repeat,
Autonumbers DO NOT ENSURE UNIQUENESS OF INFORMATION.

...discussion at hand (which dealt with the nature of Autonumbers...<

The OP's post did NOT deal with the nature of autonumbers themselves, but
with the nature of identifying SEQUENTIAL business information, for which
Autonumber are totally unsuited. Just as they are for identifying UNIQUE
business information.

 

[Arvin Meyer [MVP]]

Ted, nowhere did we say that data would be unique, each field needs its own
unique index to ensure that. What I said is that, the record is unique,
which it is.