Error When Attempting to Duplicate Subform Record

[Red Rider]

I have created a Access 2002 database that has a main form and a
subform (1 to many). After I run a macro that filters the main form
down to 1 record, with maybe 10 records in the subform, I attempt to
run a separate procedure that tries to create a new record that
duplicates the current subform record. Each time I try this I get an
error saying “Records in that TestDB was unable to paste have been
copied to the “Paste Errors” table.” If I don’t filter the main form
the program runs OK in the subform. I’d like to be able to duplicate
a subform record whether the main form is filtered or not.

Here’s the code:

Private Sub DuplicateRecord_Click()

On Error GoTo Err_DuplicateRecord_Click

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

' old code - caused same error as code above
'DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
'DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
'DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste
Append

Exit_DuplicateRecord_Click:
Exit Sub

Err_DuplicateRecord_Click:
MsgBox Err.Description
Resume Exit_DuplicateRecord_Click

End Sub

Any help would be appreciated.

 

[Allen Browne]

This will take a bit more code, but is much more reliable than the
DoMenuItem approach:
Duplicate the record in form and subform
at:
http://allenbrowne.com/ser-57.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have created a Access 2002 database that has a main form and a
subform (1 to many). After I run a macro that filters the main form
down to 1 record, with maybe 10 records in the subform, I attempt to
run a separate procedure that tries to create a new record that
duplicates the current subform record. Each time I try this I get an
error saying “Records in that TestDB was unable to paste have been
copied to the “Paste Errors” table.” If I don’t filter the main form
the program runs OK in the subform. I’d like to be able to duplicate
a subform record whether the main form is filtered or not.

Here’s the code:

Private Sub DuplicateRecord_Click()

On Error GoTo Err_DuplicateRecord_Click

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

' old code - caused same error as code above
'DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
'DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
'DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste
Append

Exit_DuplicateRecord_Click:
Exit Sub

Err_DuplicateRecord_Click:
MsgBox Err.Description
Resume Exit_DuplicateRecord_Click

End Sub

Any help would be appreciated.

 

[Red Rider]

This will take a bit more code, but is much more reliable than the
DoMenuItem approach:
    Duplicate the record in form and subform
at:
   http://allenbrowne.com/ser-57.html

--
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


I have created a Access 2002 database that has a main form and a
subform (1 to many).  After I run a macro that filters the main form
down to 1 record, with maybe 10 records in the subform, I attempt to
run a separate procedure that tries to create a new record that
duplicates the current subform record.  Each time I try this I get an
error saying “Records in that TestDB was unable to paste have been
copied to the “Paste Errors” table.”  If I don’t filter the main form
the program runs OK in the subform.  I’d like to be able to duplicate
a subform record whether the main form is filtered or not.

Here’s the code:

Private Sub DuplicateRecord_Click()

On Error GoTo Err_DuplicateRecord_Click

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

' old code - caused same error as code above
'DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
'DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
'DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste
Append

Exit_DuplicateRecord_Click:
    Exit Sub

Err_DuplicateRecord_Click:
    MsgBox Err.Description
    Resume Exit_DuplicateRecord_Click

End Sub

Any help would be appreciated.

On that site it says: "The example shows how to duplicate the record
in the main form, and also the related records in the subform."

I don't want to duplicate the Main form record just the current
Subform record. I can manually step through the process of
duplicating a subform record but it doesn't work when VBA does it.