The usual method for getting the value of the identity column is to do a query that gives you the maximum value of the identity right after you add the record. (In the Access engine, the value of the identity is set as soon as you begin an insert operation, but if you link to SQL Server for the table, it isn't set until you actually save the record, so we use the maximum value in the table, as most of our back-ends are SQL Server.) Here's some sample DAO code using that technique:

'Triple check warning

If Me.tabMain = 1 Then

If MsgBox("Are you sure you want to add this person to the system even thought this could be a duplicate?", vbCritical + vbYesNo + vbDefaultButton2, "Possible Duplicate") = vbNo Then

Exit Function

End If

End If

'SQL Change

Set rsAdd = CurrentDb.OpenRecordset("SELECT * FROM tblPeople WHERE lngPersonID=-1", dbOpenDynaset, dbSeeChanges + dbAppendOnly)

rsAdd.AddNew

rsAdd!strLastName = Me.strMemberLastName

rsAdd!strFirstName = Me.strMemberFirstName

rsAdd!strMiddleName = Me.strMemberMiddleName

rsAdd!strSuffix = Me.strMemberSuffix

rsAdd.Update

lngNewPersonID = DMax("lngPersonID", "tblPeople", "strLastName = '" & Me.strMemberLastName & "' and strFirstName = '" & Me.strMemberFirstName & "'")

Set rsAdd = CurrentDb.OpenRecordset("SELECT * FROM tblMembers WHERE lngMemberNumber=-1", dbOpenDynaset, dbSeeChanges + dbAppendOnly)

rsAdd.AddNew

rsAdd!lngMemberNumber = lngNewPersonID

rsAdd.Update

Note the cautionary warning prompt at the beginning - you might want that sort of thing to prevent accidental button clicks. Also note the use of the -1 in the criteria of the SELECT statements - that ensures that it will be an empty recordset when you start the add.

Working on a database that someone else designed is always a challenge - hope this helps.