Duplicate a Mainform and its sub forms

[Rats]

I have set up the following code to duplicate a record in a table called "customers" along with data in a sub form called "Residences". This will be used to create a new record which will become a new scenario for the client in some financial modelling we will be doing. The code runs without any error messages but on viewing the result the following has occurred.
1. A new record has been created in the table called "Customers" (as expected) but that recordset is empty (not expected).
2. The Append Query works fine and creates a new copy of the associated sub form details. However as no information is appearing in the main form record the sub form record stays blank. If I manually input a [clientID] (non primary key field) the sub form is populated.

There is obviously a problem with the .AddNew procedure but I can't identify it.

Would appreciate any help please.

Private Sub btnDuplicate_Click()
Dim dbs As DAO.Database, Rst As DAO.Recordset
Dim F As Form

' Return Database variable pointing to current database.
Set dbs = CurrentDb
Set Rst = Me.RecordsetClone

On Error GoTo Err_btnDuplicate_Click

' Tag property to be used later by the append query.
Me.Tag = Me![ClientNo]

' Add new record to end of Recordset object.
With Rst
.AddNew
!ClientID = Me!ClientID
!ScenarioName = Me!ScenarioName
!C1surname = Me!C1surname
!C1firstname = Me!C1firstname
!C2Surname = Me!C2Surname
!C2firstname = Me!C2firstname
!address = Me!address
!AdvisorID = Me!AdvisorID
.Update ' Save changes.
.Move 0, .LastModified
End With
Me.Bookmark = Rst.Bookmark

' Run the Duplicate Order Details append query which selects all
' detail records that have the ClientNo stored in the form's
' Tag property and appends them back to the detail table with
' the ClientNo of the duplicated main form record.

DoCmd.SetWarnings False
DoCmd.OpenQuery "DupResidences"
DoCmd.SetWarnings True

'Requery the subform to display the newly appended records.
Me![residences query].Requery

Exit_btnduplicate_Click:
Exit Sub

Err_btnDuplicate_Click:
MsgBox Error$
Resume Exit_btnduplicate_Click:

End Sub

 

[Rats]

Answer found

Found my own answer to this one. The procedure RecordsetCloneshould actually be written as Recordset.Clone. I discovered this while viewing some automatically generated code, tried it and it worked.

Why is it that all the MS help etc. refers to it as RecordsetClone? Trying to keep us on our toes I guess.

 

[Rats]

A Further problem with this one

As usual one problem conquered another presents itself.

I can now create a duplicate of the customer record and the append query duplicates the sub form details which is what I want to happen.

However, when I attempt to amend the new subform record I am actually editting the original sub form record. So that when I return to the original record and then view the sub form the record has now been changed to the same as the new record.

I suspect a problem with the relationships I have set up.

I would appreciate any comments on how I can lock in the new details and prevent them referring back to the original record.

I should mention that my TblCustomers has an autonumber Prime Key field as well as [ClientID]. It seems that the queries are using the [ClientID] field as their reference and not the PK[ClientNo]. If this is the case how can I change that around?

Thanks