Duplicate record on form, including subform1 and subform2 (which is dependent on subform1)...

[pncbiz]

I have main form (Contracts) in which one enters contract data. There is a subform in which one enters the products purchased/associated with that contract. To facilitate contract renewals, I have a duplicate command button on the main form that, using Allen Brown's excellent code (http://allenbrowne.com/ser-57.html) duplicates the records on both the mainform AND the subform perfectly.

However, I have additional subforms on the main form that store cost allocation data for each individual product. The underlying table of the products subform has a primary key that is then, in turn, used to record allocation data for each product. Functionally, the allocation forms are subforms to the product subform (although they do not have linked parent/child fields; I use an intermediate unbound form (list box) and VBA to drive filters for the allocation subforms). I need to be able to copy the allocation record as well, using the newly generated Product primary key for each product.

To state it visually here's the flow in terms of primary keys. Each --> represents a one to many relationship:
OrderID (Contract) --> OrderProductID (Products) --> OrderServiceCatalogID
OrderID (Contract) --> OrderProductID (Products) --> OrderServiceLineID
OrderID (Contract) --> OrderProductID (Products) --> OrderMinorAllocationID

I have code to duplicate the first relationship for each, but I do not have code to duplicate the second one. It's easy to generate the first duplication, because OrderID already exists on the form. For the second one, however, the OrderProductID is generated in the code, so how can I use that newly generated OrderProductID to key to the OrderServiceCatalogID? I can't just grab the last modified OrderProductID; there can be more than one product associated with the contract.

If the code would duplicate products one at a time, then it would be easier to duplicate the allocation records associated with each product. But Allen Brown's code duplicates ALL the products in one IF statement:

Private Sub cmdDuplicateContract_Click()'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 any 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
                !VendorID = Me.cboResellerID
                !Reference = Me.txtReference
                !OrderSummary = Me.txtOrderSummary
                !RenewalIntent = Me.chkRenewalIntent
                !ContractStart = Me.txtContractStart
                !TerminationNotice = Me.txtTerminationNotice
                !ContractEnd = Me.txtContractEnd
                !InvoicePeriodID = Me.cboInvoicePeriodID
            .Update
            
            'Save the primary key value, to use as the foreign key for the related records.
            .Bookmark = .LastModified
            lngID = !OrderID
            
            'Duplicate the related records: append query.
            If Me.[subProduct].Form.RecordsetClone.RecordCount > 0 Then
                strSql = "INSERT INTO [OrderProduct] ( OrderID, VendorID, ProductID, Serial, Quantity, UnitCost, DepartmentID, SubAccountID, CapexBudgetID ) " & _
                    "SELECT " & lngID & " As NewID, VendorID, ProductID, Serial, Quantity, UnitCost, DepartmentID, SubAccountID, CapexBudgetID " & _
                    "FROM [OrderProduct] WHERE OrderID = " & Me.OrderID & ";"
                DBEngine(0)(0).Execute strSql, dbFailOnError
            Else
                MsgBox "Contract duplicated, but no products found."
            End If
