 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms Programming / July 2005duplicate record button
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!  Signaturewww.Marzetti.com 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. > 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 [quoted text clipped - 20 lines] > > THANKS in advance! 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. Signaturewww.Marzetti.com > 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 [quoted text clipped - 32 lines] > > > > THANKS in advance! 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? > Thanks, Tina! > [quoted text clipped - 57 lines] > > > > > > THANKS in advance! 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. Signaturewww.Marzetti.com > 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 [quoted text clipped - 75 lines] > > > > > > > > THANKS in advance! 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? > Access 2002. I designed the database in this version, too. > [quoted text clipped - 81 lines] > > > > > > > > > > THANKS in advance! 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". Signaturewww.Marzetti.com > well, subforms MAY complicate the issue; depends on what you're doing. > [quoted text clipped - 103 lines] > > > > > > > > > > > > THANKS in advance! 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 > Thanks, Tina. > [quoted text clipped - 120 lines] > > > > > > > > > > > > > > THANKS in advance! 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!!! Signaturewww.Marzetti.com > 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 [quoted text clipped - 153 lines] > > > > > > > > > > > > > > > > THANKS in advance! 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 > 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 [quoted text clipped - 171 lines] > > > > > > > > > > > > > > > > > > THANKS in advance! |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.