[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 282
  • Last Modified:

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

0
crossfire04
Asked:
crossfire04
2 Solutions
 
Rey Obrero (Capricorn1)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
 
crossfire04Author 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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now