copying a record

[shaunacol]

I have a form which has several subforms within it. I am trying to copy the record in the main and subforms and then post a duplicate record. This is because the record contains details of attractions and events and often there are new similar attractions and events and it will prevent the admin having to reenter the same details. I read up and found some code:

Private Sub Copy_Click()


DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.GoToRecord , , acNewRec
DoCmd.RunCommand acCmdPaste
End Sub

This seems to do the copy and paste but then when I go to change the primary key (SKU) or any field it says "Update or CancelUpdate wthout AddNew or Edit" and I don't know what that means. I can only assume it is freaking out because of duplicate primary keys. My coding is not good enough to create code that updates the primary key. I tried adding the code below which is something I use elsewhere in the database but it says I am missing a = so either my syntax is wrong or there is another problem.

Private Sub Copy_Click()
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.GoToRecord , , acNewRec
DoCmd.RunCommand acCmdPaste
docmd.SetProperty ([sku],acPropertyValue,Left$(DLast("[SKU]","tbl_attraction"),1) & Format$(Val(Right$(DLast("[SKU]","tbl_attraction"),3)+1),"000"))
End Sub

So....im not sure this is the best way to do what I want to do. If it is then is this error because of the primary key or maybe because of the subforms? Any help appreciated.

[alansidman]

Perhaps this:

Private Sub copyrecordbutton_Click()

On Error GoTo Err_copyrecordbutton_Click

Dim txtOld1 As Variant

Dim txtOld2 As Variant

Dim txtOld3 As Variant

Dim txtOld4 As Variant



txtOld1 = txtcurrent1.Value

txtOld2 = txtcurrent2.Value

txtOld3 = txtcurrent3.Value

txtOld4 = txtcurrent4.Value



RunCommand acCmdRecordsGoToNew



txtnew1.Value = txtOld1

txtnew2.Value = txtOld2

txtnew3.Value = txtOld3

txtnew4.Value = txtOld4





Exit_copyrecordbutton_Click:

    Exit Sub



Err_copyrecordbutton_Click:

    MsgBox Err.Description

    Resume Exit_copyrecordbutton_Click



End Sub

[shaunacol]

Thanks so much for your reply. Is that the only way to do it as I have over 100 fields within the form and subforms. Thank you.

[Missinglinq]

Allen Browne has a tutorial for doing just that:

Duplicate the Record in Form and Subform

Linq ;0)>

[NTC]

Definitely you cannot have duplicate primary keys. Generally the primary key should be an auto number field, not necessarily seen by any users. When you go to duplicate a record, one leaves the key field out because it is going to auto generate by the table as part of the creation of the new record. So if you are using an auto number field - just remove that key field from your append query.

If you are using a self generated primary key then you must create that key in a calculated field and include it in the append query.

[Missinglinq]

And if you use the code given in Post #1, to do the copying, the Access Gnomes automatically know to not copy the AutoNumber/PK Field.

Linq ;0)>

[shaunacol]

Sorry I am not an experienced programmer so am trying to find the easiest solution without too much code. I have more than 100 fields in the form and subforms. I would rather not have to list all those out and then have issues because im not properly referencing (which always happens to me). If this is the simplest solution then of course I will have to do that. But is there no way to do a simple cut and paste? Its for the boss to use, not end users, so I have even tried opening a query with that record in so he can manually cut and paste but the primary key issue gets in the way. Then I tried an append query but couldn't figure that one out either. I have, however, figured out the reason I get "Update or CancelUpdate wthout AddNew or Edit" error, it is because my form is filtered. Going to keep trying tomorrow but if anyone has any other insights they are greatly appreciated thanks