>Oleg,
>
>>in multiuser env without locking you can have the same ID for 2 users.
>>Between SELECT and UPDATE someone else could issue SELECT an get the same ID.
>>Thanks,
>>Oleg
>
>I agree, but take a close look at bob's code ; I think he handles this problem very creatively (only using VFP) !

Pascal,

Let me correct that code, as Oleg said, SQLExec return of 1 doesn't indicate that the update succeeded, just that it was sent to the back end. I forgot about that, here is the corrected code...

lSuccess = .f.
Do While Not lSuccess
   cSQL = "SELECT LastNumber FROM NumberTable WHERE cKey = 'TABLENAME'"

   if SQLExec(nConn, cSQL) <> 1
      * Connection error
      Exit
   EndIf

   nLastNumber = SQLResult.LastNumber
   nNewId = nLastNumber + 1

   cSQL = "UPDATE NumberTable SET LastNumber = nNewID WHERE "+ ;
            "cKey='TABLENAME' and LastNumber = nLastNumber"

   if SQLExec(nConn, cSQL) <> 1
      * Connection Error
      Exit
   EndIf

   if SQLExec(nConn, 'Select @@Rowcount') <> 1
      * Connection error
      Exit
   EndIf

   lSuccess = ( SQLResults.Exp = 1 )
EndDo 

If ! lSuccess
   * Indicates a problem and you don't have an id
Else
   * You have a good Id
EndIf

Hope this helps,
BOb