MSAccess Coding - How to get record ID of record just inserted

I have a function that I pass a record id to, this function copies the field values into a new record and does an insert.  I need to capture the new record's ID.  Can you help?
Function addnewrecord(testcasenbr)
 
    Dim db As DAO.Database
    Dim sSQL As String
    Dim rst
    Set db = DBEngine(0)(0)
    
    sSQL = " INSERT into tbl_Testcases " & "(tc_Name, tc_Desc) " & "SELECT tc_Name, tc_Desc " & _
             "FROM tbl_Testcases " & " WHERE tc_Id = " & testcasenbr & ";"
             
    db.Execute sSQL, dbFailOnError
    db.Close
 
End Function

Open in new window

crossfire04Asked:
Who is Participating?
 
crossfire04Connect With a Mentor Author Commented:
it is a multi user and I have been trying that @@identity but can seem to get it right.  How would that be coded given my example?  thanks
0
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
is the record id an autonumber? is this for a single user or multi user application?

if autonumber and single user, you can get the new record id using dmax

newid= dmax("anField","tbl_Testcases")

if multi user check the @@Identity

0
All Courses

From novice to tech pro — start learning today.