Duplicate records in Subform to New record
<p>I have a table for my assemblies in a manufacturing process <code>[Shedmodels]</code>. The components are listed in a separate table <code>[ShedModelsComponents]</code>. The primary key in <code>[ShedModels]</code> is <code>[ModelNumber]</code>. There is a field in <code>[ShedModelsComponents]</code> also called <code>[ModelNumber]</code>. Each component is thus assigned to a certain Assembly in table <code>[ShedModels]</code>. Next I have created a form for <code>[ShedModels]</code> with a subform for <code>[ShedModelsComponents]</code> embedded. All the components for the assembly appear like I want. So far, so good. Now many of my assemblies use almost the same components, so I would like to copy or perhaps append all the components from one assembly to a new record in <code>[Shed Models]</code>. I have found this code on MS website.</p>

<pre><code>Private Sub btnDuplicate_Click()
Dim dbs As DAO.Database, Rst As DAO.Recordset
Dim F As Form

' Return Database variable pointing to current database.
Set dbs = CurrentDb
Set Rst = Me.RecordsetClone

On Error GoTo Err_btnDuplicate_Click

' Tag property to be used later by the append query.
Me.Tag = Me![ModelNumber]

' Add new record to end of Recordset object.
With Rst
   .AddNew
      !ModelNumber = Me!ModelNumber
      !ModelDesc = Me!ModelDesc
      !ModelSalePrice = Me!ModelSalePrice
   .Update                     ' Save changes.
   .Move 0, .LastModified
End With
Me.Bookmark = Rst.Bookmark

' Run the Duplicate Order Details append query which selects all
' detail records that have the OrderID stored in the form's
' Tag property and appends them back to the detail table with
' the OrderID of the duplicated main form record.

DoCmd.SetWarnings False
DoCmd.OpenQuery "Duplicate Shed Models Components"
DoCmd.SetWarnings True

'Requery the subform to display the newly appended records.
Me![Shed_Models_Query].Requery

Exit_btnduplicate_Click:
Exit Sub

Err_btnDuplicate_Click:
MsgBox Error$
Resume Exit_btnduplicate_Click:

End Sub
</code></pre>

<p>but it returns the error that this would create duplicate Model numbers, which I don't doubt. How can I copy my assembly with all the components to a new record, but change the Model Number (which would be user entered)?</p>