Run a query on a form

[Bonnie]

Hi,

I have a form with a subform. The subform may have multiple related records
with dollar amounts. I would like to key a total on the form (after
inputting multiple related records on the subform and returning to the form)
that validates against a calculation of all the related records on the
subform.

I thought I could key the total in and on exiting the control, a query that
sums the related records (the field is ItemTotal) could be run. If there
isn't a match I can pop up a MsgBox.

My question is: what's the best way to run the total?

Thanks,

Bonnie

 

[Arvin Meyer MVP]

Normally you calculate subform totals in a subform foot (which can be hidden
by setting both the textbox and footer to zero (0") height.

Set the controlsource of the hidden textbox to:

= Sum[Item])

Item being the name of the field in the detail section of the subform that
you are looking to total. Now if you want a textbox on the main form to
display that total, just set it equal to the name of the hidden textbox in
the subform's footer:

= [Forms[![FormName]![SubformControlName].Form![TextboxName]

 

[Bonnie]

Thanks very much Arvin. I believe this will do the trick!

Bonnie
http://www.dataentry-documentscanning.com

Normally you calculate subform totals in a subform foot (which can be hidden
by setting both the textbox and footer to zero (0") height.

Set the controlsource of the hidden textbox to:

= Sum[Item])

Item being the name of the field in the detail section of the subform that
you are looking to total. Now if you want a textbox on the main form to
display that total, just set it equal to the name of the hidden textbox in
the subform's footer:

= [Forms[![FormName]![SubformControlName].Form![TextboxName]

--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Hi,

I have a form with a subform. The subform may have multiple related
records
with dollar amounts. I would like to key a total on the form (after
inputting multiple related records on the subform and returning to the
form)
that validates against a calculation of all the related records on the
subform.

I thought I could key the total in and on exiting the control, a query
that
sums the related records (the field is ItemTotal) could be run. If there
isn't a match I can pop up a MsgBox.

My question is: what's the best way to run the total?

Thanks,

Bonnie