Solved

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

Posted on 2008-06-11
2
272 Views
Last Modified: 2013-11-27
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
Comment
Question by:crossfire04
2 Comments
 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 250 total points
ID: 21762985
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
 

Accepted Solution

by:
crossfire04 earned 0 total points
ID: 21763026
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now