Duplicate Data in Form, its Subform and SubSubForm
 

Hiya folks!

Heres the problem:

I have three tables. The first table is related to second table (one-to-many) and the second table is related to the third table (one-to-many). I built a form/subform/subsubform based on these tables.

I have been successful at duplicating the record from the form and subform into a new record - but I have not been able to find a way to also duplicate the data from the subsubform into the new record.

Here are the tables that I have (sorry it is not in english so I added some translation to help):

TBLCONTROLDATA - tblcontroldates
ControlDataID - controldateid
ControlDataDe - controldatefrom
ControlDataA - controldateto

TBLCONTROLSECCAO - tblcontrolsection
SeccaoID - sectionid
Seccao - section
ControlDataID - controldateid

TBLCONTROLARTIGO - tblcontrolproduct
ArtigoID - productid
Artigo - product
PrecoCIVA - priceinludingtax
SeccaoID - sectionid

Here is the code I have on the button that is used to duplicate the currently selected record:

'On Error GoTo Err_Handler
    'Purpose: Duplicate the main form record and related records in the subform.
    Dim strSql As String 'SQL statement.
    Dim lngID As Long 'Primary key value of the new record.


    'Save and 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
                !ControlDataDe = Me.ControlDataDe
                !ControlDataA = Me.ControlDataA
                'etc for other fields.
            .Update

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

            'Duplicate the related records: append query.
            strSql = "INSERT INTO [tblControlSeccao] (ControlDataID, Seccao) " & _
                "SELECT " & lngID & " As NewID, Seccao " & _
                "FROM [tblControlSeccao] WHERE ControlDataID = " & Me.ControlDataID & ";"
            DBEngine(0)(0).Execute strSql, dbFailOnError



            'Display the new duplicate.
            Me.Bookmark = .LastModified
            Me.sbfrmControl.Visible = False
            Me.sbsbfrmControl.Visible = False
            Me.sbsbsbfrmControl.Visible = False
            Me.Label17.Visible = False
            Me.Label23.Visible = True
            Me.ControlDataDe.Locked = False
            Me.ControlDataA.Locked = False
            Me.ControlDataDe.Value = Null
            Me.ControlDataA.Value = Null
        End With
    End If

Exit_Handler:
    Exit Sub

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

End Sub

Any assistance would be greatly appreciated!

Thank you!

Odeh