Copy record in form and subform to a new record.

Fred Fisher
Fred Fisher used Ask the Experts™
on
I am using Access 2016.  I am writing a music collection database with two primary tables, tblTitles and tblSongs.  The main form frmTitles is used to enter the information pertaining to the title and that form has a continuous subform subfrmSongs that all of the song information is contained in.  Every now and again I run across a duplicate title that I need to enter into the database.  Rather than rekeying all of the information again I thought I would create a command button on the form btnCopyRecord that would;

1) copy all of the data from the frmTitles (AutoNum Key field TitleID) and subfrmSongs (AutoNum Key field SongID, create a new record and copy the information into the new record with a new unique key for TitleID and SongID.  2) Save the duplicate record with the new unique keys and open that record so that the one or two fields that need to be edited can be edited without having to rekey the whole record.

While searching on the internet for a solution I came across the code listed below.  The problem is when it gets to the line .Update I get an error message Runtime error 3022.  The changes in the table were not successful as they would create duplicate values in the index, primary key or the index.  Change the data in the in the fields that contain the duplicate data, remove the index, or redefine the index to permit duplicated entries and try again.

What am I doing wrong or is there another way to skin this cat?

'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
                !TitleID = Me.TitleID
                !Status = Me.Status
                !MediaType = Me.MediaType
                !RollType = Me.RollType
                !RollOrgin = Me.RollOrigin
                !RecutSource = Me.RecutSource
                !Title = Me.Title
                !TitleNumber = Me.TitleNumber
                !RecordingCompany = Me.RecordingCompany
                !GroupPerformer = Me.GroupPerformer
                !Genre = Me.Genre
                !PurchasedFrom = Me.PurchasedFrom
                !PurchaseDate = Me.PurchaseDate
                !PurchasePrice = Me.PurchasePrice
                !CurrentMarketValue = Me.CurrentMarketValue
                !Condition = Me.Condition
                !RollRating = Me.RollRating
                !DateDigitalFileRecorded = Me.DateDigitalFileRecorded
                !TitleNotes = Me.TitleNotes
                !SoldDate = Me.SoldDate
                !SoldPrice = Me.SoldPrice
                !SoldTo = Me.SoldTo
                !GainLoss = Me.GainLoss
                'etc for other fields.
            .Update
           
            'Save the primary key value, to use as the foreign key for the related records.
            .Bookmark = .LastModified
            lngID = !TitleID
           
            'Duplicate the related records: append query.
            If Me.[subfrmSongs].Form.RecordsetClone.RecordCount > 0 Then
                strSql = "INSERT INTO [subfrmSongs] ( SongID, MediaTitle, SideDiscNumber, SongPosition, SongTitle, SongPlayedBy1, SongPlayedBy2, SongPlayedBy3, SongType, SongLength ) " & _
                    "SELECT " & lngID & " As NewID, SongID, MediaTitle, SideDiscNumber, SongPosition, SongTitle, SongPlayedBy1, SongPlayedBy2, SongPlayedBy3, SongType, SongLength  " & _
                    "FROM [subfrmSongs] WHERE TitleID = " & Me.TitleID & ";"
                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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You should use the RecordsetClone of both the master form and the child form:

Copying one Record into a new Record

Much faster and cleaner, and no update of the form(s) is needed.
Fred FisherPhotographer

Author

Commented:
When I used the following code is stops on the line

               rstInsert.Fields(.Name).Value = .Value

The field that is stopping it is a calculated field (GainLoss) that does not need to be copied.  How do I keep it from copying this field?


