Calculate values on a form from another form?

[BLFOSTER]

I have a main form with subform. I have several expressions on the subform to calculate and count etc. I need to move the calculations to a pop-up form as the mainform/subform is getting hung up when the form is re-queried. This mainform/subform is always open. Here is what I have tried and the only thing I get is #error.



=Sum(IIf([Forms]![OpenMainForm]![OpenSubForm].[Form]![MyField]="MyCriteria",1,0))

Is this even possible to do?

Thank you!

[June7]

Cannot use aggregate calcs on one form to summarize data on another form. Do the sum on the form that has the data. Other form can reference the textbox with the sum calc.

Why should mainform/subform get hung up?

[BLFOSTER]

Subform is a continuous form that is re-queried frequently to show a different dataset. Each time the form is re-queried the calculated text boxes re-calculate on the new dataset. This all works fine, until the user wants to use an "alpha find" button to locate a student. I have a button down one side for each letter of the alphabet that when pushed, returns all students in the database whose last name begins with the corresponding button. It is a very large dataset and my calculated text boxes are counting and averaging whatever set of data is brought back. This is where things get hung up, and cursor spins and spins, screen goes opaque, "Access is not responding", until the calculations are complete. I do not need to see the calculations on "all students whose last name begins with A" (for example), but I do need to see the calculations for all records in June or April say (when these buttons are pushed - which brings back small datasets and works fine), so that is why I was trying to put the calculations on a pop-up accessed by pushing a button. It is simple to refer to the boxes on the mainform/subform from the pop-up but this does not solve the problem. When I built the form, the idea behind the calculating text boxes was to be able to provide numbers at a glance when requested by management. Thanks for letting me know it cannot be done before I spent another day trying to figure it out!!!!

[June7]

Option is to use code to set the ControlSource property of those textboxes before the form is requeried or refreshed. For the student search set the property to nothing - empty string.

For other searches, set the property back to the expression.