Form/Subform Total Issue

[Jkittle]

Is it possible to take a number in a main form (stored in a table) and subtract a number in sub form (stored in a separate table) and if so how would you do it? I've tried a couple of ways and have had no success.

Jerry

 

[gemma-the-husky]

an easy way is to put a field on the main form, that reads its value from the subform

ie in the control put source =subformname!subformfield

you can make this hidden, and then you should be able to do what you want

 

[mdschuetz]

Is it possible to take a number in a main form (stored in a table) and subtract a number in sub form (stored in a separate table) and if so how would you do it? I've tried a couple of ways and have had no success.

Jerry

If you use the expression builder on the Object you're wanting to display the results in, then yes.

Example.

Form Objects.

txtResult (textbox where the calculation will occur)
txtValue1 (textbox where a numeric value is stored)

Subform Objects.

txtValue2 (textbox where a numeric value is stored)

In Design Mode, right click on txtResult, right click Properties, select the Data Tab, click in the Control Source field, click the 3 dots ( ... ) to the right, double click the folder with the + in it that is named after the form you are in.

You'll now see your form and its associated subform.

Select the control you wish to subtract from and double click it.
Now select the subform folder under your form folder on the left.
Select the control who's value you inted to subtract from the control on your main form and double click it.

Click Ok

That should do it.

= [txtValue1] - [SubFormName].Form![txtValue2] is what you should see.

 

[Jkittle]

Form/Sub Form totals

Okay I've run into another problem with this. What I have done is linked my sub form to a key field in the main form and the sub form is a continuous form.

So the main form contains a field "parts on hold" which is a total.

The sub form contains the field "parts sorted" that may have multiple entries in the field on different dates until the sort in completed.

So how would I total the "parts sorted" field and then subtract the amount from "parts on hold" (See Disposition Decision Form in attached DB)

 

[mdschuetz]

what version of access is this? it is appearing corrupt and won't open because of a switchboard error

 

[Jkittle]

2003. Try it now.