unique sequential invoice numbering

[Lesg]

Hi Guys,

Looking for suggestions to avoid duplicate invoice numbering we are experiencing as system has been introduced to multiple users.

The orders table has an orderID which is the autonumber for the table, however, as the client wants to ensure they have sequential numbering for invoices a field 'invoice' was created and as part of the open form process a new invoice number is created using the following code:

Forms!frmOrderPlacement!invoice = Nz(DMax("[invoice]", "orders"), 0) + 1

The problem with this is, if another user starts an invoice before the current one is save, the same number can be added to the next invoice, hence there is then duplicated invoice number.

This only happens quite rarely, but often enough to be a nuisance, so if anyone can suggest a better way to handle this I would appreciate it?

[Welshgasman]

Safest way is to only get next number as you commit the record, not as you dirty the record.

[Minty]

Another option is to store the last invoice number in a table, and always update it as soon as an new number is issued.

[Welshgasman]

Another option is to store the last invoice number in a table, and always update it as soon as an new number is issued.

Doesn't that leaves gaps if the user backs out of finishing the record?

[Minty]

Doesn't that leaves gaps if the user backs out of finishing the record?

I would normally record that invoice as Cancelled, and leave a record of it.
The other method is to check if it's not been updated when you cancel the record and reset it back where to the original number.

TBH all methods are not 100% fool proof, which is why marking something as cancelled/deleted is actually by far the safest route.

[NoellaG]

The most foolproof method I found: create a sequence for the invoice numbers. In Access this can be a table with one (or more if you have different numbering for credit notes) number field. You create your invoice in status 'created'. As long it is inthis status you can still alter the invoice. Once you click the button 'print' an invoice number is fetched from the sequence table and the number in the sequence table is augmented with 1. Once your invoice is numbered you cannot change the invoice any more.
Fetching the number from the sequence table is very fast and there is little chance 2 users will be doing this at the same time, and if so, the second user will be blocked by the update.

[Lesg]

Thanks for those suggestions guys, all of which I have considered, and valid, so will test out all to see which works the best, thank you