Null Value in Subform Total

  • Thread starter Thread starter chasday
  • Start date Start date
C

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.
 
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.
Click to expand...

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

Hope this helps,
-- James
 
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
 
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.

Jeanette Cunningham said:
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 said:
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.
Click to expand...
Click to expand...
 
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

Jeanette Cunningham said:
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 said:
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.
Click to expand...
Click to expand...
 
Back
Top