Autonumber

[Guest]

I'm sure this is a stupid question... I've got a DB with an autonumber that
is being used as a serial number. We must issue orders with sequential
numbers, so this is the best method of tracking. However, I'm a bit
frustrated with "gaps" in my numbering.

1) Someone starts to enter a record, and DB gives autonumber 002.
2) User says "opps", wrong item, and escapes out of record because they
shouldn't be in there.
3) Next time user enters a record, autonumber goes to 003 even though user
canceled input when doing 002.

I need 002 to be used, not 003. 002 is blank because user cancelled.

Is there a way to either:
- Add autonumber AFTER record is completely entered, or
- Code system so that numbers escaped/cancelled after input is started is
available for reuse?

 

[Guest]

The simple answer: live with it (it's an age-old question, and the
justification for auto-number gaps is rather obvious: two users may begin to
create records at almost the same moment; this leaves user1 with autonumber
2 and user2 with autonumber 3. It would be pretty confusing for user2 to get
a message indicating that his autonumber is being changed from 3 to 2 when
User1 cancelled (etc, etc).

The complicated answer (but not really). If you really must have autonumber
with no gaps, dummy up an autonumber-like process in this way (using a
non-autonumber long Integer field, indexed with no duplicates): in the
Form_BeforeUpdate event, find the highest number used (DMax), add one, and
assign it to the field--but only if it is a new record (if Form.NewRecord
Then...).

It would look something like this:

Form_BeforeUpdate(Cancel As Integer)
If Form.NewRecord Then
SerialNumber = nz(DMax("[SerialNumber]","[Item]"),0) + 1
End if

(The NZ is there only to ensure that the first record gets a serial number.)

The chance of two users hitting that event at the same moment is almost
null, but to be absolutely sure, trap the duplicate key error and run it
through again

Option #2: make an unbound form for the data entry. Append the data to the
table when the data is complete. This will generate the auto-number when the
user has no control over stopping the save processl.

Either way, pop up a window giving the user the serial number AFTER the
record is saved

 

[Anthos]

I have a similar requirement with a database I am doing, where each
site needs to have it's own running list of numbers, that are,
specific to that site, and that there be no gaps.
I have a table that is used as a Serial Number matrix, and on this
table, I record an Auto Number (it's not referenced anywhere else) the
site, and the Serial Number, it also has a status (Issued /
Cancelled / Pending) field.

When on the form to enter details, there is a button to generate a
Serial Number, which goes and gets the Sites Maximum Serial Number and
adds one.
The Form with the details, has it's own Autonumber, but that is not
related to the Serial Number, there is a relationship to the
Autonumber for the Serial.

Hope that helps in understanding a working method of this common
issue.

Kind Regards
Anthony Moore