Solved Sum([Unbound Field]) in Continuous Form Using VBA Loop (1 Viewer)

[cdoner]

Quick update. I found a more elegant solution than using two subforms. Here are the changes.

• Eliminated second subform which ran the UNION query to calculate subtotal
  • Placed the SQL for the UNION query into VBA on main form and run it on the Form_Current event (see below)
• Eliminated refresh button as it was making the dB crash when the form loaded

So basically the subform has it's own query that allows me to make edits/changes to the fields within the continous form and the VBA allows for the calculations on those changes to update.

Private Sub Form_Current()
Dim db As Database
Dim rs As Recordset


Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM [qryCalculateMenuItemCost] WHERE [MenuItemID] = " & Me.MenuItemID & " ") 'UNION qry

Dim dblTotal As Double
With rs
    If Not (.BOF And .EOF) Then
        .MoveFirst
    End If
    Do Until .EOF

        dblTotal = (dblTotal) + Nz(.Fields("ComponentSubtotal"), 0) 'Thank You Gasman for fixing this part
        'Debug.Print dblTotal 'View in Immediate Window "Ctrl +G"
        .MoveNext
    Loop
End With

Me.txtTotal_MenuItemCost.Value = dblTotal
End Sub