Subform calculation includes ALL subform items

[PamelaJoy]

I'm sure this is going to be obvious, but right now I am stumped. I am also very new to Access and learning as I go.

I am calculating a field on the subform, then using 'sum=' to adding them together on the subform footer. But the result ends up being the total of all of the subforms, not just the subform as it relates to each individual record/main form.

I tried moving the 'sum=' calculation to the footer of the main form, but get the same result. Can anyone tell me what am I doing wrong?

Thanks,
Pam

 

[Mike375]

=Sum([FieldName]) in the footer of the subform should give the total for the records on the subform for that main record.

However, if you opened the subform directlty from the data base windown then it would give the total for all the records in the underlying table or query.

 

[boblarson]

What exactly is your formula?

 

[PamelaJoy]

=[RawMaterialPercent]*[LbsPerGal] is the formula on the subform (% of this ingredient times its density) and =Sum([RawMaterialPercent]*[LbsPerGal]) is the calculation on the subform.

It doesn't seem to make any difference whether I view the record from the main form or open the subform by itself.

Pam

 

[boblarson]

1. You must make sure your text boxes aren't named "RawMaterialPercent" and/or "LbsPerGal"

2. If they are, rename them to txtRawMaterialPercent and txtLbsPerGal

3. and then make sure the calculation uses the FIELD names.

 

[PamelaJoy]

I added 'txt' to the names but the results are the same. I have attached my db in case that will help. The issue is with the 'frmAddFinishedProduct' form and 'subfrmAddRecipe' subform.
Thanks,
Pam

 

[boblarson]

Okay -

1. Your subform was not linked to your main form.

2. Your reference to the formula for the text box on the main form should be
=Subform1.Form!Text17

 

[PamelaJoy]

I changed the formula reference as you suggested, but the results are still the same.

I discovered I now have more problems than I thought - I can no longer add items using the subform. It is 'locked in place' on a single ingredient. It's amazing (and terribly frustrating) how solving one issue sometimes creates a new one where problems had not existed before.

 

[boblarson]

I changed the formula reference as you suggested, but the results are still the same.

I discovered I now have more problems than I thought - I can no longer add items using the subform. It is 'locked in place' on a single ingredient. It's amazing (and terribly frustrating) how solving one issue sometimes creates a new one where problems had not existed before.

You didn't by chance do the other half of the equation -

1. Your subform was not linked to your main form

You need to link your subform to your main form.

 

[Watergirl]

No, I did not do anything else but change the names. I thought the 'Subform1' portion of your 2. was telling me how to link them/direct the main form to the subform. When I created the subform, I 'dragged it' on to the main form, and assumed that the link between them was created that way. Obvious not - please explain how to link the two forms?
I have two other questions now, if you would be so kind:
1. In trying to solve my original problem by looking through other posts on this site, it is clear that you (and some others) don't recommend creating anything but the most element forms using the 'wizard'. May I ask why?
2. I think I will begin again with my form design - I've learned many things I didn't know when I started creating the form - and think I should base the form and subform on queries. In that query, can I create a field not in the table(s) that will do the multiplication as described above, and then bring it into the subform? Or does that need to be done outside of the query and directly on the subform since the information needed won't be supplied until the form is populated?
Thank you so much for your help. I feel like I take two steps forward and one step back. And sometimes one step forward and two steps back!

 

[PamelaJoy]

Thank you, Bob Larson. I was able to figure out how to link the subform to the form and it WORKS!!