Duplicate record

Hello, what is the best way to duplicate a record from an existing record? When the record is duplicated it gets a new Id, but the data is the same.

I thought I could pass the existing record id to a bound form, get the next id, then populate the existing data in the new bound form. Crazy?

Thanks for any help!
gogetsomeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

als315Commented:
You can use append query, where both tables are the same table, but in select part ID = ID from your form
0
gogetsomeAuthor Commented:
I found this and thought it might work. Your thoughts?

Private Sub cmdDupe_Click()
'On Error GoTo Err_Handler
    'Purpose:   Duplicate the main form record and related records in the subform.
    Dim strSql As String    'SQL statement.
    Dim lngID As Long       'Primary key value of the new record.
    
    'Save any edits first
    If Me.Dirty Then
        Me.Dirty = False
    End If
    
    'Make sure there is a record to duplicate.
    If Me.NewRecord Then
        MsgBox "Select the record to duplicate."
    Else
        'Duplicate the main record: add to form's clone.
        With Me.RecordsetClone
            .AddNew
                !CustomerID = Me.CustomerID
                !EmployeeID = Me.EmployeeID
                !OrderDate = Date
                'etc for other fields.
            .Update
            
            'Save the primary key value, to use as the foreign key for the related records.
            .Bookmark = .LastModified
            lngID = !OrderID
            
            'Duplicate the related records: append query.
            If Me.[Orders Subform].Form.RecordsetClone.RecordCount > 0 Then
                strSql = "INSERT INTO [Order Details] ( OrderID, ProductID, Quantity, UnitPrice, Discount ) " & _
                    "SELECT " & lngID & " As NewID, ProductID, Quantity, UnitPrice, Discount " & _
                    "FROM [Order Details] WHERE OrderID = " & Me.OrderID & ";"
                DBEngine(0)(0).Execute strSql, dbFailOnError
            Else
                MsgBox "Main record duplicated, but there were no related records."
            End If
            
            'Display the new duplicate.
            Me.Bookmark = .LastModified
        End With
    End If

Exit_Handler:
    Exit Sub

Err_Handler:
    MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdDupe_Click"
    Resume Exit_Handler
End Sub

Open in new window

0
als315Commented:
This is good code, but you need it only when you like to duplicate record not only in main table, but also in related tables. If you have only one table, you can do it with one update query.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gogetsomeAuthor Commented:
Thank you for your assistance. I will take out the related table stuff.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.