I personally would add directly to the table rather than the recordsetclone, though Allen is smarter than I am. Try this (though I'm not sure about the form references). Also, your SQL will fail because the fields don't match. You need the ID field; I've added what I think it might be, but you should double check.
Code:
Private Sub btnTRNSFRPrevTenXfr_Click()
'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 'this is where the debug takes me on the invalid reference error I am getting. Also is it 'With' or Set rs = or Set rst? Question is it Me.Recordset.Clone with the period or leave as is?
Set db = CurrentDb
Set rs = db.OpenRecordset("tblSite", dbAppendOnly)
With rs
.AddNew
!SITE_ID = Me.[tblSite].[SITE_ID].Value + 1
!SYSTEM_ID = Me.[tblSite].[SYSTEM_ID]
!ACCOUNT_ID = Me.[tblSite].[ACCOUNT_ID]
!CUSTOMER_ID = Me.[tblSite].[CUSTOMER_ID]
!STREET = [tblSite].[STREET]
!City = [tblSite].[City]
!State = [tblSite].[State]
!zip = [tblSite].[zip]
!ACTIVE = [tblSite].[ACTIVE]
!Status = [tblSite].[Status]
!COMMERCIAL = [tblSite].[COMMERCIAL]
!RESIDENTIAL = [tblSite].[RESIDENTIAL]
!MAINSITE = [tblSite].[MAINSITE]
'etc for other fields.
.Update
'Save the primary key value, to use as the foreign key for the related records.
.Bookmark = .LastModified
lngID = !SITE_ID
'lngID = ![tblSite].[COMPANY_ID] 'this is where I am lost, not sure what needs to reference here, from the code I used from the other thread.
'Duplicate the related records: append query.
If Me.[frmCustomer].Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO [tblCustomer] (SITE_ID, TITLE, FIRSTNAME, MINITIAL, LASTNAME, SUFFIX, STATUS, PSWRD, ALIAS, PHONE1, PHONEXT1, PHTYPE1, FAX, PHONE2, PHONEXT2, PHTYPE2, PHONE3, PHONEXT3, PHTYPE3) " & _
"SELECT " & lngID & " As NewID, TITLE, FIRSTNAME, MINITIAL, LASTNAME, SUFFIX, STATUS, PSWRD, ALIAS, PHONE1, PHONEXT1, " & _
"FROM [tblCustomer] WHERE CUSTOMER_ID = " & Me.[tblSite].[COMPANY_ID] & ";"
db.Execute strSql, dbFailOnError
Else
MsgBox "Main record duplicated, but there were no related records."
End If
'Display the new duplicate. Here I am not sure how to docmd to open the frmTransferRecord, before or after?
Me.Requery
Me.Bookmark = lngID
End With
End If
Exit_Handler:
Set db = Nothing
Set rs = Nothing
Exit Sub
Err_Handler:
MsgBox "Error " & Err.number & " - " & Err.Description, , "cmdDupe_Click"
Resume Exit_Handler
End Sub
Windows XP 
Access 2010 32bit
Reply With Quote
