Copy a record using command button

[Laura]

I am trying to copy and paste a record in a table using a
command button. When I do this, it creates a table
called "Paste Error". I think it is because the table has
a primary key that is auto numbered. Here is the VB
behind the button:

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

line two copies, line three creates a new record, line
three pastes and line four goes to the last record made,
theoretically. But it doesn't work.

Thanks for any help!

 

[Allen Browne]

Presumably this is a command button on a form, to duplicate the current
record.

The 'Paste Error' table might provide info on what went wrong.

Access can usually sort out the AutoNumber issue, but there are several
other problems with the wizard generated code, e.g.:
- Any unbound controls or controls bound to expressions? For example, an
unbound combo that nagivates records will mess up the process.

- Any chance the record could be dirty? If it can't be saved the duplicate
won't work.

Better code, with precise control over which fields are duplicated might
look like this:

If Me.Dirty Then 'Save any changes
Me.Dirty = False
End If
If Me.NewRecord Then 'Check there is a record.
MsgBox "Select a record to duplicate"
Else
With Me.RecordsetClone 'Point the clone set to this record.
.Bookmark = Me.Bookmark
'Go to a new record, and copy from the clone set.
RunCommand acCmdRecordsGotoNew
Me!SomeField = !SomeField
Me!AnotherField = !AnotherField
'etc.
End With
End If