tweaking Allen Brown Duplicate the record in form and subform code (1 Viewer)

[connerlowen]

tweaking Allen Browne Duplicate the record in form and subform code

HI,

I am using the code from AllenBrown website to duplicate the records in my main form and subform. The code works great for this purpose. My next task is to get the code to duplicate the records in my main form, subform, and two sub-subforms. I have tried many things and the database will not copy the records in the sub-subforms. Any help on this problem would be greatly appreciated. Here is a link to the website for the code I am using.

http://allenbrowne.com/ser-57.html

Thanks,

Conner Owen

 

[Minty]

What have you tried so far ? Without seeing your code it's very difficult to see why it's not working...

 

[connerlowen]

This is my code currently. The first If clause that copies the record in the subform is working fine. The next two if clauses are to copy the record in the sub-subform. none of those records are being copied at all.


'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
!AssemblyPartNumber = Me.AssemblyPartNumber
!AssemblyDescription = Me.AssemblyDescription
!QuantityPerAssy = Me.QuantityPerAssy
!EAU = Me.EAU
!MinBatch = Me.MinBatch
!AssemblyHours = Me.AssemblyHours
'etc for other fields.
.Update

'Save the primary key value, to use as the foreign key for the related records.
.Bookmark = .LastModified
lngID = !AssemblyID

'Duplicate the related records: append query.
If Me.subNewPartF.Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO NewQuotationPartT ( AssemblyID, PartNumber , PartDescription , EAU , MinBatch , ProcessID , FabPrice , ImportDuty , HandlingOperations , NaecoPayShipping , SecondOpCost , CostPerShipmentFinishedProduct, ImportDutyPercent, ExchangeRateRisk, LabourRatePerHour, AssemblyLabourRate, LocalShippingPerKg, ImportShippingPerKg, MetalsFactor, QuantityPerAssembly, AssemblyHours, QuotationID ) " & _
"SELECT " & lngID & " As NewID, PartNumber , PartDescription , EAU , MinBatch , ProcessID , FabPrice , ImportDuty , HandlingOperations , NaecoPayShipping , SecondOpCost , CostPerShipmentFinishedProduct, ImportDutyPercent, ExchangeRateRisk, LabourRatePerHour, AssemblyLabourRate, LocalShippingPerKg, ImportShippingPerKg, MetalsFactor, QuantityPerAssembly, AssemblyHours, QuotationID " & _
"FROM NewQuotationPartT WHERE AssemblyID = " & Me.AssemblyID & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
Else
MsgBox "Main record duplicated, but there were no related records."
End If

If Me!subNewPartF.Form!subNewMetalF.Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO NewMetalT ( PartID, PreciousOrBase, DateOfPrice, Metal, MetalMarket, MetalPrice, SupplierMarkUp, MetalWeight, MetalIncluded, NaecoMarket, NaecoMetalPrice, NaecoMarkUp) " & _
"SELECT " & lngID & " As NewID, PreciousOrBase, DateOfPrice, Metal, MetalMarket, MetalPrice, SupplierMarkUp, MetalWeight, MetalIncluded, NaecoMarket, NaecoMetalPrice, NaecoMarkUp " & _
"FROM NewMetalT WHERE PartID = " & Me!subNewPartF.Form!PartID & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
Else
MsgBox "Main record duplicated, but there were no related records."
End If

If Me.subNewPartF.Form.subInternalProcessF.Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO ProcessT ( PartID, Material, [Estimated/Actual], PartWeight, ScrapWeight, Diameter, [Rod/BarLength], ScrapLength, PartLength, CutOffLength, [DollarsPerRod/Bar], CycleTime, ProductionEfficiency, Scrap, ScrapMat, External2ndOp, TotalCost, ToolRecurringPer1kParts) " &
"SELECT " & lngID & " As NewID, Material, [Estimated/Actual], PartWeight, ScrapWeight, Diameter, [Rod/BarLength], ScrapLength, PartLength, CutOffLength, [DollarsPerRod/Bar], CycleTime, ProductionEfficiency, Scrap, ScrapMat, External2ndOp, TotalCost, ToolRecurringPer1kParts " & _
"FROM ProcessT WHERE PartID = " & Me.subNewPartF.Form.PartID & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
Else
MsgBox "Main record duplicated, but there were no related records."
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.

 

[Garrett!]

I don't know if this is the problem, it could be my computer, but it looks like to me your second If statement as a space in RecordsetClone between the O & N:

If Me!subNewPartF.Form!subNewMetalF.Form.RecordsetClo ne.RecordCount > 0 Then

Same thing with the third IF:

If Me.subNewPartF.Form.subInternalProcessF.Form.Recor dsetClone.RecordCount > 0 Then

Only this time it is the R & D. I don't know if it's that simple. I'm not the world's best programmer, so I could be WAY off.

 

[connerlowen]

It is just a computer thing. My code in Access does not have the spaces. Thank you for your Response though.

 

[Minty]

I can't test this but I think you may need to run the sub.sub record code on the sub records as if it was the parent form.

 

[connerlowen]

The subNewPartF form is the parent to the InternalProcessF form and the NewMetalF Form. The form NewPartF can be a subform to the form NewAssemblyF, but can calso be its own individual form. There is a command button on the subNewPartF form that duplicates the current record on that form and the related records on the other two buttons correctly when it is an individual form. the command button I am having trouble with is the button on the form NewAssemblyF. It duplicates the record on that form and the related records on the form NewPartF correctly. I need it to also duplicate the records in the forms InternalProcessesF, and NewMetalF which are related to the record in the form NewPartF.

Hopefully that makes a little more sense. Thank you.

Conner