>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

In my system I use a control table. The invoice number is not allocated until the first print. Until then there is only a "job number". You could use a separate surrogate key. If the user aborts after printing the invoice is cancelled but kept on file for all (well Accounts at least) to see.
Surely invoices can be cancelled??