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