Problem with subform control value when main form first opened

[Karen]

I have a main form that has a number of subforms, and I get an #Error
in one of the subform controls ONLY when the main form is first opened
and ONLY if the particular control on another subform that's the source
for this subform's control contains a null.

Here's the situation. Subform A contains a textbox control called
"FundingMethod" that gets its value from a table field. Subform B,
located on the second tab of a tab control on the Main_Form and based
on a different table, has some calculated controls that depend on the
value in the "FundingMethod" field. Subform B has a textbox control
that gets its value from the FundingMethod control of Subform A. The
control source for this textbox looks like this:

=IIf(IsNull(Forms!Main_Form!SubformA!FundingMethod),"unknown",Forms!Mai­n_Form
SubformA!FundingMethod)

If the field contains a null, I expect the control on Subform B to
contain "unknown", otherwise the text value of the FundingMethod field.


But when the Main_Form is first opened AND the FundingMethod field is
null, #Error is displayed in the textbox control of Subform B, and the
calculated fields then aren't calculated correctly. If I manually
Refresh the record, the control updates correctly. If I bring up
another record, the control displays correctly. If I navigate back to
the original record, the control displays correctly. Most puzzling, if
the first record displayed when the Main_Form opens contains text
rather than a null, the control displays correctly.

How can I force the control to display correctly first time, every
time?

TIA!
Karen

 

[Jeff L]

In your statement, in the False condition you don't have a ! after
Main_Form.
If that's not the issue you could do a refresh of the subform when the
form is opened.
Me.SubformB.Refresh

 

[Karen]

Oops! The missing bang happened when I retyped in my post -- it's there
in Access.

I tried adding a Requery of the subform to the OnOpen, the OnLoad, and
the OnCurrent events of the main form, to no avail. I did fix the
problem when I added it to the OnChange event of the tab control where
Subform B resides. It seems excessive to have to requery this subform
every time the user moves to a different tab, but at least it's working
now. Thanks!

Karen