Subform displays #Error when calculating SubTotal.

[TPrice]

Hello and good afternoon,

I am really struggling here with what should be super simple...i thought. I have created a form using a query that looks at the products table and stockmovements2 table.

I work out the total by using the Order Qty (Comes from product table) * Sale Price (Comes from product table). The value is then displayed in the Total field on the form.

However as this is a continuous form i need all of the total to show at the footer.

I am using Sum([Order Qty] * [Sale Price]) but i get #Error as my result.

Any help would be appreciated. I didnt create this database i have inherited and trying to improve things.


I have attached the database, the two forms are POFilled and the subform is called POList.

[Bob Fitz]

Hello and good afternoon,

I have attached the database, the two forms are POFilled and the subform is called POList.

I can't find a form called POFilled

[TPrice]

It is there if you look on the Navigator

[TPrice]

It is there I just searched for it to be sure i told you the correct name

[CJ_London]

you need to put your subtotal calculation in the subform form footer, then refer to these in your mainform control using '=POList.Form.whatever your subform subtotal control is called'

You can hide the subform footer

[ArviLaanemets]

There doesn't exist such form as POFilled! But there exists a form [Copy of POFilled]

Mu advice is add calculated monetary amount into query QryPo ([TblProducts].[Sale Price]*[Tvl Stock Movements2].[OrderQuantity] AS Amount). Then you can simply sum this calculated field in unbound control in footer of form (but I didn't check this out).

[Bob Fitz]

It is there I just searched for it to be sure i told you the correct name

Well I can't see it. I've attached a pic of the forms that I can see.

[TPrice]

I have done as you said but there is also error on the main form from this.

[TPrice]

Can you search for it? Thats the easiest way to find it.
very strange its not appearing...

[TPrice]

I have tried this, i can get a total for each line but the SUM of all these produces an error.

There doesn't exist such form as POFilled! But there exists a form [Copy of POFilled]

Mu advice is add calculated monetary amount into query QryPo ([TblProducts].[Sale Price]*[Tvl Stock Movements2].[OrderQuantity] AS Amount). Then you can simply sum this calculated field in unbound control in footer of form (but I didn't check this out).

[Gicu]

Please have a look at the attached updated file, I used a totals query to get the sale total then a dlookup in your form to leave it updatable.

Cheers,

[Micron]

Fairly certain that you cannot sum a calculate field - at least that was true at some point in time. That is/was because the values are not stored. The values should come from a table or query, which you'd then sum and do any calculations against that control but show in another control.

EDIT -To clarify, I mean that the calculated field is in the subform underlying query. I spent a few minutes looking at that and the tables but didn't come up with a way to fix it in that short time.