problem with subform control references

[Guest]

My form consists of a main form and two linked, embedded subforms. The main
form source is the employee master, the first subform ("emply_bal_subform")
source is a by-year summary of allowed absences (vacation, etc), and the
second subform shows current year absences. I've placed calculated text
boxes on subform 1 to show summary activity for the current year and balance
of unused allowances by category. The problem:
The dsum funciton entered as a record source on subform 1 returns only a
"Name?" error. The exact same expression placed on a command button on the
main form works as intended. The expression as entered:

= DSum("[hours]", "absences", "[reasonCode] = 2 AND [Year] = " &
Emply_bal_Subform.Controls![Year] & " AND [emp_id] = " &
Forms!fmEmpAbsHdr.Controls!Emp_ID)

The addition of some modifiers in the expression may be unnecessary but I
was trying everything starting with the usual "me" reference, and, as
indicated, this does work when called from a command button on the form.
I've also tested the string returned by the WHERE portion of the expression.
Why doesn't it work on the subform control? Can you suggest another approach?

DM

 

[Douglas J. Steele]

To refer to a control on a subform from the parent form, you use:

Forms!NameOfParentForm!NameOfSubformControl.Form!NameOfControlOnSubform

(You can also use Me!NameOfSubformControl.Form!NameOfControlOnSubform from
the parent form's class module)

Note that depending on how you added the subform to the parent form, the
name of the subform control may not be the same as the name of the form
being used as a subform. You must use the name of the control.