How do you renumber a field that is set to autonumber?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Detwiler - 04 Jul 2006 20:00 GMT

Each record in my database is numbered consecutively using the autonumber
feature. I needed to delete one of the files, however, and now want to assign
the deleted number to another record. Is this possible?

Reply to this Message

Douglas J Steele - 04 Jul 2006 20:04 GMT

No, it's not.

Autonumbers exist for one purpose only: to provide a (practically
guaranteed) unique value that can be used as a primary key. Gaps in the
numbering do not affect that purpose.

If the value of the field is important to you, you probably shouldn't be
using an Autonumber field. As you've discovered, they are not guaranteed to
be gap-free. In fact, it's unusual to even show the value of the field to
users.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> Each record in my database is numbered consecutively using the autonumber
> feature. I needed to delete one of the files, however, and now want to assign
> the deleted number to another record. Is this possible?

Reply to this Message

Steve Schapel - 04 Jul 2006 21:06 GMT

Detwiler,

Yes, you can use an Append Query to add a record to the table with the
"missing" number.

Signature

Steve Schapel, Microsoft Access MVP

Reply to this Message

Terry Kreft - 05 Jul 2006 15:27 GMT

... but see Doug Steeles comments as to why if you "need" to do this then
you are using the autonumber field for th wrong reasosns.

Signature

Terry Kreft

> Detwiler,
>
[quoted text clipped - 4 lines]
> > feature. I needed to delete one of the files, however, and now want to assign
> > the deleted number to another record. Is this possible?

Reply to this Message