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

cdoner

Registered User.
Local time
Today, 07:47
Joined
Dec 1, 2013
Messages
25
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.

Code:
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
 

Users who are viewing this thread

Top Bottom