Need help duplicating subforms using SQL...

[jonathanchye]

Hi,

Really need a bit of help here as I am well stuck

I am trying to run a duplicate function with duplicates all fields in the main forms and also on the subforms. I've read an excellent guide by Allen Browne here : http://allenbrowne.com/ser-57.html

However, I believe my tables are a bit complex compared to his example. I've attached a picture of how my tables are related. I wonder if someone could give me a rough guide of how to do this (if even possible). The most troublesome subform would be the Machines form as it has further 4 related subforms. So I would need to copy over x3 Machines + their related subforms....

My main form is based on "tblVersionControl" and have a tab control containing subforms based on "tblMachines" and "tblMaterialSubstrates". The subform based on "tblMachines" have further someforms as each machine would have different properties (Run Speed, Set Time etc...)



[URL="http://img560.imageshack.us/i/tblstruct.jpg/"]

 

[jonathanchye]

well I've tried to modify Allen Browne's code for tblPlateChangeWaste (not shown in picture but same structure as"tblMaterialsSubstrate" with less fields but it throws up an error saying I am trying to create a duplicate entry... here's my code :

            'Duplicate the related records: append query.
          If Me.[tblPlateChangeWaste subform].Form.RecordsetClone.RecordCount > 0 Then
            'If Me.listMachines.ListCount > 0 Then
                strSql = "INSERT INTO [tblPlateChangeWaste] ( ID, FKVersionID,NoOfColours, Waste, RunOn,Setup ) " & _
                    "SELECT " & lngID & " As NewID, FKVersionID,NoOfColours, Waste, RunOn,Setup " & _
                    "FROM [tblPlateChangeWaste] WHERE FKVersionID = " & Me.PKVersionControl & ";"
                DBEngine(0)(0).Execute strSql, dbFailOnError
            Else
                MsgBox "Main record duplicated, but there were no related records."
            End If

 

[jonathanchye]

Well..after more experienment I have got most of it working. I can copy over the machines (tier 1) but I can't find out how to also copy over all the subforms related to machines (tier 2).

Here's my code to copy over all the machines. Can anyone tell me how to copy and appened the other related tables to tblmachines? They only have an FK which is the PK for machines...maybe I should add a design to insert a primary key for tblVersions into them?

    If Me.listMachines.ListCount > 0 Then
                strSql2 = "INSERT INTO [tblMachines] ( FKVersionID, VersionNo, txtMachineName, curHourlyRate) " & _
                    "SELECT " & lngID & " As FKVersionID, VersionNo, txtMachineName, curHourlyRate " & _
                    "FROM [tblMachines] WHERE FKVersionID = " & Me.PKVersionControl & ";"
                DBEngine(0)(0).Execute strSql2, dbFailOnError
            Else
                MsgBox "Main record duplicated, but there were no related records Machines."
            End If