Total on subform

[Fireman1143]

I have used an example of Access 200 database examples to write the code for doing a total on the subform, then having it show on the main form. I had it working until I opened it with Access 2010. Now the Sum([ItemTotal]) causes a #Error in the text box in the footer.

This also happened to the database named FrmSampl(actually written in Access 2000) which I was using as a guide.

 

[dhookom]

What is the "code for doing a total on the subform"? Can you share some "code" and/or expressions? The record sources of your forms is also important.

Duane
Hook'D on Access
MS Access MVP

 

[Fireman1143]

The table includes fields for the Proj_ItemQty, Proj_ItemCostEa and Proj_ItemTotal. The item total is calculated by:
=CCur([Proj_ItemQty]*[Proj_ItemCostEa]) This works fine
In the footer of the subform I have a text field called ProjectTotal with the following.
=Sum([Proj_ItemTotal])
It seemed odd that it work while I was developing it but can't get it to work now. Once I get this subform to calculate correctly I know I can get it displayed on the Main Form

 

[dhookom]

Where does [Proj_ItemTotal] come from? Is it a field in a record source or is it the name of a control in a form? You can't Sum() a control name. You can Sum() a field/column or an expression based on fields.

=Sum([Proj_ItemQty]*[Proj_ItemCostEa])

Duane
Hook'D on Access
MS Access MVP

 

[Fireman1143]

Thanks for the assist Duane.

Proj_ItemTotal is a field in the ITEMS table which is calculated by the expression
=CCur([Proj_ItemQty]*[Proj_ItemCostEa])on the form. It appears to work as the table shows the correct totals for each line item.
I have attached the database for your review.

 

[dhookom]

Did you try my suggestion?

Are you using a calculated column in your table design? If so, why would you need to use =CCur([Proj_ItemQty]*[Proj_ItemCostEa]) in your form?

How do you get a calculated expression in a form to actually populate a field in the table?


Duane
Hook'D on Access
MS Access MVP

 

[missinglinq]

You can't use an aggregate function against a calculated field, as you're trying to do with

[b]=Sum([Proj_ItemTotal])[/b]

You have to use the function against the expression used for the calculation, as Duane showed you in Post # 4

[b]=Sum([Proj_ItemQty]*[Proj_ItemCostEa])[/b]

In addition to his question

"How do you get a calculated expression in a form to actually populate a field in the table?"

why are you storing a calculated value? There are very few scenarios where Calculated Fields need to be stored, and this is not one of them; they should simply be re-calculated, as needed.

The Missinglinq

Richmond, Virginia

The Devil's in the Details!

 

[Fireman1143]

Thank you dhookom and missinglinq With your info I was able to get this working correctly.

 

[Andrzejek]

I see you have been a member of TT for way over 10 years, yet you only got 2 satisfied answers to your problems (based on the number of stars you have given).

If the answers you have received here are helpful to you, please use: [blue]Like this Post? Star it[/blue] link. That shows others which posts were helpful.


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.