how do I replace a deleted auto number record in access

[Guest]

I have a DVD Library set in in Access. The first colum is my serial # colum
which is auto number. I have 750 DVD's and 750 records. I deleted 2 records
#748 & #749 and now need to replace those. Is there a way to have 748 and
749 show as records again with the serial # 748 and # 749?

 

[Arvin Meyer [MVP]]

I have a DVD Library set in in Access. The first colum is my serial # colum
which is auto number. I have 750 DVD's and 750 records. I deleted 2 records
#748 & #749 and now need to replace those. Is there a way to have 748 and
749 show as records again with the serial # 748 and # 749?

Only by deleteing and recreating the autonumber.

Most numbering systems will leave a gap if a record is deleted. In a
relational database, you cannot change key numbers without changing all the
related foreign keys as well. This is an excersize in futility. A sequential
number is meaningless anyway. What happens in a paper system if a
prenumbered form is destroyed? The key exists for the sole purpose of
relating data. It needs to have no other meaning. If you need another
sequential system, by all means, create one. Just be emotionally prepared
for the inevitable gaps as well.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[TC]

Open the table - or the form that is based on that table - and start
typing the data for a new record. When you've only typed one field, not
the whole record, press the Esc key a few times, to discard the new
record. Repeat this process a few times. Watch what happens to the
Autonumber.

Oops!

Autonumbers *are not* guaranteed to be a strict sequence (1, 2, 3
etc.). The user can easily create gaps in the sequence, as shown above.
This is not a problem with the Autonumber field type. It is a problem
with peoples' *expectation* of how that field type actually works.

HTH,
TC (MVP Access)
http://tc2.atspace.com

 

[Joseph Meehan]

I have a DVD Library set in in Access. The first colum is my serial #
colum which is auto number. I have 750 DVD's and 750 records. I
deleted 2 records #748 & #749 and now need to replace those. Is
there a way to have 748 and 749 show as records again with the serial
# 748 and # 749?

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.