Copying the Current Record into a new Record (with designated new primary key) CONT..

[pbaldy]

Well, you open a recordset on the target table, which may also be the source. If you want to use a form, I'd open the recordset on the record to be copied, then copy the values to the form (after either opening to a new record or using GoToRecord to move to a new record).

[imsants]

Scratch this... Do I open a new record within the code in question, if so how/where do I do this? Or am I doing two separate codes, one onclick to open and then afterupdate to run the recordset? ... I figured it out, so good news, it is doing something now and it claims that it can't create the record because it will create duplicates which is true. I removed the !SITE_ID = Me.SITE_ID.Value + 1 from the original code (I sent you) and just did !SITE_ID = Me.SITE_ID. When I test it the error comes up that it will create duplicates on .update... I guess this is because my ID is designated and it can't generate an autonumber being that it's text and not lngID as set in dim. So if possible could you help me figure this one out. The SITE_ID is text like '*LEW123*' and all the subforms have autonumbers as IDs instead. Any suggestions there. Thanks a bunch again. PS - Thinking, should have made them like the SITE_ID?

Here are the changes made:

'Duplicate the main record: add to form's clone.
With 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
'etc for other fields.
.Update 'this is where the debug error takes me.

'Save the primary key value, to use as the foreign key for the related records.
.Bookmark = .LastModified
lngID = !CUSTOMER_ID

'Duplicate the related records: append query.
If Me.[frmNewCustomer].Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO [tblCustomers] (CUSTOMER_ID, CONTACT_ID, TITLE, FIRSTNAME, MINITIAL, LASTNAME, SUFFIX, STATUS, PSWRD, ALIAS, PHONE1, PHONEXT1, PHTYPE1, FAX, PHONE2, PHONEXT2, PHTYPE2, PHONE3, PHONEXT3, PHTYPE3) " & _
"SELECT " & lngID & " As NewID, CONTACT_ID, TITLE, FIRSTNAME, MINITIAL, LASTNAME, SUFFIX, STATUS, PSWRD, ALIAS, PHONE1, PHONEXT1, PHTYPE1, FAX, PHONE2, PHONEXT2, PHTYPE2, PHONE3, PHONEXT3, PHTYPE3 " & _
"FROM [tblCustomers] WHERE CUSTOMER_ID = " & Me.CUSTOMER_ID & ";"
db.Execute strSql, dbFailOnError
Else
MsgBox "Main record duplicated, but there were no related records."
End If

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