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