Confirming a calculated value is within range 1

[dragonmanUSMC]

I have a form that my co-workers will input design data on, there is a field that will calculate the rim speed based off of the data they provide. (I know it's not a good idea to perform calculated fields on a form, but this is the only instance this number will be needed.

The control source of the field is:

=([Forms]![SB Design Calculations].[SawRPM]*[Forms]![SB Design Calculations].[DiaOfSaw])/3.8197

What I need to do is confirm that the calculated value is between 9,000 and 11,000 if so ok move on. If not I will create a custom error msg to point them in the right direction on why it is out of range.

Any help would be greatly appreciated.

 

[Eupher]

dragonman72,

Why do you think that calculated fields on a form are a bad idea? That's precisely where they *should* go (or on a report). What's generally frowned on is storing calculated values in your tables. Anyway, here is one way to do it:

In the AfterUpdate event of both [SawRPM] and [DiaOfSaw] either recalc the form or requery the calculated field, then validate the range. Something like this:

Private Sub Text0_AfterUpdate()
Me!Text4.Requery
If Me!Text4 < 3 Or Me!Text4 > 6 Then
    Me!Text4.BackColor = vbRed
    Me!Text4.ForeColor = vbWhite
    MsgBox "Text4 value is out of range.  Check your figures and try again."
    Else
        Me!Text4.BackColor = vbWhite
        Me!Text4.ForeColor = vbBlack
End If
End Sub

Private Sub Text2_AfterUpdate()
Me!Text4.Requery
If Me!Text4 < 3 Or Me!Text4 > 6 Then
    Me!Text4.BackColor = vbRed
    Me!Text4.ForeColor = vbWhite
    MsgBox "Text4 value is out of range.  Check your figures and try again."
    Else
        Me!Text4.BackColor = vbWhite
        Me!Text4.ForeColor = vbBlack
End If
End Sub

Obviously you'll use the field names and validation values as appropriate for your app.

HTH...

Ken S.