Calculating subform fields

[Tvanduzee]

Hello

Access 2007

I'm about at my witts end with trying to get this to work. I hope someone can solve this form me, or lead me in the right direction.

I have a main form (frm_WO). I have a subform (frm_WO_Details_Subform). (one to many)
Master link field is: WO_WorkOrderNo (which is an autonumber field)


Child link field is: FK_WONumber.
I have no problem viewing any of the records the way they should be.

I have a field in the subform called "WO_Details_Cost" (which I need to be totaled) this is a currency field.
I also have 3 other fields that I need to total as well, but I can take the solution from the Cost field calculation and apply it where needed.

I have tried (in the subform footer) to used Sum, Dsum and I even tried doing a Select Sum(......). But I keep getting the #error in the textbox that I want the total to show up in. (I will end up putting this value in the main form form header, but have not gotten to that point yet.

In the subform footer I have tried
=Sum([frm_WO_Details_Subform]![WO_Details_Cost] --- #error
=Dsum([frm_WO_Details_Subform]![WO_Details_Cost],"qry_WO_Details","[frm_WO_Details_Subform]![FK_WONumber]= " Me.[WO_WorkOrderNo] --- got the same #error
And of course the Select statement did not work at all....

I would sure appreciate a hand with this. It's holding me back from completing my project.

Thanks So Much
Terry

[RuralGuy]

Did you try: Sum([WO_Details_Cost])

[Tvanduzee]

Yes, I did. Got the same #error

[RuralGuy]

I assume your SubForm is in Continuous Form view so the footer is not visible. It needs to Sum() controls on your SubForm and not the fields to which they are bound. Those controls need not be visible.

[Tvanduzee]

I actually have it as a datasheet view.
So, on a subform that contains many rows, how is it possible to sum the control?

Is it possible to sum directly from the header of the main form, instead of summing from the subform?

[RuralGuy]

More complicated. Make sure the Name of your Control is *different* than the field to which it is bound, then Sum() the control and not the field in the footer.

[Tvanduzee]

Hmmm
I'm not sure I know what you mean. Is the control for the field not the field itself?
I'm suspecting to create an Expr field in a query and name it something different, and assign the actual field to the expr.

What is the difference between a control and the field?

[RuralGuy]

Controls are objects on Forms that display Fields in tables, among other things. Each control on your Datasheet View has its own name which unfornately defaults to the name of the field it displays. This can confuse Access when you try and do what you are doing. Each control has its own property sheet and the 1st element of the Other Tab of the property sheet is Name. Change it to C1 and then Sum(C1) in the footer and see what happens.

[Tvanduzee]

Ok
Sorry about that, I think I figured out what you mean. On the field "WO_Details_Cost", I assigned the "Name" of it as "Cost". I then tried to Sum([Cost]) but received the same error : #error
I tried summing both in the subform footer and in the main form header and received the same error.

Terry

[RuralGuy]

Any chance you could post your db? Compact and Repair and then zip it up to attach. Remove any sensitive data of course.

[Tvanduzee]

The Form in question is frm_WO and its subform frm_WO_Details_Subform
Relationship is between ID on the Main form and FK_WONumber on the subform.

You will notice I have tried to subtotal in 3 Places. On the subform footer, the mainform footer and the Mainform header. The only one I need to see is the one in the Main form header.

All data in the db is test data only; at this point.

Thank You

Terry

[RuralGuy]

This will not fix your issue but you need to read this link: http://access.mvps.org/access/lookupfields.htm

[Missinglinq]

This is tested with your attached file and works:

On the Main Form, set the Control Source for Text26 (aka Cost Total) to

=[frm_WO_Details_Subform]![Text22]

Then, on the Subform, set the Control Source for Text22

=Sum([WO_Details_Cost])

When you get this in place, you really need to rename these Controls (Text22 and Text26), as I'm sure you know, from looking at the rest of your Forms, but be very careful, when you do, to adjust the Control Source of Text26 to reflect the new name of Text22!

Linq ;0)>

[RuralGuy]

Excellent point Linq. I think the formatting of the control was causing the error.

[Missinglinq]

You know, Allan, I've done this dozens and dozens of times, and it always has me banging my head!

You got any snow up there, yet, in the thin-air country?

Linq ;0)>