Calcs based on subform

[Guest]

Access 2000
I have a form called frmSales (based on a sales table – tblSales), that
contains a sub form called subfSale_Details (based on a sale details table –
tblSale_Details). The sub form contains a list of items sold along with the
cost and quantity, and a computed subtotal field - SubTotal = cost*quantity.
I want to show a TOTAL on the main form which is the sum of the sub totals on
the sub form. I am trying to use the “Sum†function as the control source
for a text box on the main form. I have tried every permutation (that I can
think of) of the following

=Sum(Forms(“frmSalesâ€)(“subfSale_Detailsâ€).Form.Controls(“SubTotalâ€)
(I have also tried using cost*quantity rather than SubTotal)

Nothing seems to work, when I open the form the text box has “#Errorâ€

Also, I have been trying to keep a running TOTAL on the main form when
entering a new sale. I tried using the OnLostFocus, and OnExit events of the
SubTotal (tab sequence in sub form cycles through the computed control) in
the sub form with the code:

Forms!frmSales.Total = Forms!frmSales.Total + Me.SubTotal
(Also tried cost*quantity rather than SubTotal)

It doesn’t work, Total on the main form remains blank. The event seems to
work since if I change the code to Forms!frmSales.Total = 16.25, The number
16.25 appears on the main form after the SubTotal is is computed


Any help would be greatly appreciated. Thanks

Garry

 

[Guest]

I solved the second problem (running total in VBA - it was a NULL gotcha.
Once I set the default value to 0, all worked fine. Still need help on the
first one - showing total on main form from sub form data for existing
records.

Thanks, Garry