Copy record in form and subform to a new record.

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
Fred FisherPhotographerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
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.
0
Fred FisherPhotographerAuthor 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
0
Fred FisherPhotographerAuthor 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:
0
Determine the Perfect Price for Your IT Services

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

Fred FisherPhotographerAuthor 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?
0
Gustav BrockCIOCommented:
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

0
Fred FisherPhotographerAuthor 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
0
Gustav BrockCIOCommented:
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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Fred FisherPhotographerAuthor 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.
0
Gustav BrockCIOCommented:
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.
0
Fred FisherPhotographerAuthor 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
0
Gustav BrockCIOCommented:
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.
0
Fred FisherPhotographerAuthor 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.
0
Gustav BrockCIOCommented:
You are welcome!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.