Alan,

If the user wants control over the values then it is not a purely unique ID, it has meaning to the user. I would leave the user's ID alone and add another field to be the PK for the table. That way the PK is not overloaded with meaning other than uniquely identifying a record within the table and you can then user an Identity field for the purpose. You can maintain the other field as you are now and at least the problem you are facing is simply duplicate values in that field and not referential integrity of the database and/or the table.

>Hi Frank, thanks for the reply.
>
>>when using a VFP back end I use a similar process to generate keys, but >for SQL Server I use an identity column for my PKs. FWIW any new >developement in VFP I'll look at using an autoinc field.
>
>I'm not sure my client would be happy with the values allocated by SQL - they want to have more control over number ranges. If it was VFP alone I wouldn't have the update problem, because I could use RLOCK() to ensure that only one user could update the value at any one time. Theoretically, using the key and modified fields in the UPDATE command should work, but I guess there's something I'm missing in my code.
>
>Regards,
>Alan