Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old April 17th, 2008, 03:01 AM
Authorized User
Join Date: Mar 2006
Posts: 83
Thanks: 1
Thanked 0 Times in 0 Posts
Default 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."
        'Duplicate the main record: add to form's clone.
        With Me.RecordsetClone
                !ControlDataDe = Me.ControlDataDe
                !ControlDataA = Me.ControlDataA
                'etc for other fields.

            '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 Sub

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

End Sub

Any assistance would be greatly appreciated!

Thank you!


Similar Threads
Thread Thread Starter Forum Replies Last Post
Check for Duplicate Records in Data Entry Form roznix Access VBA 5 June 7th, 2012 08:53 AM
form/subform problem chacquard Access VBA 1 November 21st, 2006 06:50 PM
Cant print when the form become subform yikchin Access 6 November 22nd, 2005 10:22 PM
Subform allows end-user to make duplicate record pkaptein1 Access 7 May 25th, 2005 11:34 AM
form/subform problem chacquard Access VBA 5 June 21st, 2004 10:45 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.