Private Sub btnCopy_Click()

  Dim rstSource   As DAO.Recordset
  Dim rstInsert   As DAO.Recordset
  Dim fld         As DAO.Field
 
  If Me.NewRecord = True Then Exit Sub
   
  Set rstInsert = Me.RecordsetClone
  Set rstSource = rstInsert.Clone
  With rstSource
    If .RecordCount > 0 Then
      ' Go to the current record.
      .Bookmark = Me.Bookmark
      With rstInsert
        .AddNew
          For Each fld In rstSource.Fields
            With fld
              If .Attributes And dbAutoIncrField Then
                ' Skip Autonumber or GUID field.
              Else
                ' Copy field content.
                rstInsert.Fields(.Name).Value = .Value
              End If
            End With
          Next
        .Update
        ' Go to the new record and sync form.
        .MoveLast
        Me.Bookmark = .Bookmark
        .Close
      End With
    End If
    .Close
  End With
 
  Set rstInsert = Nothing
  Set rstSource = Nothing
 
End Sub
Fred FisherPhotographer

Author

Commented:
I edited the above code by adding which takes care of the issue that I was having, now I will tackle the subform.

   With fld
                If .Attributes And dbAutoIncrField Then
                  ' Skip Autonumber or GUID field.
                 
                 
                Else
                    If .Name = "GainLoss" Then
                        GoTo Skip
                    End If
                   
                    If .Name = "TitleImage" Then
                         GoTo Skip
                    Else

                      ' Copy field content.
                       rstInsert.Fields(.Name).Value = .Value
                     End If
                End If

             
            End With
Skip:
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Fred FisherPhotographer

Author

Commented:
I am confused as to where I put the code so that it will trigger adding the subform data when the copy record button is pressed on the main form.  What am I missing?
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
As soon as you have the new foreign key, call the CopyRecords function to copy the child records. Then sync the main form to the new parent record:

       .MoveLast

        ' Copy childrecords.
        CopyRecords lngNewFK

        Me.Bookmark = .Bookmark

Open in new window

Fred FisherPhotographer

Author

Commented:
No joy, still only getting main form to copy.  Below is what I have:  

Public Sub CopyRecords(ByVal lngNewFK As Long)
 
  Dim rstSource   As DAO.Recordset
  Dim rstInsert   As DAO.Recordset
  Dim fld         As DAO.Field
  Dim lngLoop     As Long
  Dim lngCount    As Long

  Set rstSource = Me!subfrmSongs.Form.RecordsetClone
  Set rstInsert = rstSource.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 = "FK" Then
                ' Insert default new foreign key from copy of new master record.
                rstInsert.Fields(.Name).Value = lngNewFK
              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

AND

Private Sub btnCopyRecord_Click()
 
  Dim rstSource   As DAO.Recordset
  Dim rstInsert   As DAO.Recordset
  Dim fld         As DAO.Field
 
  If Me.NewRecord = True Then Exit Sub
   
  Set rstInsert = Me.RecordsetClone
  Set rstSource = rstInsert.Clone
  With rstSource
    If .RecordCount > 0 Then
      ' Go to the current record.
      .Bookmark = Me.Bookmark
      With rstInsert
        .AddNew
          For Each fld In rstSource.Fields
            With fld
                If .Attributes And dbAutoIncrField Then
                  ' Skip Autonumber or GUID field.
                 Else
                    If .Name = "GainLoss" Then
                        GoTo Skip
                    End If
                    If .Name = "TitleImage" Then
                         GoTo Skip
                    Else

                      ' Copy field content.
                       rstInsert.Fields(.Name).Value = .Value
                     End If
                End If
            End With
Skip:
         Next
        .Update

        .MoveLast
        CopyRecords lngNewFK

        Me.Bookmark = .Bookmark
             
       End With
    End If
    .Close
  End With
 
  Set rstInsert = Nothing
  Set rstSource = Nothing
 
End Sub
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
This code - the parent form code-behind module - is tested and works:

Option Compare Database
Option Explicit

