Calculating total cost of rows in datasheet view

[Simon22]

Hi all,
I have a sales form that list the client's name/address with a subform that is used to make the invoice. The subform is displayed in datasheet view and has the following columns (Product_ID, Product_Description, Product_Cost, Number_of_Units, (Row Subtotal = Product_Cost*Number_of_Units).

As an item is chosen from a lookupwizard, the related description and price come up. The user enters the number of units and then the row subtotal column calculates the total for that row. This is working fine.

My problem is: I want to somehow get the sum of all the row totals into a textbox on the form so that I can use it in the main form to calculate HST and the grand total.

I have a textbox in the footer of the subform with control source = SUM(Row_Total). The problem is: Access just displays #Error in this box. Can you tell me why the sum of the row totals cannot be diplayed in this box? Can a value not be summed from a bunch of calculated values? This works in my other form which just diplays past invoices but the difference there is that the values for everything are already stored and do not have to be calculated.

Please help me !!!!!

 

[RichUK]

=SUM([ProductCost]*[Number Of Units]}NB try and avoid spaces in field names Access doesn't like them.

 

[MikeKohler]

I had the exact same problem. The only way I could get this to work was to change the design of the subform to tabular and include the total in the subform.
-M.Kohler
mkohler@telusplanet.net