![]() |
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 |
All times are GMT -4. The time now is 10:14 AM. |
Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.