Private Sub CopyButton_Click()

    Dim rst         As DAO.Recordset
    Dim rstAdd      As DAO.Recordset
    Dim fld         As DAO.Field
    Dim Count       As Integer
    Dim Item        As Integer
    Dim Bookmark    As Variant
    Dim NewId       As Long
    
    ' Copy parent record.
    Set rstAdd = Me.RecordsetClone
    Set rst = rstAdd.Clone
    
    ' Move to current record.
    rst.Bookmark = Me.Bookmark
    With rstAdd
        .AddNew
        For Each fld In .Fields
            With fld
                If .Attributes And dbAutoIncrField Then
                    ' Skip Autonumber or GUID field.
                Else
                    ' Copy field.
                    .Value = rst.Fields(.Name).Value
                End If
            End With
        Next
        .Update
        ' Pick Id of the new record.
        .MoveLast
        NewId = !Id.Value
    End With
    ' Store location of new record.
    Bookmark = rstAdd.Bookmark
    
    ' Copy child records.
    Set rstAdd = Me!subChild.Form.RecordsetClone
    Set rst = rstAdd.Clone

    Count = rstAdd.RecordCount
    For Item = 1 To Count
        With rstAdd
            .AddNew
            For Each fld In .Fields
                With fld
                    If .Attributes And dbAutoIncrField Then
                        ' Skip Autonumber or GUID field.
                    ElseIf .Name = "FK" Then
                        ' Master/child field. Skip old FK, use new FK.
                        .Value = NewId
                    ElseIf .Name = "Notat" Then
                        ' Ignore/skip this field.
                    Else
                        .Value = rst.Fields(.Name).Value
                    End If
                End With
            Next
            .Update
        End With
        rst.MoveNext
    Next
    rst.Close
    rstAdd.Close
    
    ' Move to the new recordcopy.
    Me.Bookmark = Bookmark
    
    Set fld = Nothing
    Set rstAdd = Nothing
    Set rst = Nothing

End Sub

Private Sub Form_Current()

    Me!CopyButton.Enabled = Not Me.NewRecord
    
End Sub

Open in new window

See the attached demo, please.
CopyParentChild.accdb
Fred FisherPhotographer

Author

Commented:
I tried it but now it copies ALL of the records (700 of them) in the main table tblTitles and none  of the records in the subform.  I was trying to copy just the current record in the main form frmTitles and the corresponding records in the subform subfrmSongs.
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Can't tell why.
As you can see, my demo works as intended, so build your form to follow the behaviour of my demo, and it will work.
Fred FisherPhotographer

Author

Commented:
This is what is happening when the copy button is pressed.  The tblTitles information on frmTitles is added to a new record, I set a watch variable in the code and confirmed this (NewID = !TitleID.Value.  

The only issue now is that the NewID is lost and the tblSongs information on subfrmSongs is copied to the original TitleID not the new one.  What is happening?  I am guessing the answer is in the code below since that is what loops through the subform records for the original TitleID and then copies it to the NewID.

       .MoveLast
        NewId = !TitleID.Value
               
    End With
    ' Store location of new record.
    Bookmark = rstAdd.Bookmark
   
    ' Copy child records.
    Set rstAdd = Me!subfrmSongs.Form.RecordsetClone
    Set rst = rstAdd.Clone

    Count = rstAdd.RecordCount
    For Item = 1 To Count
        With rstAdd
            .AddNew
            For Each fld In .Fields
                With fld
                    If .Attributes And dbAutoIncrField Then
                        ' Skip Autonumber or GUID field.
                    ElseIf .Name = "FK" Then
                        ' Master/child field. Skip old FK, use new FK.
                        .Value = NewId
                '    ElseIf .Name = "Notat" Then
                        ' Ignore/skip this field.
                    Else
                        .Value = rst.Fields(.Name).Value
                    End If
                End With
            Next
            .Update
        End With
        rst.MoveNext
    Next
    rst.Close
    rstAdd.Close
   
    ' Move to the new recordcopy.
    Me.Bookmark = Bookmark
   
    Set fld = Nothing
    Set rstAdd = Nothing
    Set rst = Nothing
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
But do you have a field named FK? That's from my demo.
You must adjust those field names spelled out to match those of yours.
Fred FisherPhotographer

Author

Commented:
Finally sorted it out.  I was getting confused because both ID columns in your sample DB were "ID".  Now it is working properly.  Thanks for your patience!!!!  I am like a dog with a bone on something like this, the syntax etc is slowly coming back.
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You are welcome!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial