Run-Time error '3022': When duplicating a record in a form with subforms.

[imsants]

Hi, I keep getting errors when duplicating a form with multiple subforms. It's at the .Update level of the code. I have read that it may have to do with duplicating the PK which it can't do but I need help figuring out where in the code is the problem. In the end the form will need to duplicate what's on the main form and two subforms, the three other subforms are for new data. I keep having trouble with the primary key and foreign key concept of the code. I have attached the database and added the code. Any help or suggestions are appreciated, Thanks.

Private Sub Form_Load()
'THANK YOU Allen Browne - Microsoft MVP
'On Error GoTo Err_Handler
'Purpose: Duplicate the main form record and related records in the subform.
Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim strSql As String 'SQL statement.
Dim lngID As Long 'Primary key value of the new record.

'Save any edits first
If Me.Dirty Then
Me.Dirty = False


End If

'Make sure there is a record to duplicate.
If Me.NewRecord Then
MsgBox "Select the record to duplicate."
Else

'Duplicate the main record: add to form's clone.
With Me.RecordsetClone
.AddNew
!SITE_ID = Me.SITE_ID
!STREET = Me.STREET
!CITY = Me.CITY
!STATE = Me.STATE
!ZIP = Me.ZIP
!ACTIVE = Me.ACTIVE
!STATUS = Me.STATUS
!COMMERCIAL = Me.COMMERCIAL
!RESIDENTIAL = Me.RESIDENTIAL
!MAINSITE = Me.MAINSITE
.Update

'Set primary key for related records to append.
.Bookmark = .LastModified
lngID = !SYSTEM_ID

'Duplicate the related records: append query.
If Me.frmNewSystem.Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO tblSystem (SYSTEM_ID, ACCOUNT_ID, MAKE, MODEL, TYPE, BRAND, SALESTECH, LEADSOURCE, ORIGIN, INSTLTECH, COMPLETED, PAAMOUNT, WARRYR, WARRMOS) " & _
"SELECT " & lngID & " As NewID, ACCOUNT_ID, MAKE, MODEL, TYPE, BRAND, SALESTECH, LEADSOURCE, ORIGIN, INSTLTECH, COMPLETED, PAAMOUNT, WARRYR, WARRMOS " & _
"FROM tblSystem WHERE SYSTEM_ID = " & Me.SYSTEM_ID & ";"
Debug.Print strSql
db.Execute strSql, dbFailOnError
MsgBox db.RecordsAffected & " subform records were copied"
Else
MsgBox "Main record duplicated, but there were no related records."
End If

'Display the new duplicate.
Me.Bookmark = .LastModified
End With
End If

Exit_Handler:
Set db = Nothing
Set rs = Nothing
Exit Sub

Err_Handler:
MsgBox "Error " & Err.Number & " - " & Err.Description, , "Form_Load"
Resume Exit_Handler

End Sub

[orange]

3022 is typically an attempt to add a duplicate/replicate to a unique record.

What are you trying to do in plain English?

[imsants]

Hi thanks for the reply. Plain English, I want to make a copy of one record and it's related records to edit and save as a new record. The setup is to select the known record by customer ID which is a designated primary key in a format like LEW123 and when it opens in the form, the idea is that it opens a copy of that record so I can edit and make a new record when I save it. The form has multiple subforms and the related records have an autonumber as the primary key. Once open, I will change the customer ID to the new designated primary key, and what ever else needs changed. I wish for the related records to just take on the next available autonumber, either when it opens or saves. I hope this was a good explanation, thanks again.

[orange]

see this tutorial/sample
http://allenbrowne.com/ser-57.html

[imsants]

Thanks for the link, if you look at my original post that is the sample I am working with. I just can't get it to work. Any suggestions on another sample or what concept am I missing here. Thanks.