Empty Subform = Persistent #Error in TextBox

J

Jay

Given:
An invisible textbox control (SubTBox) in the form footer of a subform sums
the values in Field07. A textbox control (ParentTBox) displays the value in
SubTBox whenever SubTBox changes.

Problem:
When there is no data on the subform, SubTBox returns #Error which is
displayed in ParentTBox. I have not been successful at handling the #Error
value and converting it to a more friendly indication (like displaying a
blank in ParentTBox instead of #Error).

Any modification of the formula in the controlsource property of ParentTBox
also results in #Error. The #Error value returned in SubTBox creates an
error in any formula that references it. Handling with
"=iif(IsError(SubTBox),"",SubTBox)" or other null/error functions still
produces #Error in ParentTBox.

Is there a best practice (or any work-around) to handle this common '#Error'
result ?

Note: The following artcle describes causes of #Error and resolutions for a
variety of situations, but it does not provide a final solution (or any
productive leads) for resolving #Error in a calculated control that depends
on form data from an empty form.

http://office.microsoft.com/en-us/access/HA011814481033.aspx?pid=CL100570041033
 
M

Marshall Barton

Jay said:
Given:
An invisible textbox control (SubTBox) in the form footer of a subform sums
the values in Field07. A textbox control (ParentTBox) displays the value in
SubTBox whenever SubTBox changes.

Problem:
When there is no data on the subform, SubTBox returns #Error which is
displayed in ParentTBox. I have not been successful at handling the #Error
value and converting it to a more friendly indication (like displaying a
blank in ParentTBox instead of #Error).

Any modification of the formula in the controlsource property of ParentTBox
also results in #Error. The #Error value returned in SubTBox creates an
error in any formula that references it. Handling with
"=iif(IsError(SubTBox),"",SubTBox)" or other null/error functions still
produces #Error in ParentTBox.
Click to expand...


I would expect that to return either #Name or #Error whether
there was data in the subform or not. If it is actually
displaying a value when the subform has data, then I will
place a big bet that the main form's text box expression is
something other than what you posted. If that's correct,
please don't waste your and our time by retyping what you
post. Use Copy/Paste so we don't end up debugging any
typos.

The main form text box expression must reference the subform
text box by going through the subform **control** that is
displaying the (sub)form object:

=IIf(IsError(subformcontrol.Form.SubTBox),"",subformcontrol.Form.SubTBox)

Normally, a zero or Null is preferable over a ZLS.
 
J

Jay

Hi Marshall -

Your solution worked perfectly. I was mislead by the fact that, when
records are present on the subform, its unnecessary to specify the subform
control as part of the qualifier (the proper value is returned without
referencing the subform control). I incorrectly assumed that the IsError
function would likewise be fed the #Error value in SubTBox. Might the the
take-home message of this post be that functions require more rigorous
qualifiers?

I apologize for concepturally paraphrasing the expression I provided in my
previous post; I got lazy and fired it off too quickly. Won't happen again.
 
M

Marshall Barton

Jay said:
Your solution worked perfectly. I was mislead by the fact that, when
records are present on the subform, its unnecessary to specify the subform
control as part of the qualifier (the proper value is returned without
referencing the subform control). I incorrectly assumed that the IsError
function would likewise be fed the #Error value in SubTBox. Might the the
take-home message of this post be that functions require more rigorous
qualifiers?

I apologize for concepturally paraphrasing the expression I provided in my
previous post; I got lazy and fired it off too quickly. Won't happen again.
Click to expand...


No apology necessary, just live and learn ;-)

Note that the subform control name is required in a
reference to a subform control in all situations. If it
worked before, then I have to assume you have a main form
text box named SubTBox, perhaps left over from an earlier
experiment??
 
J

Jay

Marshall -

Not exactly left over from an earlier experiment, but your assumption was on
target again. This is the formula (copied!) from the parent form textbox
control source. It DOES include a reference to the subform control in the
qualifier; I thought it did not.

=Val(Job_Expenses!Expenses_CostSubTotal)

I'm new enough at Access VBA that I'm still working on how to qualify an
identifier in a standard way (when to use '!' and when to use '.[Form].'). I
suppose it's context sensitive and it will become clear through repeated
exposure. Are you aware of any rigorous reference that clarifies the
standards or am I making too much of this ?
 
G

GW

Try IIf IsNumeric, I have used something like

=(IIf(IsNumeric([InvoiceHoursTotalQuery subform].Form!InvoiceHoursTotal),([InvoiceHoursTotalQuery subform].Form!InvoiceHoursTotal),0))

EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com
 
Top