Sub Form Calculation

[FranD]

I'm trying to add a calculated control to a form which includes 4 fields from my main form and one from a sub form. I can't figure out how to refer to the sub form control in the expression - here's what I've tried:

[Field1]+[Field2]-[Field3]+[Field4]-Forms![frmMainFormName]!subFormName.Form![Control Name on subForm].

I've used the above syntax in the past to refer to subform controls - but only in modules. Is there a different syntax for expressions. I'm a serious novice - so please be specific in your response......thanks!!!!

 

[RobinL]

have you tried refering to it directly?

e.g.

Forms!subformname![control on subform]

*im not sure of this, but its the first thing i'd try*

 

[MikeAngelastro]

Try adding a footer to the subform. Add a textbox to it and make it equal to the sum of the fields you want. On the main form add another textbox and make it equal to the subform's suming text box as suggested by RobinL.

 

[FranD]

Thanks Mike,

I tried what you suggested, but it's still not working. In the text box on the main form, which refers to the calculated total (in the footer of the subform) I get a "#Name?" error - and in the text box which includes that text box in the grand total calculation I get an "#Error" message.

Any suggestions......

Thanks again

 

[MikeAngelastro]

Are you using an "=" sign as below?

=Me.[Subform Control Name].[Subform Field]

Remember you must use the name of the subform control, which the container for the subform, and not the subform itself.

 

[FranD]

But where does the "=me.SubForm Control.SubForm Field" go? As you suggested in your first reply, I added a text box to the subform in the form footer and simply calculated a sum of the field I wanted. I named that calculated control "totalRemoved."

I then added a text box to the main form and in the 'Default Value' property setting I put the following "=Forms!subFormName!totalRemoved." I named this field "totalRemoved_MainFrm".

I then modified the calculated control - also on the main form - which sums 4 fields on the main form and the value from the text box which refers back to the subform "totalRemoved". That formula looks like this

[Field1]+[Field2]-[Field3]+[Field4]-[totalRemoved_MainFrm."

I hope this makes sense. It's really drving me crazy. I appreciate all your help.

 

[MikeAngelastro]

You put it the Control Source to be found in the data tab of the textbox properties.