duplicate records on 2 different forms

[Alan]

Hi folks,

Ok, I've searched this all over and have seen many examples of how to
duplicate the info on a current form including the subforms attached to it.
I've adapted some code from Allen Browne's website on duplicating records and
it works great on the current form open HOWEVER, the problem I'm running into
is that from this main form you're on, there is another form that's connected
to it but with a different PK than the current form.

Essentially this is a quote that is being created but it has two parts. The
main form creates the quote# (PK), associates the customer to the quote
through the quote# (there can be multiple customers for one quote) and for
each quote, there can be multiple packages so again, associated through the
quote#. The duplicate record function works great on this main form because
the quote# is the driving connection between the main form and the two
subforms however it's the individual package configurations I'm having a
problem with. Once the package# is created, the configuration of that package
is based off that package# (PK) so the quote# is no longer a factor here but
it's still part of this quote.

Here's the code I've adapted to do the duplicate function of the main form:

Private Sub cmdDupe_Click()
'On Error GoTo Err_Handler
'Purpose: Duplicate the main Quote Header form record and related
records in the Customer & Package subforms.
Dim strSql1 As String 'SQL statement for customer subform.
Dim strSql2 As String 'SQL statement for quote package subform.
Dim newQuoteID As Long 'Primary key value of the new Quote Header
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
!branch = Me.branch
!Salesman = Me.Salesman
!market = Me.market
!description = Me.description
'etc for other fields.
.Update

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

'Duplicate the related Quote Customer records: append query.
If Me.[sbfmQuoteCustomer].Form.RecordsetClone.RecordCount > 0 Then
strSql1 = "INSERT INTO [tblQuoteCustomer] ( quoteID,
customer, endUser, location, primary ) " & _
"SELECT " & newQuoteID & " As NewID, customer, endUser,
location, primary " & _
"FROM [tblQuoteCustomer] WHERE quoteID = " & Me.quoteID
& ";"
DBEngine(0)(0).Execute strSql1, dbFailOnError
Else
MsgBox "Main record duplicated, but there were no related
records."
End If

'Duplicate the related Quote Package records: append query.
If Me.[sbfmQuotePkgHeader].Form.RecordsetClone.RecordCount > 0
Then
strSql2 = "INSERT INTO [tblQuotePkgHeader] ( quoteID, pkgNo,
pkgDesc, pkgType ) " & _
"SELECT " & newQuoteID & " As NewID, pkgNo, pkgDesc,
pkgType " & _
"FROM [tblQuotePkgHeader] WHERE quoteID = " & Me.quoteID
& ";"
DBEngine(0)(0).Execute strSql2, 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

Essentially I want this button to not only copy the main form and subforms
on it but also the associated form containg the configuration of each package.

Any help/suggestions would be greatly appreciated!

Thanks,

Alan