How can I stop my form crashing from making duplicate ID's?

[thrainpa]

Hi, basically i have a form for making new users on our system and at first I used the access default system for subforms, where the Primary Key field shows (New) until something is entered into another field in the form and it automatically then creates said new record.

My problem is that more than one user uses this form and if two people use the 'new record' button and overlap then they try to both take the same primary key ID.

Originally I had a system that scanned all records, then (via VBA) changed the master link field to RecordCount +1 and after that it would setfocus to a field in the subform and automatically add that record to the database, it's a little wasteful but it worked, until now.

Now we're having the same issue where two frontends are trying to create seperate records under the same Primary Key, ID.

Is there ANY way to catch the error in the VBA and have it add 1 to the primary key field instead of just crashing out?

 

[speakers_86]

So are you using an autonumber? Also, is the db split? What is the record locking set to?

 

[thrainpa]

So are you using an autonumber? Also, is the db split? What is the record locking set to?

-- EDIT --

Yes the primary key is an autonumber.
If you mean front-end back-end style system then yes it is.
It is set to No Locks.

( Sorry it took so long for a decent reply i had to look those 2 terms up. )

 

[thrainpa]

Okay so, I've got as far as:

Private Sub Form_Error(DataErr As Integer, Response As Integer)
    If DataErr = 3022 Then
        Response = acDataErrContinue
        RunCommand acCmdRecordsGoToNew
        Call FunctionName
    End If
End Sub

But because there is data on the form it is trying to skip past, it will not just delete the 'duplicate record' it has created, is there any way i can get it to stop trying to create said duplicate record?

 

[speakers_86]

Try changing the record locking to edited record.