Adding subform totals if one subform is Null

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

JJF - 14 Mar 2007 16:25 GMT

I have a main form (supermarket order) with three subforms (meat, groceries,
produce). Each subform multiplies [quanty] * [price] and sums it's catagory.
The main form totals the three subforms. All is fine until the customer
doesn't order from all three catagories. I get #ERROR. Any help?

Reply to this Message

Allen Browne - 14 Mar 2007 16:50 GMT

That should work unless the subform's Detail section goes completely blank
when there are no records.

IF that happens, you will need to test whether it has records, so instead
of:
=[Sub1].[Form].[txtTotal]
you need:
=IIf([Sub1].[Form].[RecordsetClone].[RecordCount] = 0, 0,
Nz([Sub1].[Form],0))

Unfortunately, that expression fails in Access 2007. Post back if you are
using the new version.

For an explanation of why the form goes blank, see:
http://allenbrowne.com/casu-20.html

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

>I have a main form (supermarket order) with three subforms (meat,
>groceries,
> produce). Each subform multiplies [quanty] * [price] and sums it's
> catagory.
> The main form totals the three subforms. All is fine until the customer
> doesn't order from all three catagories. I get #ERROR. Any help?

Reply to this Message

JJF - 14 Mar 2007 17:40 GMT

In your =IIf statement what is [RecordsetClone] and [RecordCount]. I'm in
access 2003

> That should work unless the subform's Detail section goes completely blank
> when there are no records.
[quoted text clipped - 18 lines]
> > The main form totals the three subforms. All is fine until the customer
> > doesn't order from all three catagories. I get #ERROR. Any help?

Reply to this Message

JJF - 14 Mar 2007 18:13 GMT

Thanks Allen. It worked.

> In your =IIf statement what is [RecordsetClone] and [RecordCount]. I'm in
> access 2003
[quoted text clipped - 21 lines]
> > > The main form totals the three subforms. All is fine until the customer
> > > doesn't order from all three catagories. I get #ERROR. Any help?

Reply to this Message