Duplicate Record Button

[Guest]

I added a duplicate record button to a form. When I use it I get the error
prompt:
"Update or CancelUpdate without AddNew or Edit." The help button brings up
this info:

"Update or CancelUpdate without AddNew or Edit. (Error 3020)
On a Microsoft Jet database, you called the Update or CancelUpdate method
but did not use the AddNew or Edit method before writing data to a record.

On an ODBCDirect database, this error occurs when you attempt to write data
to a record without first calling AddNew or Edit."

This is like reading Ancient Greek. Can anyone translate this?

Thanks!

 

[Brendan Reynolds]

We'd need to see the code, John.

 

[Guest]

Thanks, Brendan.

The button code is:
Private Sub cmdduplicatefg_Click()
On Error GoTo Err_cmdduplicatefg_Click


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

Exit_cmdduplicatefg_Click:
Exit Sub

Err_cmdduplicatefg_Click:
MsgBox Err.Description
Resume Exit_cmdduplicatefg_Click

End Sub

 

[Brendan Reynolds]

Urgh! Sorry I asked! It's not your fault, John, but the stupid command
button wizard, which is still using obsolete Access 95 syntax. But I can
only guess what that code is doing. My guess is that probably the first
"DoMenuItem" line is selecting the record, and the second one is copying it.
I can't quite figure out how that would cause that particular error message,
though.

You could try replacing those three DoCmd.DoMenuItem lines with the
following ...

DoCmd.RunCommand acCmdSaveRecord
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.RunCommand acCmdPasteAppend

This works for me with a simple test table and form.

 

[Guest]

Thanks, Brendan!

I changed the code but still get the error. I think the challenge is that
I'm not using a simple form.

My form record source is:
SELECT tblProfiles.*, tblProfiles.Class, tblFinishedGoods.UnitPackCount,
tblFinishedGoods.SubUnitPackCount, tblFinishedGoods.PackSize,
tblFinishedGoods.PackUOM, tblFinishedGoods.PackType, tblFinishedGoods.Claim,
tblFinishedGoods.CountryLanguage, tblFinishedGoods.Brand,
tblFinishedGoods.FlavorStyle, tblFinishedGoods.Category,
tblFinishedGoods.Version, tblFinishedGoods.Life,
tblFinishedGoods.LifetimeUOM, tblFinishedGoods.Storage, tblFinishedGoods.Ti,
tblFinishedGoods.Hi, tblFinishedGoods.AdditionalUnits,
tblFinishedGoods.UnitLoadTotal, tblFinishedGoods.LayerSpread,
tblFinishedGoods.LayerPattern, tblFinishedGoods.UnitLoadStack FROM
tblProfiles INNER JOIN tblFinishedGoods ON tblProfiles.txtProfileID =
tblFinishedGoods.txtProfileID WHERE (((tblProfiles.Class)="MZ"));

There are also 5 other subforms that relate in one-many relationships with
cascading updates and deletes.

Are the subform records perhaps the reason for the error?

 

[Brendan Reynolds]

I would imagine so, John, but I'm not sure what to suggest. This is not
something I've ever tried to do - I'm usually more concerned with
*preventing* duplicate records!

Unless someone else has a better suggestion, the only thing I can think off
is to narrow down the problem by a process of elimination. Try creating a
copy of the form and removing the subforms from the copy. If the code works
with the copy, you'll know that the problem is indeed related to the
presence of the subforms.

 

[Guest]

Good suggestion. I'll give it a whirl.

I'm creating duplicate records because I can have entities that may be
exactly the same except for a couple minor things. I figured it would be
easier to duplicate that record and then change the few minor things.

Is there an easier/better way to do this?

 

[Brendan Reynolds]

I'm not familiar enough with your application to say whether there might be
a better or easier way. Let's see if the code works without the subforms,
and then if it does you could try adding them back one at a time, to see if
the problem is associated with one particular subform.

 

[Guest]

Thanks, Brendan.

I removed all of the subforms but this didn't resolve things. I think
there's an issue with the combo box of the primary key. This has an after
upate event procedure that may be fouling things because I created a new form
with this function removed and the duplication button worked. However, the
button didn't duplicate the related records in the subforms.

Hmmm...I'm stumped.

 

[Brendan Reynolds]

The code doesn't attempt to duplicate the related records, John, so in that
sense it's not failing, it's now doing what it's supposed to do, which is
duplicate the parent record. If you wanted to duplicate the related records
as well, you'd either have to loop through each record in each subform, and
execute similar code for each record, or (and this would probably be much
more efficient) execute a couple of append queries, one for each sub form.

 

[Guest]

Thanks! That sure sounds like an easy solution, however, my skills aren't
quite up to it yet. I'll save this thread on my computer for future reference.

I appreciate all your help!!!