Solved

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

Posted on 2008-06-11
2
276 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) 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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

739 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