Duplicate Subform and SubSubForm Records but not Main

[Wayne]

I’m using Allen Browne's code to duplicate subform and its subform.
The tables are laid out like this:
The One The Many
------------------------------
Table1 ------>Table2
Table2 ------>Table3

Table1 contains information that never or very rarely changes. Table2
is the Invoice table and it has Table3 as a subform, for Invoice
details. I want to duplicate Table2 and Table3 but I keep getting the
following error:

"The changes you requested were not successful because they would
create duplicate values in the index, primary key, or relationship.
Change the data in the field or fields that contain duplicate data,
remove the index, or redefine the index to permit duplicate entries
and try again."

Here’s the code. Any help would be appreciated.

Private Sub cmdDupe_Click()
'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 and 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
!ContractID = Me.ContractID
!InvoiceID = Me.InvoiceID
!Invoice_Type = Me.Invoice_Type
!Billing_Period = Me.Billing_Period
'etc for other fields.
.Update

'Save the primary key value, to use as the foreign key for
the related records.
.Bookmark = .LastModified
lngID = !InvoiceID

'Duplicate the related records: append query.
If Me.[frmInvoiceDetail_1].Form.RecordsetClone.RecordCount

0 Then

strSql = "INSERT INTO [frmInvoiceDetail_1]
( InvoiceDetailID, PeakDate ) " & _
"SELECT " & lngID & " As NewID, InvoiceDetailID,
PeakDate " & _
"FROM [frmInvoiceDetail_1] WHERE OrderID = " &
Me.InvoiceID & ";"
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

 

[John W. Vinson]

I presume that InvoiceID is the Primary Key of Invoice? If so, don't include
it in the duplication: you certainly do NOT want two records with the same
InvoiceID. If it's an Autonumber and you just don't include it in the AddNew
block you'll get a new InvoiceID (with the next available autonumber) and
won't get this error.

Table1 contains information that never or very rarely changes. Table2
is the Invoice table and it has Table3 as a subform, for Invoice
details. I want to duplicate Table2 and Table3 but I keep getting the
following error:

"The changes you requested were not successful because they would
create duplicate values in the index, primary key, or relationship.
Change the data in the field or fields that contain duplicate data,
remove the index, or redefine the index to permit duplicate entries
and try again."

Here’s the code. Any help would be appreciated.

Private Sub cmdDupe_Click()
'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 and 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
!ContractID = Me.ContractID
!InvoiceID = Me.InvoiceID
!Invoice_Type = Me.Invoice_Type
!Billing_Period = Me.Billing_Period
'etc for other fields.
.Update

'Save the primary key value, to use as the foreign key for
the related records.
.Bookmark = .LastModified
lngID = !InvoiceID

'Duplicate the related records: append query.
If Me.[frmInvoiceDetail_1].Form.RecordsetClone.RecordCount

0 Then

strSql = "INSERT INTO [frmInvoiceDetail_1]
( InvoiceDetailID, PeakDate ) " & _
"SELECT " & lngID & " As NewID, InvoiceDetailID,
PeakDate " & _
"FROM [frmInvoiceDetail_1] WHERE OrderID = " &
Me.InvoiceID & ";"
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