Gaurd against subforms that have no data

[Guest]

I have a text box which pulls a value from a subform named claims. This is
used to calculate currency amounts. At the moment if the subform has no data,
the text box shows error!! and the following sums do not add up. the code in
the text box is =(Claims.Form!sumClaimed)

Can anyone help me solve this??? (Probably an easy solution)

 

[Roger Carlson]

What about using the Nz (null to zero) function:
=Nz(Claims.Form!sumClaimed)

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Dirk Goldgar]

I have a text box which pulls a value from a subform named claims.
This is used to calculate currency amounts. At the moment if the
subform has no data, the text box shows error!! and the following
sums do not add up. the code in the text box is
=(Claims.Form!sumClaimed)

Can anyone help me solve this??? (Probably an easy solution)

Try Keri Hardwick's nnz() function. See this link for the code and
explanation:

http://www.mvps.org/access/forms/frm0022.htm
Forms: #Error when the Subform has no records

 

[Guest]

i have tried using this nnz function but still can not get this to work, also
i have more than one text box returning this error. Do i place this function
in the on error event of the main form. Can anyone help me with using the
function or is there another way of doing this

 

[Dirk Goldgar]

i have tried using this nnz function but still can not get this to
work, also i have more than one text box returning this error. Do i
place this function in the on error event of the main form. Can
anyone help me with using the function or is there another way of
doing this

Having saved the function in a standard module, you just wrap the
controlsource expression of your text box -- the one on the main form --
with a call to that function. Where before you had this:

=(Claims.Form!sumClaimed)

Now you have this:

=nnz(Claims.Form!sumClaimed)

 

[Guest]

I have tried =nnz(Claims.Form!sumClaimed) using the function nnz in the
module nnz but this still wont work for me. Now i am receiving the error
#Name?

These text boxes just dont seem to want to give a zero value for some reason

 

[Dirk Goldgar]

I have tried =nnz(Claims.Form!sumClaimed) using the function nnz in
the module nnz but this still wont work for me. Now i am receiving
the error #Name?

These text boxes just dont seem to want to give a zero value for some
reason

Cantankerous creatures, aren't they? <g>

If you named the module "nnz", that's the source of your #Name? error.
The module can't have the same name as the function, or Access won't
know which of these two public objects named "nnz" you are referring to.
Give the module a different name. I suggest "basUtilities" or
"modUtilities", with the idea that you may put other utility functions
into this same module later on. The "bas" prefix would indicate (to me)
that this is a Basic-language module, while the "mod" prefix would
indicate simply that this is a module.

 

[Guest]

I have finally got the code to work for me , the problem was the name of the
module so when i changed it to basUtilities it gave me 0 in the text box.

Thanks alot Dirk i really appreciate the help with this problem