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
APD TorontoAsked:
Who is Participating?
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor 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-MathConnect With a Mentor Commented:
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
All Courses

From novice to tech pro — start learning today.