On Wednesday 23 Apr 2003 4:44 pm, Bruce Williams wrote:
> Hi,
>
> I'm creating an invoice in an Access 2000 report. I would like to know
> how can I establish an invoice number that will generate automatically
> in a table? Do I have to create my own numbers or do you have a better
> suggestion. Thanks
If you want sequential invoice numbers with no gaps, then you need to make
sure that only one process allocates numbers at any time.
This means you want to either lock the table or use the serializable
transaction isolation level while allocating invoice numbers.
Allocating the next invoice number is as simple as max(invoice_num)+1 although
many invoice applications use a "next invoice value" in a system table (which
lets you set up an initial invoice number per company).
If you have many clients, you may wish to insert invoices without a number
then create the number in a second transaction to reduce lock contention. Try
a search on the mailing-list archives for other discussions on this.
-- Richard Huxton