#error propogation

[JimS]

On a subform, I have a textbox that sums a column. I don't display it, just
reference it.

When the subform recordset is empty, it propogates "#error" to all the text
boxes that reference it. How do I get it to calculate out to a zero or null
instead of #error?

subform text box control source: =Sum(-nz([Value],0))

I know, "Value" is a bad choice for a column name. I'll fix it later.

Jim

 

[Jeanette Cunningham]

Jim,
have a look at this page on the access web.

http://www.mvps.org/access/forms/frm0022.htm


Jeanette Cunningham

 

[JimS]

Thank you, Jeanette. I bludgeoned it to death by setting the text boxes that
refer to the subform as follows:

=IIf(sfmAllPCardTab.Form.recordset.RecordCount>0,sfmAllPCardTab.Form!PCARD,0)

Not at all elegant,but it worked. I appreciate your reply, which itself is
not particularly elegant, but has the advantage of having been tested.
Thanks again.
--
Jim

Jim,
have a look at this page on the access web.

http://www.mvps.org/access/forms/frm0022.htm


Jeanette Cunningham

On a subform, I have a textbox that sums a column. I don't display it,
just
reference it.

When the subform recordset is empty, it propogates "#error" to all the
text
boxes that reference it. How do I get it to calculate out to a zero or
null
instead of #error?

subform text box control source: =Sum(-nz([Value],0))

I know, "Value" is a bad choice for a column name. I'll fix it later.

Jim

 

[Jeanette Cunningham]

Jim,
the advantage of using the Nnz function is that you can save it as a public
sub in a module and easily call it to use on any form.
It is a lot less typing (bludgeoning to death!).


Jeanette Cunningham

Thank you, Jeanette. I bludgeoned it to death by setting the text boxes
that
refer to the subform as follows:

=IIf(sfmAllPCardTab.Form.recordset.RecordCount>0,sfmAllPCardTab.Form!PCARD,0)

Not at all elegant,but it worked. I appreciate your reply, which itself is
not particularly elegant, but has the advantage of having been tested.
Thanks again.
--
Jim

Jim,
have a look at this page on the access web.

http://www.mvps.org/access/forms/frm0022.htm


Jeanette Cunningham

On a subform, I have a textbox that sums a column. I don't display it,
just
reference it.

When the subform recordset is empty, it propogates "#error" to all the
text
boxes that reference it. How do I get it to calculate out to a zero or
null
instead of #error?

subform text box control source: =Sum(-nz([Value],0))

I know, "Value" is a bad choice for a column name. I'll fix it later.

Jim