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