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


Registered User.
Local time
Today, 07:47
Dec 1, 2013
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
    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"
End With

Me.txtTotal_MenuItemCost.Value = dblTotal
End Sub

Users who are viewing this thread

Top Bottom