'            If Me.[subServiceCatalogAllocation].Form.RecordsetClone.RecordCount > 0 Then
'                strSql = "INSERT INTO [OrderServiceCatalog] ( OrderServiceID, OrderProductID, ServiceID, Percentage ) " & _
'                    "SELECT " & lngID & " As NewID, NEWORDERPRODUCTID, ServiceID, Percentage " & _
'                    "FROM [OrderServiceCatalog] WHERE OrderProductID = " & Me.[EACHORDERPRODUCTID] from av & ";"
'                DBEngine(0)(0).Execute strSql, dbFailOnError
'            Else
'                MsgBox "Contract duplicated, but no service catalog allocation found."
'            End If
'            If Me.[subServiceLineAllocation].Form.RecordsetClone.RecordCount > 0 Then
'                strSql = "INSERT INTO [OrderServiceLine] ( OrderServiceLineID, OrderProductID, ServiceLineID, Percentage ) " & _
'                    "SELECT " & lngID & " As NewID, NEWORDERPRODUCTID, ServiceLineID, Percentage " & _
'                    "FROM [OrderServiceLineID] WHERE OrderProductID = " & Me.[EACHORDERPRODUCTID] & ";"
'                DBEngine(0)(0).Execute strSql, dbFailOnError
'            Else
'                MsgBox "Contract duplicated, but no service line allocation found."
'            End If
'            If Me.[subOtherAllocation].Form.RecordsetClone.RecordCount > 0 Then
'                strSql = "INSERT INTO [OrderMinorAllocation] ( OrderMinorAllocationID,OrderProductID, MinorAllocationID, Percentage ) " & _
'                    "SELECT " & lngID & " As NewID, NEWORDERPRODUCTID, MinorAllocationID, Percentage " & _
'                    "FROM [OrderMinorAllocationID] WHERE OrderProductID = " & Me.[EACHORDERPRODUCTID] & ";"
'                DBEngine(0)(0).Execute strSql, dbFailOnError
'            Else
'                MsgBox "Contract duplicated, but no other allocation found."
'            End If


            'Display the new duplicate.
            Me.Bookmark = .LastModified
        End With
    End If


Exit_Handler:
    Exit Sub


Err_Handler:
    MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdDupe_Click"
    Resume Exit_Handler
End Sub

Thanks for any ideas. I'm certain I haven't explained this well...
Phil

[Gicu]

Try to store the original OrderProductID in the Product table from the initial records you are duplicating (add a new field to the table called OriginalOrderProdID and modify the append query to include that field). Then you can use that one in a join to add the related record from the other three tables (join the OrderProduct to OrderServiceCatalog by OrderProduct.OriginalOrderProdID to OrderServiceCatalog.OrderProductID).

Cheers,
Vlad

[pncbiz]

Vlad,
Thanks! Actually, I've been noodling on this and I think I've figured out one nuance that may help.

There is only ONE OrderID; there are many OrderProductIDs for each OrderID. They have a relationship. The SQL append query pulls all the OrderProductIDs associated with the ONE OrderID.

For each OrderProductID, there are many OrderServiceCatalogIDs (etc.). However, each OrderProductID still has the ONE OrderID. So the SQL append query could pull all the OrderServiceCatalogIDs where the OrderIDs of the OrderProductIDs are the same. In other words, OrderID is the one constant I'm trying to match against. If I query all the OrderProductIDs with OrderID, then append a copy of all the OrderServiceCatalogIDs, then that should do it, right?

This is so hard to explain in words...

[pncbiz]

I've attached a copy of my db, slimmed down, in case that helps.
frmITFMNav is an always-open form; make sure it's always open before you try to run the Contract Manager.

I also built a test select query (qryTest), that pulls the desired data (I've got the OrderID of 73 hard coded in as my test contract) for the Service Catalog Allocation (once I get it running, I'll copy/paste to the others). So that's the data I want to pull. Now I need to append those two fields (ServiceID and Percentage) back to the same table BUT with the respective,. NEW OrderProductIDs created in the duplication routine in the first append query of the code above. I already have the new OrderID (lngID).

Maybe this will help...

[Gicu]

Hi Phil,

Please have a look, this is what I was trying to say. I think you need a persistent reference to the original orderproductid to make it work.

Cheers,

[pncbiz]

Vlad,
Thank you so much! I see the duplicate VBA, but where did you put the routine to copy the old OrderProductID?

[pncbiz]

Vlad,
Never mind, I see it now. I guess you have to add that extra field because it's impossible to just have code go line by line and update all the tables. If it did, you could store the original OrderProductID as a variable in the code. Instead, it does bulk updates so it has to have the original in the table so it can just copy it over.
Thanks,
Phil

[Gicu]

Yes, that's correct. Easier than adding them one by one.

Cheers,
Vlad