CODEWITHSUNDEEP
ms-accessvba
Xor
Xor

Reputation: 1

Access - Duplicate Record button creates a blank record

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

Upvotes: 0

Views: 2888

Answers (3)

Gustav
Gustav

Reputation: 55841

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

Upvotes: 1

dbmitch
dbmitch

Reputation: 5386

No error message?

Do you have a Primary Key ID field on the form that doesn't allow you to copy duplicate ID?

Upvotes: 0

Dave B
Dave B

Reputation: 659

I think the easiest way may to create an append query. Set the criteria field as the ID of the current record. This can be done pretty easily in the query design window.

Upvotes: 0

Related Questions