Duplicate records in Subform to New record

Question

I have a table for my assemblies in a manufacturing process [Shedmodels]. The components are listed in a separate table [ShedModelsComponents]. The primary key in [ShedModels] is [ModelNumber]. There is a field in [ShedModelsComponents] also called [ModelNumber]. Each component is thus assigned to a certain Assembly in table [ShedModels]. Next I have created a form for [ShedModels] with a subform for [ShedModelsComponents] 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 [Shed Models]. I have found this code on MS website.

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

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)?

score 0 · Answer 1 · answered Jun 03 '16 at 15:56

First, copy the parent record (like you do). But don't let the form move to the new record.

Next, obtain the new PK.

Then, copy the child records using the new PK. This is a skeleton - you would use the RecordsetClone of the subform. See below.

Finally, move the parent form to the new record. The subform will automatically update.

Public Sub CopyRecords()

  Dim rstSource   As DAO.Recordset
  Dim rstInsert   As DAO.Recordset
  Dim fld         As DAO.Field
  Dim strSQL      As String
  Dim lngLoop     As Long
  Dim lngCount    As Long

  strSQL = "SELECT * FROM tblStatus WHERE Location = '" & _
                "DEFx" & "' Order by Total"

  ' Change this to the RecordsetClone of the subform.
  Set rstInsert = CurrentDb.OpenRecordset(strSQL)  

  Set rstSource = rstInsert.Clone
  With rstSource
    lngCount = .RecordCount
    For lngLoop = 1 To lngCount
      With rstInsert
        .AddNew
          For Each fld In rstSource.Fields
            With fld
              If .Attributes And dbAutoIncrField Then
                ' Skip Autonumber or GUID field.
              ElseIf .Name = "ParentID"  ' Name of FK.
                  rstInsert.Fields(.Name).Value = NewID  ' The new ID of the parent.
              ElseIf .Name = "Total" Then
                ' Insert some default value.
                rstInsert.Fields(.Name).Value = 0
              ElseIf .Name = "PROCESSED_IND" Then
                ' Empty a field.
                rstInsert.Fields(.Name).Value = Null
              Else
                ' Copy field content.
                rstInsert.Fields(.Name).Value = .Value
              End If
            End With
          Next
        .Update
      End With
      .MoveNext
    Next
    rstInsert.Close
    .Close
  End With

  Set rstInsert = Nothing
  Set rstSource = Nothing

End Sub

Duplicate records in Subform to New record

1 Answers1

Linked