Parent form shows "#Error" in total if subform has no records

[sphere_monk]

Hi everyone,

I've got a grand total field on a parent form that adds the value of two summary fields that are located on subforms of the parent, each one on a different subform. These subforms are looking up transactions from different tables. One subform DOES allow edits, additions and deletions (EAD's) whereas the other does NOT allow these. They are both "Continuous Forms" type forms.

The parent grand total field is using the following for a Control Source: [frmProjectBudgetPOsubfrm].[Form]![txtPOTotal]+[frmProjectBudgetTrans].[Form]![txtTotal]

The trouble I'm having is that if the subform that does NOT allow EAD's does not find any records in the table, then the parent form's grand total shows "#Error". If the situation is reversed, with a record found in the subform that does NOT allow EAD's but with no record found in the form that DOES allow EAD's, then the parent's total is blank rather than showing an error.

Does anyone know of a way I can force the parent form's grand total to display "0" instead of "#Error" if the subform that does NOT allow EAD's shows no records?

I've tried changing the Control Source to IIf(IsNull([frmProjectBudgetPOsubfrm].[Form]![txtPOTotal])=True Or [frmProjectBudgetPOsubfrm].[Form]![txtPOTotal]="",(0+[frmProjectBudgetTrans].[Form]![txtTotal]),[frmProjectBudgetPOsubfrm].[Form]![txtPOTotal]+[frmProjectBudgetTrans].[Form]![txtTotal]), but this does not help.

Thanks!

Dan

 

[sphere_monk]

Hi Bob,

Thanks for your reply!

I pasted your code into the field and it is still behaving in exactly the same manner. I keep forgetting about the NZ function, it's a lot easier than the way I searched for nulls in the previous post.

I'm thinking the error is because the form that does not allow additions does not have the "new" record, and therefore its recordset is null?

 

[spikepl]

I myself use this: =IIf(Not IsError([sfrmOrderdetails].[Form]![txtCount]),[sfrmOrderdetails].[Form]![txtCount],0)

Allen Browne has another fix for this on his site allenbrowne.com somewhere -do not remember where, so search for it yourself, if not happy wioth the above.

 

[sphere_monk]

Thanks spikepl, that worked perfectly!

I changed the ControlSource of the grand total field on the parent to

=IIf(Not IsError([frmProjectBudgetPOsubfrm].[Form]![txtPOTotal]),[frmProjectBudgetPOsubfrm].[Form]![txtPOTotal]+[frmProjectBudgetTrans].[Form]![txtTotal],0+[frmProjectBudgetTrans].[Form]![txtTotal])

The grand total field is now blank if that subform finds no records.

Thanks again!

 

[ScarlettWizard]

I know this is an old post.
But spikepl. If you see this you are a GOD
I have searched everywhere :banghead::banghead: for this answer.
THANK YOU