Identity in Multi User Setting???

[mchris]

@@Identity in Multi User Setting???

I have the following code attached to a submit button on a form. When clicked it updates the entered information to tblContacts. It then returns the primary key, lngContactID, to the text field txtCID. I am having trouble finding out if this will work in a multi-user environment.

Any suggestions or alternatives??

Private Sub cmdSubmit_Click()
On Error GoTo Err_cmdSubmit_Click

Dim strSQL As String
Dim rs As ADODB.Recordset
Dim LastAddedContactID As Variant

strSQL = "INSERT INTO tblContacts (chrFirstName, chrLastname, chrAddress1, chrAddress2, chrCity, chrState, chrZipCode, chrPhoneNumber) "
strSQL = strSQL & "VALUES('"
strSQL = strSQL & Me.txtFirstName.Value & "',"
strSQL = strSQL & "'" & Me.txtLastName.Value & "',"
strSQL = strSQL & "'" & Me.txtAddress1.Value & "',"
strSQL = strSQL & "'" & Me.txtAddress2.Value & "',"
strSQL = strSQL & "'" & Me.txtCity.Value & "',"
strSQL = strSQL & "'" & Me.txtState.Value & "',"
strSQL = strSQL & "'" & Me.txtZipCode.Value & "',"
strSQL = strSQL & "'" & Me.txtPhoneNumber.Value & "')"

CurrentProject.Connection.Execute strSQL
Set rs = CurrentProject.Connection.Execute("select @@identity")
LastAddedContactID = rs.Fields(0).Value
Me.txtCID.Value = LastAddedContactID
rs.Close
Set rs = Nothing

Me.cboVDN.SetFocus
Me.cmdSubmit.Visible = False
Me.cmdClear.Visible = False
Me.cmdEdit.Visible = True


Exit_cmdSubmit_Click:
    Exit Sub

Err_cmdSubmit_Click:
    MsgBox Err.Description
    Me.txtFirstName.SetFocus
    Resume Exit_cmdSubmit_Click

End Sub

Thanks. This site is awesome and always proves a trusty resource.

Matt