Solved

Duplicate a Record and retrive ID

Posted on 2011-09-20
4
325 Views
Last Modified: 2012-05-12
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
Comment
Question by:APD_Toronto
  • 2
4 Comments
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 250 total points
Comment Utility
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
 

Author Comment

by:APD_Toronto
Comment Utility
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
 

Expert Comment

by:Gene-Math
Comment Utility
   
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
 

Assisted Solution

by:Gene-Math
Gene-Math earned 250 total points
Comment Utility
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

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

744 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

11 Experts available now in Live!

Get 1:1 Help Now