Command button - copy record

[laurak]

I am trying to copy a record in a table using a command
button on a form. The table has a primary key that is
auto numbered. When I use the button to copy and paste a
record, it creates a Paste Errors table. I believe it is
because of the auto number primary key. The vb code is as
follows:

Private Sub Copy_Record_Click()
On Error GoTo Err_Copy_Record_Click


DoCmd.DoMenuItem acFormBar, acEditMenu, 8, ,
acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, ,
acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, ,
acMenuVer70 'Paste Append

Exit_Copy_Record_Click:
Exit Sub

Err_Copy_Record_Click:
MsgBox Err.Description
Resume Exit_Copy_Record_Click

End Sub

Can anyone help? Thanks so much!!

 

[Sandra Daigle]

Hi LauraK,

The method you are attempting to use relys on the old Docmd.DoMenuItem
methods. These methods are best avoided since they are a bit cryptic and
since there are other ways of doing the same thing without involving the
User Interface.

Here is some sample code that uses the recordsetclone of the form to make a
copy of the current record - note that in this example (built on the
Northwind Employee form), the Employeeid field is an autonum field and is
skipped, also, at least one of the other Unique Key fields is amended with
the string "Copy". This makes the copy stand out, but it also allows you to
save the record without errors on duplicate key values.

Private Sub Command34_Click()
Dim fld As Field
With Me.RecordsetClone
.Bookmark = Me.Bookmark
Me.Recordset.AddNew
For Each fld In .Fields
'skip the PK field
If fld.Name <> "EmployeeID" Then
Me.Recordset.Fields(fld.Name) = fld.Value
'amend the value in one of the unique index fields
If fld.Name = "LastName" Then
Me.Recordset.Fields(fld.Name) = fld.Value & " Copy"
End If
End If
Next fld
Me.Recordset.Update
End With
set fld=nothing

End Sub