Subform won't calculate subtotal - nothing to pass to main form.

[Helen269]

Help! I'm doing an Access 2003 database for IT class in Windows XP. We have to pass ALL units or fail the course. So far I'm doing very well but this Access database is not behaving itself and threatens to be the one thing that will fail me.

I have a form with a subform. The subform allows the user to input items for purchase from a table of products then is supposed to add it all up and present me with an order total.

In the Order Details form I have:
OrderNumber
ProductNumber
Quantity
Subtotal =[Quantity]*[Price] - the price is stored on the product info table)

The field names here are the actual names of the control boxes, not the labels. Just to be clear on that.

In the subform footer I have a text box with =Sum([SubTotal]) in it that is supposed to refer to the SubTotal box and give me the grand total for a given numbered order. I check to see it's doing what it's supposed to in the Form View but all I get is £0.00 - what's going wrong?



I've seen this problem in this and other forums and checked from those threads that I'm doing it right and I seem to be but obviously something's not quite right. I'm stumped.

[Helen269]

If it helps here is a picture of the main and subform together showing the two items on order number 1 which should add up to £35.

I'll worry about getting the subtotal onto the main form later but in the meantime my order total in the subform in Form view should say £35 but doesn't and I can't for the life of me work out why it doesn't.

[SteveH2508]

In the subform footer I have a text box with =Sum([SubTotal])

You cannot refer to the text box in the formula (it is not like Excel) - you need to recreate the formula.
Like this:
=Sum([Quantity]*[Price])

[Helen269]

You cannot refer to the text box in the formula (it is not like Excel) - you need to recreate the formula.
Like this:
=Sum([Quantity]*[Price])

Aha! Thank you! That kind of works but now in the Form view I have a total for every item in the orders table, not just order no. 1, but at least it's a start.

I'm hoping that once I figured out how to get the grand total figure to show in the totals box in the main form that it will only show the grand total of each order.

The order details table has Order Number as its first column so the first two records are order no.1, the next few are order no.2, the next couple are order no.3 and so on. What you have suggested totals everything in that table regardless of which order no. it belongs to.

Let's see what happens when I (eventually) work out how to get the subform total linked to the main form total...

[Helen269]

New

I had to put this into my main form Order Total box:

=Forms("frm2_CustomerOrder")![frm2_OrderDetails_Subform].[Form].[OrderTotal]

After MUCH searching and trying various solutions I found this on someone else's thread and adapted it to my field and form names. I would NEVER have figured this out by myself.


I hate Access 2003.