Create new unique invoice number

[Peter Hibbs]

Access 2003

Table tblInvoices has field InvoiceNo (amongst others) which is type
Number (Long), Indexed property = Yes (No Duplicates) and is PK.

Using this code in the button which creates a new invoice -

Me.AllowAdditions = True
DoCmd.GoToRecord , , acNewRec
InvoiceNo = DMax("InvoiceNo", "tblInvoices") + 1
.... (more code)
....

No problem so far.

This DB is being used on a LAN with BE on main PC and FEs on six other
PCs where any user could create a new invoice record at any time.

Now Sods Law states that if it is possible for two users to create two
invoices with the *same* invoice number, it will happen one day
(probably 3 years down the line when I have forgotten all about it).

Is there any way (other than making the InvoiceNo field unique) to
ensure that Access will not allocate the same number if two users
click the button in the same millisecond or will Access throw up an
error in this case?

Peter Hibbs.

 

[Peter Hibbs]

OK Pieter,

Presumably you are just updating the underlying record with the new
invoice number as soon as it is allocated. Would it not still be
possible, however unlikely, for two users to run the same code at the
same instant on different PCs. Having said that I don't know if Access
would erroneously use the same number because it is virtually
impossible to test for this case in practice. Perhaps I should run it
as is and hope it never happens.

Peter Hibbs.

 

[Larry Linson]

OK Pieter,

Presumably you are just updating the underlying record with the new
invoice number as soon as it is allocated. Would it not still be
possible, however unlikely, for two users to run the same code at the
same instant on different PCs. Having said that I don't know if Access
would erroneously use the same number because it is virtually
impossible to test for this case in practice. Perhaps I should run it
as is and hope it never happens.

You can eliminate the possiblity by using a separate table to hold the most
current invoice number and using a level of locking and/or transactions to
force only one user at a time can obtain the next invoice number, then
update it, before the next user can get access. You could possibly do this
on your actual data table, but there may be other updates (of existing
records) going on whose performance would be adversely affected.

Larry Linson
Microsoft Access MVP

 

[Peter Hibbs]

Larry,

Thanks for the idea, I will give it a try.

Peter Hibbs.