How do you Copy a record on a Form

[Ron]

Hi there,

I'm trying to create a Copy record button on my form but
am not having any luck doing it.
I've tried to use the "Duplicate Record" function in the
Command Button wizard but it's giving me the "Action or
command PasteAppend is not available now."

The form [frmDmJobInfo] is controlled by the query
[qryDmJobTracking]which is comprised of a table & query
[tblDmJobTracking] & [qryDmJobInfo](this query is a combo
of a table [tblDmJobInfo]and another query).

The basic code below is the one you get from the wizard
for the Duplicate Record command button: (I know this code
isn't right for what I need to do, but I'm not sure how to
construct one that will make a copy of the record on that
form [frmDmJobInfo] and paste append it to the proper
tables [tblDmJobTracking] & [tblDmJobInfo] (Both of these
tables have an Autonumber field in them).
Any help would be very much appreciated.

Thanks,
Ron
--------------------------------------------------
Private Sub cmdCopyDMrecord_Click()
On Error GoTo Err_cmdCopyDMrecord_Click


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

Exit_cmdCopyDMrecord_Click:
Exit Sub

Err_cmdCopyDMrecord_Click:
MsgBox Err.Description
Resume Exit_cmdCopyDMrecord_Click

End Sub

 

[NIck]

Ron,

Can you add a new record at all using this form? I suspect
that the forms' underlying recordset based on all those
nested queries may not be updateable.

It should be possible (depending on the data and relation
constraints) to at least partially duplicate a row against
those two tables, but I don't think you'll do it via a stack
of nested queries.

Perhaps you could run a query to get just the record you
want to copy (making sure the two autonumber fields are
blank) save it to temporary table(s) then use another query
to just pop that row from the temp to the proper ones.

Although I generally do things like this in code I almost
always do a sort of prototype with sets of queries first to
make sure that the principle is OK. Plus it writes a lot of
the SQL for you...

Good luck
Nick Coe (UK)