Empty Subform = Persistent #Error in TextBox

[Guest]

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

 

[Marshall Barton]

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.

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.

 

[Guest]

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.

 

[Marshall Barton]

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.

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??

 

[Guest]

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 ?

 

[Marshall Barton]

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 ?

More confusion caused by two different things with the same
name. This one doesn't look like it serves a useful purpose
and should probably be deleted.

When you have an object that is related to a form object,
then use the .Form property to get from the former to the
latter. Some examples might help.

You can refer to an open form through the Forms collection.
Forms!formname is a form object so using the Form property
is at best redundant.

A subform control on a main form is referenced the same as
any other control. E.g. you can make a subform invisible
by using Forms!formname.subformcontrol.Visible = False
OTOH, to get to a control in a subform, you need to go
through the subform control to get to the displayed form
object, Forms!formname.subformcontrol.Form.textbox.Visible =
False.

I don't think I'm explaining this very well. Try googling
for terms like Access Subform Control for lots of other
explanations. For discussions about when to use ! and .
I googled for Access Bang Dot and the first of a million
hits was:
http://blogs.msdn.com/frice/archive/2004/02/18/75685.aspx

 

[tina]

I don't think I'm explaining this very well.

well, i think you did, Marsh. subforms are easy to explain when you're
sitting beside somebody, pointing and waving your arms (that's how i do it,
anyway <g>), but awful to try to explain in print.

i think the hardest thing for people to "get" is that the subform and the
container control are two different objects. in case it might help, the
SubformControlName link at
http://home.att.net/~california.db/instructions.html tries to illustrate
that point.

hth

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 ?

More confusion caused by two different things with the same
name. This one doesn't look like it serves a useful purpose
and should probably be deleted.

When you have an object that is related to a form object,
then use the .Form property to get from the former to the
latter. Some examples might help.

You can refer to an open form through the Forms collection.
Forms!formname is a form object so using the Form property
is at best redundant.

A subform control on a main form is referenced the same as
any other control. E.g. you can make a subform invisible
by using Forms!formname.subformcontrol.Visible = False
OTOH, to get to a control in a subform, you need to go
through the subform control to get to the displayed form
object, Forms!formname.subformcontrol.Form.textbox.Visible =
False.

I don't think I'm explaining this very well. Try googling
for terms like Access Subform Control for lots of other
explanations. For discussions about when to use ! and .
I googled for Access Bang Dot and the first of a million
hits was:
http://blogs.msdn.com/frice/archive/2004/02/18/75685.aspx

 

[Guest]

Marshall -

Thanks for your time, persistence, and leads.

The dialog's been on target, concise, and truly helpful. That's a wrap!
--
Thanks again,
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 ?

More confusion caused by two different things with the same
name. This one doesn't look like it serves a useful purpose
and should probably be deleted.

When you have an object that is related to a form object,
then use the .Form property to get from the former to the
latter. Some examples might help.

You can refer to an open form through the Forms collection.
Forms!formname is a form object so using the Form property
is at best redundant.

A subform control on a main form is referenced the same as
any other control. E.g. you can make a subform invisible
by using Forms!formname.subformcontrol.Visible = False
OTOH, to get to a control in a subform, you need to go
through the subform control to get to the displayed form
object, Forms!formname.subformcontrol.Form.textbox.Visible =
False.

I don't think I'm explaining this very well. Try googling
for terms like Access Subform Control for lots of other
explanations. For discussions about when to use ! and .
I googled for Access Bang Dot and the first of a million
hits was:
http://blogs.msdn.com/frice/archive/2004/02/18/75685.aspx

 

[Guest]

Tina -

Thanks for jumping in. I've read a lot about subform controls and
referencing them lately, but the link you provided is the best I've seen
describing the separation of the control and its source object - it doesn't
get any clearer than that.

--
Thank you,
Jay

I don't think I'm explaining this very well.

well, i think you did, Marsh. subforms are easy to explain when you're
sitting beside somebody, pointing and waving your arms (that's how i do it,
anyway <g>), but awful to try to explain in print.

i think the hardest thing for people to "get" is that the subform and the
container control are two different objects. in case it might help, the
SubformControlName link at
http://home.att.net/~california.db/instructions.html tries to illustrate
that point.

hth

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 ?

More confusion caused by two different things with the same
name. This one doesn't look like it serves a useful purpose
and should probably be deleted.

When you have an object that is related to a form object,
then use the .Form property to get from the former to the
latter. Some examples might help.

You can refer to an open form through the Forms collection.
Forms!formname is a form object so using the Form property
is at best redundant.

