Sum based on subforms

[f123 via AccessMonster.com]

i have a main form containing two subforms (Activity&ActivityContest), where
its a One to Many relationship between Activity&ActivityContest.
Example:
Activity
ID ActivityBudegt
1 1000
2 500

ActivityConstist
ID ActivityID ActivityContistBudget
1 1 700
2 1 300
3 2 500
....

the question is,ActivityBusget is the total sum of ActivityContistBudget for
each ActivityID
i tried many ways but doesnt work with me

any help appreciated

 

[KARL DEWEY]

You main form needs a totals query to sum the data from the table that has
the data shown in the subform.

 

[f123 via AccessMonster.com]

appreciate your feedback DEWEY, but i didnot figure out what you mean!!

 

[Bob H]

You need to build or create a query based on the information in your
table, and then in design mode click the 'Totals' button and group the
feild you want to total by 'sum'.

 

[Bob H]

You need to build or create a query based on the information in your
table, and then in design mode click the 'Totals' button and group the
feild you want to total by 'sum'.

 

[rolaaus]

I'm completely willing to admit if I'm wrong, but if f123 did what you
suggested (Bob and Karl), wouldn't it make the main form un-editable?

f123, if you're up to it, I would recommend using an unbound field, and
write a VBA module that updates the unbound field with the total. For one
thing, your total shouldn't be editable, which means you can change the
editable property of that field to false.

The VBA module would look something like
dim rsRecord as currentdb.openrecordset("Select sum(calculatedFieldName)
from SomeTable WHERE KeyValue = " & me.KeyFieldName

me.txtCalculatedValue.text = rsRecord("CalculatedFieldName")

(some minor tweaking required, but this gives you a start)

This module would go in the OnCurrent event procedure.