gogetsome
asked on
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!
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!
You can use append query, where both tables are the same table, but in select part ID = ID from your form
ASKER
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you for your assistance. I will take out the related table stuff.