#Error in subform textbox when main form first opened

[Karen]

I'm new to Microsoft Access, and I've been having a few frustrating
problems. Today I'm wrestling with a main form that has a number of
subforms and an #Error that appears in one of the subform controls ONLY
when the main form is first opened and ONLY if a particular control
contains a null.

Here's the situation. One subform, called "Info_Subform" that is based
on a table called "Info", contains a textbox control called
"FundingMethod". Another subform --called "Finance_Subform", based on a
table called "Finance", and located on the second tab of a tab control
on the Main_Form -- has some calculated controls that depend on the
value in the "FundingMethod" field. In Finance_Subform I have a textbox
control that gets its value from the FundingMethod control of the other
subform, Info_Subform. The control source for this textbox looks like
this:

=IIf(IsNull(Forms!Main_Form!Info_Subform!FundingMethod),"unknown",Forms!Main_Form!Info_Subform!FundingMethod)

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

The problem is that when the Main_Form is first opened AND the
FundingMethod field is null, #Error is displayed on Finance_Subform in
the textbox control. 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. 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?

 

[Guest]

Hi Karen,

I'd try using me.recalc in the open_form event code - this forces all the
calculated fields to be recalculated and may persuade Access to behave.

I had a similar problem myself a little while ago. The field in question
was a combox with a slightly expensive select in it's rowsource. Luckily for
me the problem went away when the users equipment was upgraded. Good Luck!

 

[Karen]

Thanks, John! Nothing in the OnOpen event helped, but I'm requerying
now using the OnChange event of the tab control where the subform
resides, and that seems to be working.

Karen