>I have this app that is networked. I need to create CONSECUTIVE Invoice numbers. Currently what I do is look up the last number used in the invoice table and at the time of actually saving the record I check if the # is used. If used increment by 1 till not used and save.
>
>The problem I am having is that being a networked system, The refresh is not that fast and recently I had the situation that 2 invoices, saved 1 second apart, had the same number!!
>
>The other problem is that the number MUST be consecutive with no gaps.So I am unable to use the normal method of storing the # in a separate table as the user may decide to abort the transaction.
>
>I was thinking on the lines of a dummy table to hold the record for a few seconds before updating the main table in batch form, changing the invoice # as necessary, but then I will not be able print the invoice.
>
>Any ideas to solve this??
>
>Thanks
>Bernard

Create a separate control table intended for control no. with 2 fields. The first one is just for temporary invoice no. being used when still adding and manipulating new invoice, and the other one as the permanent invoice no. replacing the first one when saving the record (Good for Trading Company Invoicing). If for POINT-OF-SALE systems, the second one (permanent invoice no.) would replace the permanent one after printing the invoice.

Upon adding new invoice:

SELECT controltable
ntempinvoice = controltable.tempinvno + 1
REPLACE controltable.tempinvno WITH ntempinvoice

SELECT invoice
INSERT INTO...

SELECT invoice_item
INSERT INTO...

Upon Saving / After Printing the New invoice:
SELECT controltable
nperminvoice = controltable.perminvno + 1
REPLACE perminvno WITH nperminvoice

nprevinvoice = invoice.invno
SELECT invoice
REPLACE invoice.invno WITH nperminvoice

SELECT invoice_item
REPLACE invoice_item.invno WITH nperminvoice FOR invoice_item.invno = nprevno

The reason why it is important to use two fields in control table is to avoid missing permanent invoice no. caused by TABLEREVERTS, ABNORMAL SHUTDOWN, and to have its functionality reliable even a USER opens more than one WORKSTATION at a time.

This is our approach for all our trading invoicing systems and point-of-sales.