Replace deleted records in table that uses Autonumber

[cpurpleturtle]

I created a database that uses an autonumber field to number purchase orders.
The people using the db deleted some records, so right now they skip from 5
to 9. Is there a way for me to recreate blank records for 6, 7, and 8 so
they can use those and have no gaps in the purchase order numbers?
Thanks!
Cristen

 

[Douglas J. Steele]

Realistically, if you care about gaps in numbering, you probably shouldn't
be using an Autonumber field.

Autonumbers are not guaranteeed to be consecutive. Their only purpose is to
provide a (practically guaranteed) unique value that can be used as a
primary key. 5, 9, 10, 12 serves that purpose just as well as 5, 6, 7, 8

Note that it's not necessary to have deleted records to have gaps. Should a
user start to fill out a form and change his/her mind, the value that would
have been used will be lost.

 

[Wayne-I-M]

Hi

Best bet is never to use the auronumber feature for "anything" other than to
identify the record as unique. Don't use it for anything other than this -
like your purchase order numbers.

So if you have Mr John Smith (ID 1234) and Mr John Smith (ID 1235) your
application will know which John Smith you are working with.

If you want to creat a sqential numbering system you could do this as you
cre4ate record. And, best thing is you can always go back and alter this to
whatever you want - if you delete some records, etc.

Say you have a number is a field called MyNumber in a table called MyTable
you would use
nz(DMax("MyNumber", "MyTable"), 0) + 1
The nz just takes care of the 1st record - as there is no "max"

Don't forget that, as it's not an auto number you can, instead of using the
DMax, just type a number iand so can you record number right.

Hope this helps a bit

 

[Ken Sheridan]

Cristen:

There are really two considerations here:

1. Do you want to guarantee sequential numbering when inserting new records?
2. If a record is deleted do you want to reuse its number?

In either case an autonumber doesn't fit the bill. For guaranteeing
sequential values, while an autonumber will mostly do so its not guaranteed
to as its purpose is to guarantee unique values, not sequential values. Not
only that, but if a user starts to insert a new record, but then abandons it,
that number won't be reused (unless the database is first compacted) so gaps
can very easily creep in even without a record being deleted.

Mark has given you a solution to generate sequential numbers, but be aware
that this can be subject to conflicts in a multi-user environment as two or
more users could be inserting a new record simultaneously and each get the
same number. You will of course have uniquely indexed the field, so this
would cause an error in view of the index violation. There are various ways
of avoiding conflicts, one of which you'll find at the following link, which
also allows you to reset the number at which the sequence will restart when
the next record is inserted, provided the number is greater than the highest
already in use (so it doesn't fill gaps)


http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=23839&webtag=ws-msdevapps


A simpler method by Roger Carlson, but without the provision for resetting,
can be found at:


http://www.rogersaccesslibrary.com/...?TID=395&SID=83z7c11zc7b721d2a1e51989c53d7ffb


The second consideration, filling unused gaps after deletions, can be done
if a non-autonumber long integer number data type is used. One way would be
to step through the rows in an ordered recordset based on the table until the
first gap is reached and insert the value into a new record. Another would
be to store the unused numbers in a separate table and get the lowest, by
means of the DMin function, insert it into the new record, and then delete it
from the unused numbers table. However, I'd question the wisdom of this from
a business point of view as once a number has been assigned to an order, if
its the reassigned to another order it could give rise to confusion.
Somebody might have cited the order number in relation to the original order
before deletion in some document outside of the database; to cite it again in
relation to a different order could cause the two orders to be confused.
You'd also need to be absolutely confident that no rows in a related table,
OrderDetails say, contain reference the now deleted order, i.e. referential
integrity and cascade deletions have been enforced, or when you reassign the
number to a new order it will be referenced by the OrderDetails records from
the deleted order. Unless there is an overpowering business reason why you
need an unbroken sequence of numbers it makes a lot more sense to accept that
their values are arbitrary and just allow an autonumber to assign them,
accepting that there may be gaps in the sequence.

Ken Sheridan
Stafford, England

 

[Paul Shapiro]

I agree with the others 100%- an autonumber is just a unique identifier, and
should not be expected to have any other meaning. Which means gaps in the
sequence have no meaning and therefore don't matter.

But if you REALLY want to do this as you outlined, you can write an append
query, specifying the value for the poID, to add placeholders for those
"missing" numbers, and then get someone to use those placeholders for the
next 3 po's. When you use an append query to insert a value for an
autonumber, it usually messes up Access's tracking of the next autonumber.
So after you insert 6, 7 and 8, Access may well try to use 9 for the next
new record. This fails due to the unique index constraint and you can't add
any more records.

Allen Browne's website has code for resetting Access autonumbers to the next
sequential value.
http://www.allenbrowne.com/ser-40.html

 

[a a r o n . k e m p f]

jesus

in SQL Server, you can write a particular number-- in the middle of
existing autonumber fields by using the SET command
If Jet autonumbers don't meet your needs-- then you should just move
to a database that can handle generating autonumbers and has the
flexibility to let you use them as you want.

-----------------------------------------------------------------------------
set identity_insert tblEmployees ON

INSERT INTO tblEmployees(empID, empFirstName, empLastName)
Values (4, 'Aaron', 'Kempf')

set identity_insert tblEmployees OFF

 

[Danny Lesandrini]

I don't know, Aaron, but seems Access JET is simpler. You don't need to invoke IDENTITY_INSERT to insert an autonumber record.
Just perform the INSERT ... if the ID isn't already used, the record will go in. Easy as pie.

--
Danny J Lesandrini
(e-mail address removed)
www.amazecreations.com



jesus

in SQL Server, you can write a particular number-- in the middle of
existing autonumber fields by using the SET command
If Jet autonumbers don't meet your needs-- then you should just move
to a database that can handle generating autonumbers and has the
flexibility to let you use them as you want.

-----------------------------------------------------------------------------
set identity_insert tblEmployees ON

INSERT INTO tblEmployees(empID, empFirstName, empLastName)
Values (4, 'Aaron', 'Kempf')

set identity_insert tblEmployees OFF