>Assuming that the table has an unique index on the Id_Nr field of the table, we usually do the following:
>
>
> Sele Mytable && The table you are about to update
> set orde to Unique_Index && A unique index
> go bott && Get the highest number
> stor Id_Nr+1 to mnewnr && Add 1 to it
> INSE INTO Mytable (Id_Nr) valu(mnewnr) && Insert the new number
>
>
>If the Id_Nr field is a character type, use the following:
>
> Sele Mytable && The table you are about to update
> set orde to Unique_Index && A unique index
> go bott && Get the highest char number
> stor STR(VAL(Id_Nr)+1,4) to mnewnr && Based on a 4 char field
> INSE INTO Mytable (Id_Nr) valu(mnewnr) && Insert the new number
>
> Keep in mind that if you use a character field for the Id or Reference number of a table, you should always start at a number like 100, 1000, 10000, etc. If you start at a blank the index will display the records incorrectly.
>
>
>Hope it helps... :)
This method is open to duplicate keys in a busy multi-user system. Between the time of GO BOTTOM and INSERT INTO, it is possible that another user will do the same, thus causing duplicate keys. For a better method, see the Primary Keys KB article on my web site.
Craig Berntson
MCSD, Microsoft .Net MVP, Grape City Community Influencer