duplicate record button

[Guest]

I need to streamline data entry by duplicating pre-existing records. The task
is to create records for finished goods. The finished goods fall into
families of pack configurations like 2/1 GAL; 4/1 GAL; 8/.5 GAL; etc. Each
pack family has shared as wells as unique records.

What I'd like to do is create a "template" record for each pack family and
duplicate them whenever I need to create a new finished good record of the
same pack. For example: I've created "2/1 GAL" including all the shared
records. I then created a "duplicate record" button with the wizard.
Unfortunately, when the button is executed the following is returned:

"Update or CancelUpdate without AddNew or Edit."

The Help button returns this:
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 all foreign to me and I'm at a loss. Any help would certainly be
appreciated!

THANKS in advance!

 

[tina]

i wasn't able to replicate the error you're getting. my wizard-created
command button successfully added a duplicate record - until i put a primary
key in the underlying table. then the record did duplicate successfully, but
could not be saved until the primary key value was changed.

where is the "template" record stored?
where are the "real" records stored?
and please post the code behind the "duplicate" button on the form.

 

[Guest]

Thanks, Tina!

Here's the duplicate button code:

Private Sub cmdduplicaterecord_Click()
On Error GoTo Err_cmdduplicaterecord_Click


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

Exit_cmdduplicaterecord_Click:
Exit Sub

Err_cmdduplicaterecord_Click:
MsgBox Err.Description
Resume Exit_cmdduplicaterecord_Click

End Sub

The template record and the real record are stored in the same table:
tblProfiles

I suspect this is part of the problem.

 

[tina]

hmm, well, that's the same code my wizard created. and it performed for me
as i previously posted, copying one record from a table back into the same
table, as a new record.

what version of Access are you using?
and what version of Access is your database?

 

[Guest]

Access 2002. I designed the database in this version, too.

I posted this problem quite sometime ago and it befuddled this forum then,
too. Is the problem perhaps the many subforms and related tables? For
example, I have a subform with a lot of event codes.

 

[tina]

well, subforms MAY complicate the issue; depends on what you're doing.

are you trying to duplicate the template record *between* forms - say, from
mainform to subform, or vice versa? or from subform to sub-subform, or vice
versa?
is the command button on the same form (or subform) where the record is that
you're trying to duplicate?

 

[Guest]

Thanks, Tina.

The command button is in the header of the form for the record I'm trying to
duplicate.

Here's the table:

tblProfiles
txtProfileID (PK)

Within this table I have a txtProfileID "2/1 GAL." I need to duplicate this
record within the table and rename it.

The 2/1 GAL record has several related records in tables with one to many
relationships. For example, it's related to the following txtProfile ID's:
951602, 600312, and Pallet 48"x40".

 

[tina]

well, none of that info accounts for the error you originally posted. that
error basically says "you opened a Recordset and tried to Update, but you
didn't add or edit a value first, so we can't Update the record."
unfortunately, Access error messages are not always quite appropriate to
what's actually happening.

at this point, the only thing i can suggest is to create an Append query to
add the duplicate record, using references to the form controls as the
source of the append data. if you're using an incremented Autonumber for the
table's primary key, you should be able to requery the form after running
the Update query, and then go to the "last" record - which should be the new
record - and make changes to it.

hth

 

[Guest]

I was afraid you'd say that! This sounds easy enough, however, I'm completely
inexperienced with append queries. Here's what I have as a start. Maybe you
can see something in it that makes the button return that error message:

INSERT INTO tblProfiles ( txtProfileID, Class, Type )
SELECT tblProfiles.txtProfileID, tblProfiles.Class, tblProfiles.Type
FROM (tblProfiles INNER JOIN (((tblFinishedGoods INNER JOIN
tblFGUnitLoadsFinishingAttributes ON tblFinishedGoods.txtProfileID =
tblFGUnitLoadsFinishingAttributes.txtProfileID) INNER JOIN
tblFGUnitLoadsLayerParameters ON tblFinishedGoods.txtProfileID =
tblFGUnitLoadsLayerParameters.txtProfileID) INNER JOIN
tblFGUnitLoadsLayerPatterns ON tblFinishedGoods.txtProfileID =
tblFGUnitLoadsLayerPatterns.txtProfileID) ON tblProfiles.txtProfileID =
tblFinishedGoods.txtProfileID) INNER JOIN tblPKProfilesAssociations ON
tblProfiles.txtProfileID = tblPKProfilesAssociations.txtProfileID;

THANKS!!!

 

[tina]

well, i'm not very good at reading complex SQL statements when i can't refer
back to the underlying tables to "figure it out". <wry grin>

but i think you're making this unnecessarily complicated, at least in the
Append query itself. if your goal is simply to create a copy of the
"current" record in the form, and paste that copy into the form's underlying
table - then just refer to the controls in the form to get the "paste"
values, as

INSERT INTO tblProfiles ( txtProfileID, Class, Type )
SELECT Forms!MyForm!ProfileIDControl, Forms!MyForm!ClassControl,
Forms!MyForm!TypeControl

substitute the correct form name and control names, of course. and remember,
in an Append query, the record is immediately saved - so make sure you're
NOT inserting the primary key value from the current form record. otherwise
you'll get an error on the Append, because you can't save a duplicate value
in the primary key field in the table.

hth