Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old April 17th, 2008, 03:01 AM
Authorized User
Join Date: Mar 2006
Location: , , .
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!

Reply With Quote

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

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

All times are GMT -4. The time now is 10:33 AM.

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