SubForm calculations don't run after cloneing Form (1 Viewer)

[isoman53]

Using Allen Browne's code for cloning a record from the current one on a form with several subforms I found a small snag. The clone works. The only snag I found was that the "Line Total" calculated text box in one of the subforms does not calculate each line as it does when data was entered in the previous record. Once I change one of the fields in one of the records in that subform (Process) and move to the next field the calculation runs for all records in the subform. The only difference in the subforms is that the "Process" subform uses two fields of data on the main form to run the calculations notably [Qty] & [MCI]. I also noticed that when entering a new record on the main form if I go back to change [Qty] it does not change the Total calculations in the Process subform. So I think using those two fields from the main form as input for the totals calculations in the process subform is the crux of my problem. How can I work around this?
The calculation for the Line Total in Process subform:
=(([intSUTime]+([intCycleTime]*[Text23]))*[Text25])+[intSetCost]

[Text23]is "Qty" carried into subform from Main Form
[Text25]is "Machine Charge Index" carried into subform from Main Form

The clone code I am using:
Private Sub Command52_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 ldsID 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
!intMCI = Me.intMCI
!dtmDateEntered = Date
'etc for other fields.
.Update

'Save the primary key value, to use as the foreign key for the related records.
.Bookmark = .LastModified
intMID = !idsID
intCID = !idsID
intTID = !idsID
intGID = !idsID
intFID = !idsID
intEID = !idsID
intPID = !idsID
intPkID = !idsID

'Duplicate the related material records: append query.
If Me.[frmMainEntryMaterialsubform].Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO [tblMaterial] ( intMID, strMNotes, strMDescription, strMSize, intMCost ) " & _
"SELECT " & intMID & " As NewID, strMNotes, strMDescription, strMSize, intMCost " & _
"FROM [tblMaterial] WHERE intMID = " & Me.idsID & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
Else
MsgBox "Main record duplicated, but there were no related records."
End If

'Duplicate the related component records: append query.
If Me.[frmMainEntryComponentsubform].Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO [tblComponent] ( intCID, strCNotes, strCDescription, strCSize, intCCost ) " & _
"SELECT " & intCID & " As NewID, strCNotes, strCDescription, strCSize, intCCost " & _
"FROM [tblComponent] WHERE intCID = " & Me.idsID & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
Else
MsgBox "Main record duplicated, but there were no related records."
End If

'Duplicate the related tooling records: append query.
If Me.[frmMainEntryToolingsubform].Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO [tblTooling] ( intTID, strTNotes, strTDescription, intTCost ) " & _
"SELECT " & intTID & " As NewID, strTNotes, strTDescription, intTCost " & _
"FROM [tblTooling] WHERE intTID = " & Me.idsID & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
Else
MsgBox "Main record duplicated, but there were no related records."
End If

'Duplicate the related gauging records: append query.
If Me.[frmMainEntryGaugingsubform].Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO [tblGauging] ( intGID, strGNotes, strGDescription, intGCost ) " & _
"SELECT " & intGID & " As NewID, strGNotes, strGDescription, intGCost " & _
"FROM [tblGauging] WHERE intGID = " & Me.idsID & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
Else
MsgBox "Main record duplicated, but there were no related records."
End If

'Duplicate the related freight records: append query.
If Me.[frmMainEntryFreightsubform].Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO [tblFreight] ( intFID, strFNotes, strFDescription, intFCost) " & _
"SELECT " & intFID & " As NewID, strFNotes, strFDescription, intFCost " & _
"FROM [tblFreight] WHERE intFID = " & Me.idsID & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
Else
MsgBox "Main record duplicated, but there were no related records."
End If

'Duplicate the related engineering records: append query.
If Me.[frmMainEntryEngineeringsubform].Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO [tblEngineering] ( intEID, strENotes, strEDescription, intHr) " & _
"SELECT " & intEID & " As NewID, strENotes, strEDescription, intHr " & _
"FROM [tblEngineering] WHERE intEID = " & Me.idsID & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
Else
MsgBox "Main record duplicated, but there were no related records."
End If

'Duplicate the related process records: append query.
If Me.[frmMainEntryProcesssubformLookupByPartNumber].Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO [tblProcess] ( intPID, strOP, strPDescription, intSetCost, strMachine, intSU, intCycle ) " & _
"SELECT " & intGID & " As NewID, strOP, strPDescription, intSetCost, strMachine, intSU, intCycle " & _
"FROM [tblProcess] WHERE intPID = " & Me.idsID & ";"
DBEngine(0)(0).Execute strSql, dbFailOnError
Else
MsgBox "Main record duplicated, but there were no related records."
End If

'Duplicate the related packaging records: append query.
If Me.[frmMainEntryPackagingsubform].Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO [tblPackaging] ( intPkID, strPkDescription, intPkCost,intPkQty ) " & _
"SELECT " & intPkID & " As NewID, strPkDescription, intPkCost, intPkQty " & _
"FROM [tblPackaging] WHERE intPkID = " & Me.idsID & ";"
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, , "Command52_Click"
Resume Exit_Handler

End Sub

 

[Megan]

*Disclaimer: Listen to my fledgling ideas at your own peril and amusement. Sometimes I get it right - maybe you'll get lucky.

I didn't understand a good deal of your post (probably due to my noobility), but perhaps data macros to calculate totals in the tables themselves would be helpful in your situation.