A subform control on a main form is referenced the same as
any other control. E.g. you can make a subform invisible
by using Forms!formname.subformcontrol.Visible = False
OTOH, to get to a control in a subform, you need to go
through the subform control to get to the displayed form
object, Forms!formname.subformcontrol.Form.textbox.Visible =
False.

I don't think I'm explaining this very well. Try googling
for terms like Access Subform Control for lots of other
explanations. For discussions about when to use ! and .
I googled for Access Bang Dot and the first of a million
hits was:
http://blogs.msdn.com/frice/archive/2004/02/18/75685.aspx

 

[tina]

you're very welcome, Jay, i'm glad it helped.

Tina -

Thanks for jumping in. I've read a lot about subform controls and
referencing them lately, but the link you provided is the best I've seen
describing the separation of the control and its source object - it doesn't
get any clearer than that.

--
Thank you,
Jay

I don't think I'm explaining this very well.

well, i think you did, Marsh. subforms are easy to explain when you're
sitting beside somebody, pointing and waving your arms (that's how i do it,
anyway <g>), but awful to try to explain in print.

i think the hardest thing for people to "get" is that the subform and the
container control are two different objects. in case it might help, the
SubformControlName link at
http://home.att.net/~california.db/instructions.html tries to illustrate
that point.

hth

Jay wrote:

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 ?


More confusion caused by two different things with the same
name. This one doesn't look like it serves a useful purpose
and should probably be deleted.

When you have an object that is related to a form object,
then use the .Form property to get from the former to the
latter. Some examples might help.

You can refer to an open form through the Forms collection.
Forms!formname is a form object so using the Form property
is at best redundant.

A subform control on a main form is referenced the same as
any other control. E.g. you can make a subform invisible
by using Forms!formname.subformcontrol.Visible = False
OTOH, to get to a control in a subform, you need to go
through the subform control to get to the displayed form
object, Forms!formname.subformcontrol.Form.textbox.Visible =
False.

I don't think I'm explaining this very well. Try googling
for terms like Access Subform Control for lots of other
explanations. For discussions about when to use ! and .
I googled for Access Bang Dot and the first of a million
hits was:
http://blogs.msdn.com/frice/archive/2004/02/18/75685.aspx

 

[Marshall Barton]

Thanks for the words of encouragement tina ;-)

I guess the only(?) siimple(?) topic that's more difficult
to explain is the rules for quoted quotes ;-\

I think your link should be a big help. Actually, I think
the entire site is a great starting point for beginners.
--
Marsh

I don't think I'm explaining this very well.

well, i think you did, Marsh. subforms are easy to explain when you're
sitting beside somebody, pointing and waving your arms (that's how i do it,
anyway <g>), but awful to try to explain in print.

i think the hardest thing for people to "get" is that the subform and the
container control are two different objects. in case it might help, the
SubformControlName link at
http://home.att.net/~california.db/instructions.html tries to illustrate
that point.

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 ?

More confusion caused by two different things with the same
name. This one doesn't look like it serves a useful purpose
and should probably be deleted.

When you have an object that is related to a form object,
then use the .Form property to get from the former to the
latter. Some examples might help.

You can refer to an open form through the Forms collection.
Forms!formname is a form object so using the Form property
is at best redundant.

A subform control on a main form is referenced the same as
any other control. E.g. you can make a subform invisible
by using Forms!formname.subformcontrol.Visible = False
OTOH, to get to a control in a subform, you need to go
through the subform control to get to the displayed form
object, Forms!formname.subformcontrol.Form.textbox.Visible =
False.

I don't think I'm explaining this very well. Try googling
for terms like Access Subform Control for lots of other
explanations. For discussions about when to use ! and .
I googled for Access Bang Dot and the first of a million
hits was:
http://blogs.msdn.com/frice/archive/2004/02/18/75685.aspx

 

[tina]

Thanks for the words of encouragement tina ;-)

I guess the only(?) siimple(?) topic that's more difficult
to explain is the rules for quoted quotes ;-\
lol


I think your link should be a big help. Actually, I think
the entire site is a great starting point for beginners.

thank you, Marsh!

 

[John W. Vinson]

I guess the only(?) siimple(?) topic that's more difficult
to explain is the rules for quoted quotes ;-\

oh... so saying "to include a doublequote in a doublequote delimited
string you double the doublequote" sounds like doubletalk???

<g>

John W. Vinson [MVP]

 

[Marshall Barton]

OTOH, to say "to include a singlequote in a singlequote
delimited string you double the singlequote" is singularly
not doubletalk. ;-)

Definitely a case where examples make the point better than
words alone.

 

[tina]

ROFL

OTOH, to say "to include a singlequote in a singlequote
delimited string you double the singlequote" is singularly
not doubletalk. ;-)

Definitely a case where examples make the point better than
words alone.

 

[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