>>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.
>
>I've almost had a problem like this last October; I circumvented it some other way, don't remember how, but the worm kept biting down my brain on this problem. Here's the theory of what I planned to do:
>
>Have a lookup table with several numbers to offer or keep. All the records contain an invoice number, date/time of issue and status. One record keeps the last number used, and has status Main. Anybody receiving a number, increments the value in this record and creates a record with this number, current date/time and status Waiting. Now if this invoice is saved, it discards its record (it should be recycled later, actually - blanked and refilled, or deleted and undeleted); if it cancels the invoice, it marks its number with status Available. Also, a maintainance routine could revert status for all numbers Waiting and too old (older than three days or so) as Available.
>
>Now if a new number should be issued, it returns the first number with status Available (thus filling the gap) and marks it with new datetime, status Issued; Main is untouched. If there are no Availables, then it uses Main for a new number.
>
>Luckily, I never actually needed this (therefore no code, no testing, just an excercise of mind), though it was real close at the time.

My way:
I have a config table wich stores the next invoice number in a field (next_inv). The program works like this
1. The user presses add button
2. The program returns the next_inv number with no further validation s, AND shows a message: "provisional"
At this point, all the users that intend to create a new invoice are presented with the same value (current next_inv value), but advertised the number is not "Real"
3. The user fills all fields. if decides to cancel, nothing happen. the next_inv field remains unchanged; it hasn't been touched yet.
4. When a user decides to save, then lock the config file, get the real next_inv number, assign it to the invoice's number field and increment. While this is happening, no other user could assign the same number as the current, and we're sure that this user won't abort. Once incremented, release the file and let the other users fight for it in order to get their real numbers.
5. Once saved, change the message from "Provisional" to "real" or simply "".

It assures no gaps and real numbers assignment in an scenario where you don't use the temporary number as a key to maintain a relation with a grid or something. If that is the case, you should solve the problem of changing the provisional number for the real one when saving.