Adding subform totals if one subform is Null

J

JJF

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?
 
A

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
 
J

JJF

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

Allen Browne said:
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.

JJF said:
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?
Click to expand...
Click to expand...
 
J

JJF

Thanks Allen. It worked.

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

Allen Browne said:
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.

JJF said:
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?
Click to expand...
Click to expand...
Click to expand...
 
Top