Calculated Text Box Caused Database to Close

[mattc66 via AccessMonster.com]

I have a main form with a text box that performs a calculation from 2 other
text boxes on the main form. The 2 text boxes get there data from a subform
footer subtotal.

When I have this calculation in the form the form crashes and closes ACCESS.
If I remove the calculation it runs fine.

Does anyone have a suggestion on what I can do or look for?

Thanks
Matt

 

[Guest]

It would be helpful if you posted the code for the calculation

 

[mattc66 via AccessMonster.com]

I have tried a new way to perform the calculation, below is the formulas. I
placed the calculation on the subform. Now I get an ERROR in the
ttlHrsPerSqFt1 text box.

These are the text boxes on the subform datasheet pagefooter.
Name: ttlSqFt
Control Source: =Sum([SqFt])

Name: ttlLaborHrs
Control Source: =Sum([LaborHrs])

**THIS IS THE PROBLEM CODE**
Name: ttlHrsPerSqFt
Control Source: =([ttlHrsPerSqFt]/[ttlSqFt])

Text Boxes on the main form.

Name: ttlSqFt1
Control Source: =[frmShopOrderSqFtShippedSummaryRpt].[Form]![ttlSqFt]

Name: ttlLaborHrs1
Control Source: =[frmShopOrderSqFtShippedSummaryRpt].[Form]![ttlLaborHrs]

Name: ttlHrsPerSqFt1
Control Source: =[frmShopOrderSqFtShippedSummaryRpt].[Form]![ttlHrsPerSqFt]

It would be helpful if you posted the code for the calculation

I have a main form with a text box that performs a calculation from 2 other
text boxes on the main form. The 2 text boxes get there data from a subform

[quoted text clipped - 7 lines]

Thanks
Matt

 

[Guest]

My guess is [ttlSqFt] is 0 when the error occurs. You can't divide by zero
in any programming language.

You might think using an IIf statement would cure that, but it will not
since VBA evaluates all the expressions before it executes, so you still get
the error.

My solution would be to take the expression out of the control source of
ttlHrsPerSqFt and replace it with a function:

Private Function Calc_ttlHrsPerSqFt() As Double

Calc_ttlHrsPerSqFt = 0
If Not IsNull(Me.ttlSqFt) And Not IsNUll(Me.ttlLaborHrs) Then
If Me.ttlSqFt <> 0 Then
Calc_ttlHrsPerSqFt = [ttlHrsPerSqFt]/[ttlSqFt]
End If
End If
End Function

Then in the control source: =Calc_ttlHrsPerSqFt()
--
Dave Hargis, Microsoft Access MVP

I have tried a new way to perform the calculation, below is the formulas. I
placed the calculation on the subform. Now I get an ERROR in the
ttlHrsPerSqFt1 text box.

These are the text boxes on the subform datasheet pagefooter.
Name: ttlSqFt
Control Source: =Sum([SqFt])

Name: ttlLaborHrs
Control Source: =Sum([LaborHrs])

**THIS IS THE PROBLEM CODE**
Name: ttlHrsPerSqFt
Control Source: =([ttlHrsPerSqFt]/[ttlSqFt])

Text Boxes on the main form.

Name: ttlSqFt1
Control Source: =[frmShopOrderSqFtShippedSummaryRpt].[Form]![ttlSqFt]

Name: ttlLaborHrs1
Control Source: =[frmShopOrderSqFtShippedSummaryRpt].[Form]![ttlLaborHrs]

Name: ttlHrsPerSqFt1
Control Source: =[frmShopOrderSqFtShippedSummaryRpt].[Form]![ttlHrsPerSqFt]

It would be helpful if you posted the code for the calculation

I have a main form with a text box that performs a calculation from 2 other
text boxes on the main form. The 2 text boxes get there data from a subform

[quoted text clipped - 7 lines]

Thanks
Matt

--
Matt Campbell
mattc (at) saunatec [dot] com

Message posted via AccessMonster.com

 

[mattc66 via AccessMonster.com]

Okay that sounds good. I assume that this would all go on the subform.. When
I run it all I get on the main form is 0.000 and there is data so it should
not be 0.

Any suggestions?

Thanks
Matt

My guess is [ttlSqFt] is 0 when the error occurs. You can't divide by zero
in any programming language.

You might think using an IIf statement would cure that, but it will not
since VBA evaluates all the expressions before it executes, so you still get
the error.

My solution would be to take the expression out of the control source of
ttlHrsPerSqFt and replace it with a function:

Private Function Calc_ttlHrsPerSqFt() As Double

Calc_ttlHrsPerSqFt = 0
If Not IsNull(Me.ttlSqFt) And Not IsNUll(Me.ttlLaborHrs) Then
If Me.ttlSqFt <> 0 Then
Calc_ttlHrsPerSqFt = [ttlHrsPerSqFt]/[ttlSqFt]
End If
End If
End Function

Then in the control source: =Calc_ttlHrsPerSqFt()

I have tried a new way to perform the calculation, below is the formulas. I
placed the calculation on the subform. Now I get an ERROR in the

[quoted text clipped - 28 lines]

 

[mattc66 via AccessMonster.com]

I am getting all zero's. I think it's because the function is firing before
the data is available. Were can I put the function so that it fires after the
data if available?

My guess is [ttlSqFt] is 0 when the error occurs. You can't divide by zero
in any programming language.

You might think using an IIf statement would cure that, but it will not
since VBA evaluates all the expressions before it executes, so you still get
the error.

My solution would be to take the expression out of the control source of
ttlHrsPerSqFt and replace it with a function:

Private Function Calc_ttlHrsPerSqFt() As Double

Calc_ttlHrsPerSqFt = 0
If Not IsNull(Me.ttlSqFt) And Not IsNUll(Me.ttlLaborHrs) Then
If Me.ttlSqFt <> 0 Then
Calc_ttlHrsPerSqFt = [ttlHrsPerSqFt]/[ttlSqFt]
End If
End If
End Function

Then in the control source: =Calc_ttlHrsPerSqFt()

I have tried a new way to perform the calculation, below is the formulas. I
placed the calculation on the subform. Now I get an ERROR in the

[quoted text clipped - 28 lines]

 

[Guest]

I don't think that is the case. I think that in the data you are summing,
there is at least one record with a null value in the field. Any calculation
that references a varialbe, control, or field with a Null value returns Null
regardless of the other values in the calculation.
--
Dave Hargis, Microsoft Access MVP

I am getting all zero's. I think it's because the function is firing before
the data is available. Were can I put the function so that it fires after the
data if available?

My guess is [ttlSqFt] is 0 when the error occurs. You can't divide by zero
in any programming language.

You might think using an IIf statement would cure that, but it will not
since VBA evaluates all the expressions before it executes, so you still get
the error.

My solution would be to take the expression out of the control source of
ttlHrsPerSqFt and replace it with a function:

Private Function Calc_ttlHrsPerSqFt() As Double

Calc_ttlHrsPerSqFt = 0
If Not IsNull(Me.ttlSqFt) And Not IsNUll(Me.ttlLaborHrs) Then
If Me.ttlSqFt <> 0 Then
Calc_ttlHrsPerSqFt = [ttlHrsPerSqFt]/[ttlSqFt]
End If
End If
End Function

Then in the control source: =Calc_ttlHrsPerSqFt()

I have tried a new way to perform the calculation, below is the formulas. I
placed the calculation on the subform. Now I get an ERROR in the

[quoted text clipped - 28 lines]

Thanks
Matt