Generate my own autonumber?

[Alec M1BNK]

How is the question. i need to create an auto incrementing number for each
successive record, but one which will maintain the sequence infallibly with
no gaps.

Before anyone asks, here's the why. Our accounts manager is unhappy becase
the autoinvoicing system I have implemented inside an access database, uses
the autonumber record ID from the table of invoice data to generate an
invoice reference number. As we all know, any database using autonumber
aquires gaps, missing numbers from the sequence, more users = more mistakes =
more gaps. Apparently the auditors might think there is something fishy going
on if the invoice numbers are not in perfect sequence.

Alec

 

[John Vinson]

How is the question. i need to create an auto incrementing number for each
successive record, but one which will maintain the sequence infallibly with
no gaps.

Before anyone asks, here's the why. Our accounts manager is unhappy becase
the autoinvoicing system I have implemented inside an access database, uses
the autonumber record ID from the table of invoice data to generate an
invoice reference number. As we all know, any database using autonumber
aquires gaps, missing numbers from the sequence, more users = more mistakes =
more gaps. Apparently the auditors might think there is something fishy going
on if the invoice numbers are not in perfect sequence.

Alec

You will clearly have to prohibit ANY record deletions; no matter how
you do the numbering, it makes no sense to renumber invoices 34
through 8832 if invoice 33 needs to be deleted... <g> This can be
handled by adding a yes/no "InvoiceExpunged" field.

You can use VBA code in a suitable Form event to add a calculated Long
Integer invoice number. This question is asked all the time, and there
are in fact several suggestions. I'd recommend the one from the
following Knowledge Base article:

http://support.microsoft.com/kb/240317/en-us

John W. Vinson[MVP]

 

[BruceM]

I have used a technique based on an example that can be found here:
http://www.rogersaccesslibrary.com/download3.asp?SampleName=AutonumberProblem.mdb
I don't know if there is an advantage to using the code in the link John
provided. I am offering this link simply as a suggestion that has worked
for me. Note that if you don't need for the user to see the number when the
record is being created, holding off running the code until the form's
Before Update event will minimize the likelihood of an error because of a
duplicate value. From another angle, running the code after the first field
is completed (or after the first character is typed in a new record) will
similarly minimize the chance of an error event. Roger's example includes a
provision for when two users are entering a record at the same time, but the
fewer error events you need to run the better, I would think.