NEW record - Using DMax

[darinmc]

Hi
I am already using this code to create a new record. This works fine BUT if 2 people try create a record at the same time, an error occurs as the EmpRegNo is the same...

Private Sub cmdNewRec_Click()
Dim VarEmpReg As Integer
VarEmpReg = Nz(DMax("[EmpRegNo]", "tblEmployee")) + 1
DoCmd.GoToRecord , , acNewRec
Me.EmpRegNo = VarEmpReg
Me.EmpRegDate.SetFocus
Forms!frmEmpRead!EmpMemoChanges = "NEW EMPLOYEE STARTED BY - " & Forms!frmSwitchboard!tName & " - " & Now()
End Sub

How would i check if record is in the EDIt state OR how do I work round this???

Thx
Darin

 

[Remou]

Have a look at this:

http://www.tek-tips.com/faqs.cfm?fid=184

 

[JoeAtWork]

I keep the "next" number in a separate table. When I need to assign it to a new record, I grab it and then immediately update the "next" number.

The referenced FAQ does basically the same except takes it one step further by locking the record until the update is complete.