> > -----Original Message-----
> > > Why would you want to unless this is for invoicing or something
> > > similar?
> > >
> > Sarbanes Oxley - accounting standards.
>
> Then you will need something like "An Auditable Series of Numbers" at
> (http://www.ibobjects.com/TechInfo.html)
>
> Rgds
>
> Si Carter

that won't work - you can't go back and use a number which is lower than the
current one just to fill the gap. That part of the paper (above) doesn't
meet the standard either. You have to create invoices numbers as the last
step (account posting) of the invoice. Up til this point the user can't see
an invoice number. When the user finally posts, a gen value is received
(never blocking) and a record insertion to a simple table (2 values) is done
with that gen value and a description "Invoice draft [pk of other table]
posting cancelled by user...." (also never blocking) This insertion is done
under a separate transaction and immediately committed - no other action is
associated with this single action and transaction.
Now the invoice (under it's own/other transaction) is posted with the gen
value previously used as it's Invoice Number which serves as a key into the
simple table. This invoice table has it's own surrugate PK which has nothing
to do with invoice numbers other than being used in the previous description
of "draft [pk.."
Now if the user cancels the commit or there is an exception during this
posting, the real invoice number (gen value) still exists in the simple
table with a cancelled annotation, the draft returns to draft status, but if
the transaction successfully commits, it includes an update to the simple
table description to "Invoice draft [pk of other table] posting successful
by user...."
Now you have an unbroken sequence of invoice numbers, most have successful
annotation, some have cancelled by user whoever in it.
A join between the simple and the full table will show the full story.
Alan