asked on

Copying Record from Continous Subform

I have a continous subform (see screenshot), when I click the "Create" button I want it to copy all the data on that record and copy into a new table. This is the code I'm using:

Dim stDocName As String
Dim rs As dao.Recordset

Set rs = CurrentDb.OpenRecordset("tblSE")

rs.AddNew
rs!intSEID = Me.txtVSENum.Value
rs!intIncidentID = Me.intIncidentID.Value
rs!intPeopleID = Me.intPeopleID.Value
rs!txtFirstName = Me.txtFirstName.Value
rs!txtMiddleName = Me.txtMiddleName.Value
rs!txtLastName = Me.txtLastName.Value
rs.Update

rs.Close

As long as no other record on the continous subform has ever called this before... it works. But if I call it twice (on different records), it won't copy the second time even though it's all new data. I get this error:

The changes you requested to the table were not successful because they would create duplicate values in the index, primary key or relationship. Change the data in the field or fields that contain duplicate data, remove the index or redefine the primary to permit duplicate entries.

See sample, I was able to add Complaint #8 no problem, go to create Complaint #5 and I get the error and it doesn't create the copy in the tblSE

I've stepped through the code and know that it's a unique ID I'm trying to pass.

I also removed the primary key on this table to allow duplicates as a test, and I still get this error.

Could it be an issue with me using a continious form?
ee-subform-.jpg

Donald Maloney

Each time you hit create it looks as if your routine actually resets the recordset to the original input and then tries to rewrite the first record again.
My code to do this is to set a bgookmark when you go into the code and then check the record info.

This is an example that works for my ffiles.

Private Sub EventDelete1_Click()

Me.RecordsetClone.Bookmark = Me.Bookmark

Me.EventDelete = True
Me.Dirty = False
Me.Refresh
Me.Parent![Events By Customer Subform].Form.Recalc

If (MsgBox("do you want to delete this record?", vbYesNo, "Delete Record")) = vbYes Then
If vbYes = MsgBox("Are you sure you want to delete this record?" & vbCrLf & vbCrLf & _
" Event = " & DLookup("EventName", "Events", "[EventID] = " & EventTypeID) & vbCrLf & _
" Location = " & DLookup("LocationName", "Locations", "[LocationID] = " & Nz(EventLocationID, 0)) & vbCrLf, _
vbYesNo + vbDefaultButton2 + vbExclamation, _
" Please Confirm") Then
DoCmd.SetWarnings False
DoCmd.RunSQL "Delete * From [ContractEvents] Where [ContractID] = " & Me.[ContractID] & " And [EventTypeID] = " & Me.[EventTypeID] & " And [EventLocationID] = " & Me.[EventLocationID]
DoCmd.SetWarnings True
Me.Requery
Else
Me.EventDelete = False
Me.EventDelete1.Requery
End If
Else
Me.EventDelete = False
Me.EventDelete1.Requery
End If

End Sub

Eric Sherman

Can you upload a sample of your db???

ET

gcgcit

ASKER

donaldmaloney: how do I incorporate your bookmark code into mine? this is what i have... not sure all the lines i'll need to make it work

Dim stDocName As String
Dim rs As dao.Recordset

Set rs = CurrentDb.OpenRecordset("tblSE")

rs.AddNew
rs!intSEID = Me.txtVSENum.Value
rs!intIncidentID = Me.intIncidentID.Value
rs!intPeopleID = Me.intPeopleID.Value
rs!txtFirstName = Me.txtFirstName.Value
rs!txtMiddleName = Me.txtMiddleName.Value
rs!txtLastName = Me.txtLastName.Value
rs.Update

rs.Close

gcgcit

ASKER

I tried putting

Me.RecordsetClone.Bookmark = Me.Bookmark

directly after my variable declarations but it's still coming up with error.

ASKER CERTIFIED SOLUTION

Donald Maloney

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

gcgcit

ASKER

Your a genius Donald Maloney!!! One of my fields was set to Duplicates No... BY MISTAKE!!! Thank you thank you thank you!!!

Donald Maloney

qcqcit,
Just glad I could help.
but better is that your app works.
Don