Access - Duplicate Record button creates a blank record

Question

I've got a fairly simple database, 5000 records about 60-70 fields, and I created a button to copy the current record and blank out some of the fields (which we called EXPAND).

I recently received a requests to do the same thing without blanking out any fields but it doesn't work. I used the Command Button wizard on my form and chose the Duplicate option, but the new record is completely blank. Additionally, I get an error message when I close the record that talks about "A large amount of data copied to the clipboard". I tried the original button I had made (EXPAND) and it resulted in the same issue. Searching back through old records, I see that it was working as recently as 6/10/2016 (10 days ago).

Has something changed which would prevent this code from executing properly? Is there a new setting/option I need to enable to get it working again? Or is there some alternative method I can use to accomplish the same result?

Here is the (very simple) code the system created to duplicate the record (which doesn't work):

Private Sub cmdDuplicate_Click()

On Error GoTo Err_cmdDuplicate_Click


    DoCmd.RunCommand acCmdSelectRecord
    DoCmd.RunCommand acCmdCopy
    DoCmd.RunCommand acCmdRecordsGoToNew
    DoCmd.RunCommand acCmdSelectRecord
    DoCmd.RunCommand acCmdPaste


Exit_cmdDuplicate_Click:

    Exit Sub

Err_cmdDuplicate_Click:

    MsgBox Err.Description
    Resume Exit_cmdDuplicate_Click

End Sub

What happens if you do that manually? Select record, copy, goto new record, select record, paste. — Andre
Yes... it is a data entry form and sometimes the user needs to have a second record where almost all of the fields are the same as the first. To simplify this, the idea was to just open up a new records with all of the same data and they could just change the 1 or 2 items that were different. This procedure worked just fine in the database until about 2 weeks ago. Now, for some reason, it just creates a new blank record. — Xor
Yes... if I go into the table and click a row (record) and hit copy, then create a new row (record) and hit paste, it works fine. It seems the problem only occurs within the Data Entry form I've created. Everything works except the final "acCmdPaste" bit. The data is still copied because I can go into notepad and hit paste and it shows the contents of the record... it just won't paste it into the form. There have been a bunch of Access updates my computer installed recently; I wonder if they changed/broke something? — Xor
Does this answer your question? Duplicate Record with New Primary Key (VBA) — Shimon Lebovits

Gustav Gustav · Accepted Answer · 2016-06-22T08:38:28

The fastest and simplest way is to use DAO and the RecordsetClone of the form:

Private Sub cmdDuplicate_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.
              ElseIf .Name = "SomeFieldToPreset"
                rstInsert.Fields(.Name).Value = SomeValue
              ElseIf .Name = "SomeFieldToExclude
                ' Leave blank
              Else
                ' All other fields.
                ' 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

Access - Duplicate Record button creates a blank record

3 Answers