Update controls on main form from on changed values of the sub

[Ladi]

Dear friends and Pros

I'm trying to update a control in the main form with the Sum of the values
in the subform containing columns with numbers.
There is already the relationship between main table and child table, also
between the main form and sub.
The following function would update only one control on the first record.
I have Tables:
MainTab
ChildTabName
Forms:
FormName
SubformName
-------------------------------------------------
Form_ControlName= DSum("[ChildTabName]![ColName]", "ChildTabName",
"[MainID]=[ChildTabName]![MainTabID] And [ChildTabName]![ChildTabID]=
[SubformName].Form![ChildTabID] ")

Thanks in advance,
Ladi

 

[Allen Browne]

In a word, Don't.

Unless there are cases where the sum of values field in the main table
should validly be different from actual the sum of values in the related
table, you must not store this value.

If you want to do it anyway, use the AfterUpate and AfterDelConfirm events
of the subform to write the sum back to the parent form. The code will look
something like this:

Me.Parent![SomeControlName] = DSum("[ColName]", "ChildTabName", _
"[ChildTabID] = " & [ChildTabID])

If the field ChildTabID is a Text field, you need extra quotes:
Me.Parent![SomeControlName] = DSum("[ColName]", "ChildTabName", _
"[ChildTabID] = """ & [ChildTabID]) & """"