Form Subform Error

[Mary Ann]

I have a main form with 4 subforms. One of the subforms reflects
other costs/miscellaneous charges that an account might be assessed.
The main form includes a field with a total pulled from that other
cost subform. The total field reflects the correct amount as long as
there is at least one record for the account in the other cost
subform. However, many accounts won't have other costs assessed so
those accounts are not in the query for the subform. If there are no
other costs for that account, the total field on the form returns an
#Error message. I made the total fields on both the main form and
subform 0 if the answer is null and still get an error message on the
main form. What is the best way to resolve this? Is there a code
that says if the answer is #Error, return 0 or Null?

Thanks so much!

[Allen Browne]

If a form has no records - not even the new row where one can be added - the
detail section appears completely blank. Attempting to refer to the control
the "doesn't exist" then generates an error.

You can avoid the error by testing IsError(), or by checking the number of
records.

Example:
=IIf(IsError([MySub].[Form]![MyTotal], 0, Nz([MySub].[Form]![MyTotal], 0))

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html

"Mary Ann" <it*@itmservice s.com> wrote in message
news:17******** *************** **@posting.goog le.com...

I have a main form with 4 subforms. One of the subforms reflects
other costs/miscellaneous charges that an account might be assessed.
The main form includes a field with a total pulled from that other
cost subform. The total field reflects the correct amount as long as
there is at least one record for the account in the other cost
subform. However, many accounts won't have other costs assessed so
those accounts are not in the query for the subform. If there are no
other costs for that account, the total field on the form returns an
#Error message. I made the total fields on both the main form and
subform 0 if the answer is null and still get an error message on the
main form. What is the best way to resolve this? Is there a code
that says if the answer is #Error, return 0 or Null?

Thanks so much!