Improve company productivity with a Business Account.Sign Up

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

Duplicate a Record and retrive ID

Hello Experts,

I have a table with 63 fields, with fldID as Primary Key Auto#, and I'm hoping If any one can tell me how I can:

1) Duplicate a record (repeat order)
2) Retrieve the new fldID

Any help will be much appreciated
0
APD Toronto
Asked:
APD Toronto
  • 2
2 Solutions
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You can try this:

Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
CurrentProject.Connection.Execute "INSERT INTO YourTable (SELECT * FROM YourTAble WHERE YourIDField=YourID)"
rst.Open "SELECT @@IDENTITY AS NewID", CurrentProject.Connection

Msgbox rst("NewID")

Obviously you'd have to change the YourIDField to match your own, and the value of YourID to that of the record you wish to duplicate.
0
 
APD TorontoAuthor Commented:
Can I use the above without the ADODB as I need to deploy the library

Bsides, its Access, we can use

Dim rst As Recordset

and

CurrentDB

Can we combine these 2 instead
0
 
Gene-MathCommented:
   
Dim DB As DAO.Database
    Dim RS As DAO.Recordset
    Dim lNewID As Long
    
    Set DB = CurrentDb
    
    DB.Execute "INSERT INTO tblSource SELECT Fld1, Fld2, Fld3, Fld4, Fld5 FROM tblSource WHERE ID=5;"
    Set RS = DB.OpenRecordset("SELECT @@IDENTITY AS NewID FROM tblSource;")
    
    If RS.RecordCount <> 0 Then
        RS.MoveFirst
        lNewID = RS!NewID
    End If
    
    Debug.Print lNewID

Open in new window

0
 
Gene-MathCommented:
I forgot to add.  You will get an error if your ID(Autonumber) field is in your select field list.

Also don't forget to remove your DB and RS

Set RS = Nothing
Set DB = Nothing

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

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