Totals on Sub forms

[PulseTeqUser]

Hi

Struggling to get a total on a subform.

I have a form called Orders Placed. This has a subform called Order Details. This is a datasheet with each order line for the order seen in Orders Placed. There is a field called Total Price which is calculated thus

=[Quantity]*[Unit Price]

This works fine.

I have a text box in the footer of the subform called Value of Order. This has the Control source

=Sum([Total Price])

but it never gets displayed

What am I doing wrong?

Thanks

 

[Ranman256]

in the query, if the name of the field for
[TOTAL PRICE]: [Quantity]*[Unit Price]

then the textbox =Sum([Total Price]) is correct. Be sure the field name is correct.

BUT if any records have a null Qty or Price,
then the field will be null. But none of these fields should be null in an order.

 

[PulseTeqUser]

Thank you for replying.

I see in Layout view that the field on the subform is being populated. However when I look at the Orders Placed form I am just getting #Error. Some screenshots included to illustrate.

 

[Tieval]

Access doesn't like doing two bits of maths. Instead of having a calculated field on the sub-form for total you need to put it in the query you used and display it as a value rather than a calculation.

You will then be allowed to sum the fields.

 

[PulseTeqUser]

Thank you for responding but I am not sure what you mean - are you able to provide an example?

 

[Tieval]

Unfortunately, having looked through this again, I may be wrong. Can you post the database so that I can take a look. I think you can just zip it and attach it.

 

[PulseTeqUser]

I appreciate your help. Here is the database

 

[Tieval]

You cannot use a datasheet view for this, attached seems to work.

 

[Tieval]

One thing to note here, although I hate datasheet views most people seem to love them. If you want a datasheet view you can always make a continuous form look like a datasheet view.

 

[Tieval]

and change the main form order value textbox to =[Order Details].[Form]![Text13]

 

[PulseTeqUser]

Wonderful, thank you! Can I ask what you changed in the database for future knowledge?

 

[Minty]

Your Table designs aren't quite right - firstly you are using a calculated field in the table for your Total value. Whilst this isn't necessarily incorrect, it also can lead to problems - for instance null values (which I think is why you are getting error#).

You should also have another autonumber field in your order details table. At the moment you can't identify a particular line from the details table without checking the part number ordered. So add an OrderLineID as an autonumber.

I would be tempted at this stage to rename all your fields and remove the spaces and any non alphanumeric characters from them. They will cause problems later.

 

[Tieval]

You may want to look at Minty's logics, I just looked directly at your question. I just changed the sub-form view to continuous form as attached and then messed with the layout a bit.