Null Value in Subform Total

[chasday]

I have a Job Cost form with a Change Order subform. The fields in the subform
are totaled in the footer, and are then referenced in textboxes in the main
form. Everything works great as long as the subform has records returned. If
the subform is empty, the subform totals are blank and the referenced Text
boxes show #Error. I want the referenced boxes to have a 0 value.

The Control Source of the subform total textbox reads =Sum([EquipmentCost])
I Tried =Nz(Sum([EquipmentCost]),0) and =Nz((Sum([EquipmentCost])),0) to
replace Null with 0 but it didn't make a difference.

The Control Source of the Main form textbox is =([tblChangeOrder
subform].[Form]![tbCOEquipTotal])

Thanks for any help.

 

[Minton M]

I have a Job Cost form with a Change Order subform. The fields in the subform
are totaled in the footer, and are then referenced in textboxes in the main
form. Everything works great as long as the subform has records returned. If
the subform is empty, the subform totals are blank and the referenced Text
boxes show #Error. I want the referenced boxes to have a 0 value.

The Control Source of the subform total textbox reads =Sum([EquipmentCost])
I Tried =Nz(Sum([EquipmentCost]),0) and =Nz((Sum([EquipmentCost])),0) to
replace Null with 0 but it didn't make a difference.

The Control Source of the Main form textbox is =([tblChangeOrder
subform].[Form]![tbCOEquipTotal])

Thanks for any help.

Just use the ISERROR function in the sum control to wrap your
calculation and detect (and handle) the #ERROR value.

Hope this helps,
-- James

 

[Jeanette Cunningham]

Hi,
try this. Note: untested air code, it may need some tweaking to fit your
form.
txtSum1, txtSum2, etc are the names of the textboxes on the subform that
compute the totals.
Put the following code in the Current event of the subform.

Private Sub Form_Current

If Me.RecordsetClone.RecordCount = 0 Then
Me.txtSum1 = 0
Me.txtSum2 = 0
Me.txtSum3 = 0
End if

End Sub

When the subform has no records, the calculated textboxes should show 0


Jeanette Cunningham

 

[chasday]

Jeanette,
Your solution makes sense, but it isn't working for some reason. When the
subform is empty the Sum boxes are blank (I believe they should have 0's) and
I still get the #Error in the main form boxes.

Also, I'm not sure how I would implement Milton's ISERROR sugestion.

Hi,
try this. Note: untested air code, it may need some tweaking to fit your
form.
txtSum1, txtSum2, etc are the names of the textboxes on the subform that
compute the totals.
Put the following code in the Current event of the subform.

Private Sub Form_Current

If Me.RecordsetClone.RecordCount = 0 Then
Me.txtSum1 = 0
Me.txtSum2 = 0
Me.txtSum3 = 0
End if

End Sub

When the subform has no records, the calculated textboxes should show 0


Jeanette Cunningham

I have a Job Cost form with a Change Order subform. The fields in the
subform
are totaled in the footer, and are then referenced in textboxes in the
main
form. Everything works great as long as the subform has records returned.
If
the subform is empty, the subform totals are blank and the referenced Text
boxes show #Error. I want the referenced boxes to have a 0 value.

The Control Source of the subform total textbox reads
=Sum([EquipmentCost])
I Tried =Nz(Sum([EquipmentCost]),0) and =Nz((Sum([EquipmentCost])),0) to
replace Null with 0 but it didn't make a difference.

The Control Source of the Main form textbox is =([tblChangeOrder
subform].[Form]![tbCOEquipTotal])

Thanks for any help.

 

[chasday]

OK, I got it to work.
I had to use Form_Load instead of Current, and add Me.txtSum1.ControlSource
= ""
but now it works.

Thanks for the help

Hi,
try this. Note: untested air code, it may need some tweaking to fit your
form.
txtSum1, txtSum2, etc are the names of the textboxes on the subform that
compute the totals.
Put the following code in the Current event of the subform.

Private Sub Form_Current

If Me.RecordsetClone.RecordCount = 0 Then
Me.txtSum1 = 0
Me.txtSum2 = 0
Me.txtSum3 = 0
End if

End Sub

When the subform has no records, the calculated textboxes should show 0


Jeanette Cunningham

I have a Job Cost form with a Change Order subform. The fields in the
subform
are totaled in the footer, and are then referenced in textboxes in the
main
form. Everything works great as long as the subform has records returned.
If
the subform is empty, the subform totals are blank and the referenced Text
boxes show #Error. I want the referenced boxes to have a 0 value.

The Control Source of the subform total textbox reads
=Sum([EquipmentCost])
I Tried =Nz(Sum([EquipmentCost]),0) and =Nz((Sum([EquipmentCost])),0) to
replace Null with 0 but it didn't make a difference.

The Control Source of the Main form textbox is =([tblChangeOrder
subform].[Form]![tbCOEquipTotal])

Thanks for any help.