if you are allocating the number, by doing a dmax, then the time to do this is in the beforeupdate event for the form (record), as there will only be a few millisecs when the situation you describe could occur. (ie two users doing a beforeupdate simulataneously)
Had a very similar problem with a site. For some weird reason the auditors wanted the recordID and the record number from Access to match. I had put in an Autonumber fld but within a few days was instructed to do what they wanted - the first delete got the nos. out of synch.
I tested the following code and it worked perfectly. It cycles around the SaveRecord routine until Error 3022 goes, then it save the record using the next highest number.
I got it to save the record after certain info was entered. This minimized the chance of an accidental/wrong entry. lngVisitID is DIMmed in the module, along with the sub GetVisitID().
Error 3022 in the code below is a DAO error " 'The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. etc". If 2 users are in simultaneously, this routine cycles around for both until 1 just beats the other to a record save. The 2nd user then gets the next highest no. and so can save unless another user beats them to it etc.
Private Sub cboAgentID2_AfterUpdate()
On Error GoTo Err_cboAgentID2_AfterUpdate
If Forms!frmVisits!fldVisitID = 0 Then
GetVisitID
Forms!frmVisits!fldVisitID = lngVisitID
End If
SaveRecord:
DoCmd.RunCommand acCmdSaveRecord
Exit_cboAgentID2_AfterUpdate:
Exit Sub
Err_cboAgentID2_AfterUpdate:
If Err.Number = 3022 Then
Forms!frmVisits!fldVisitID = 0
GetVisitID
Forms!frmVisits!fldVisitID = lngVisitID
GoTo SaveRecord
Else
MsgBox Err.Description
Resume Exit_cboAgentID2_AfterUpdate
End If
End Sub
Public Sub GetVisitID()
Dim dbs As Database
Dim rst As Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblVisitDetails", dbOpenDynaset)
rst.MoveLast
lngVisitID = rst!fldVisitID + 1
rst.Close
Set rst = Nothing
End Sub