Adding subform totals if one subform is Null

[Guest]

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?

 

[Allen Browne]

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

 

[Guest]

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.

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia

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?

 

[Guest]

Thanks Allen. It worked.

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.

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia

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?