system to prevent field from being populated with number outside of range

[sheckay]

Hello. Is there a way to keep a field from being populated, via a form, with a number that is outside of a range? The number being entered into the field needs to check the value of two different fields, the numbers entered into these two "range" fields determine the range. The field to be populated is compared to the first "range" fields to make sure it's equal to or higher. Then to the other "range" field, to make sure the number being entered is equal to or lower. If the number being entered is within the range, it's accepted. If not, there's a window that pops up.
Or would a query have to be run to do this?
Thanks in advance.

[Micron]

Use control BeforeUpdate event to check if it contains something that is >= txt1 AND <= txt3 and if it does not pass the test. Don't copy this directly and use your own control names.

Private Sub txt3_BeforeUpdate(Cancel As Integer)
  If Not txt3 >= txt1 AND txt3 <= txt2 Then Cancel = True
End Sub

[sheckay]

Wow! Thank you. I'll give it a shot once I start the design. Thank you very much!

[Micron]

You're welcome. Hope it helps.

[sheckay]

Thank you. Sorry for the late reply. I was working on other aspects of the database. But I did try the solution you suggested, and it didn't come into play. I tried to tweek it, but that did help. So I googled it again, based on the key words of your suggestion, and I came up with this. It gives me the appropriate error when I enter a number out of the range. But it also gives me a message that the number's too high even when it's in the tolerance range. Can anyone see what I'm missing? Thanks in advance!

Private Sub Text94_BeforeUpdate(Cancel As Integer)
If Me.Text94 < Me.DTMinus Then
' Display a message
MsgBox "Your Entry is Outside of the Tolerance Range. Too low!!", vbExclamation
' Cancel the update
Cancel = True
End If
If Me.Text94 > Me.DTPlus Then
' Display a message
MsgBox "Your Entry is Outside of the Tolerance Range. Too high!!", vbExclamation
' Cancel the update
Cancel = True
End If


End Sub

[Bob Fitz]

Try:

If Not Me.Text94 >= Me.DTPlus AND Me.Text94 <= Me.DTMinus Then 
  MsgBox "Your Entry is Outside of the Tolerance Range.", vbExclamation
  Cancel = True
End If

[Micron]

If 95 is acceptable, of course you're going to get a 'too high message' as it's greater than 94. That's why I had >= AND <= in my first post.
It would help to know what an example of an acceptable range would be.

[sheckay]

Hi Bob. Thank you. But, for some reason, that doesn't give any message, or restrict any entry.

[sheckay]

Hello Micron. Text94 is just the name of the text box used to enter a number.

[sheckay]

Hello Micron. Text94 is just the name of the text box used to enter a number. Not a value.

[Welshgasman]

Hello Micron. Text94 is just the name of the text box used to enter a number. Not a value.

Start giving controls meaningful names.
6 months down the line Text94 is not going to mean anything to you, or any other poor soul who has to change it.

[Welshgasman]

Thank you. Sorry for the late reply. I was working on other aspects of the database. But I did try the solution you suggested, and it didn't come into play. I tried to tweek it, but that did help. So I googled it again, based on the key words of your suggestion, and I came up with this. It gives me the appropriate error when I enter a number out of the range. But it also gives me a message that the number's too high even when it's in the tolerance range. Can anyone see what I'm missing? Thanks in advance!

Private Sub Text94_BeforeUpdate(Cancel As Integer)
If Me.Text94 < Me.DTMinus Then
' Display a message
MsgBox "Your Entry is Outside of the Tolerance Range. Too low!!", vbExclamation
' Cancel the update
Cancel = True
End If
If Me.Text94 > Me.DTPlus Then
' Display a message
MsgBox "Your Entry is Outside of the Tolerance Range. Too high!!", vbExclamation
' Cancel the update
Cancel = True
End If


End Sub

Code tags for a start
If you put the code between code tags, it retains the indentation. You are indenting your code, aren't you?

Walk through your code with F8 and inspect the values, as that looks OK to me ATM.

[Minty]

Everyone is getting there logic brains a little bit messed up

If Not Me.Text94 >= Me.DTPlus OR Me.Text94 <= Me.DTMinus Then 
  MsgBox "Your Entry is Outside of the Tolerance Range.", vbExclamation
  Cancel = True
End If

[Bob Fitz]

Everyone is getting there logic brains a little bit messed up

If Not Me.Text94 >= Me.DTPlus OR Me.Text94 <= Me.DTMinus Then 
  MsgBox "Your Entry is Outside of the Tolerance Range.", vbExclamation
  Cancel = True
End If

On reflection I think you're right.
I believe it needs a NOT after the OR as well.

If Not Me.Text94 >= Me.DTPlus OR NOT Me.Text94 <= Me.DTMinus Then
 MsgBox "Your Entry is Outside of the Tolerance Range.", vbExclamation
  Cancel = True
End If

[Minty]

On reflection I think you're right.
I believe it needs a NOT after the OR as well.

If Not Me.Text94 >= Me.DTPlus OR NOT Me.Text94 <= Me.DTMinus Then
 MsgBox "Your Entry is Outside of the Tolerance Range.", vbExclamation
  Cancel = True
End If

Nope Bob we are are both wrong, it doesn't need the NOT on either
If The DTPlus is the upper limit and the DTMinus is the lower limit then the correct statement is

If Me.Text94 >= Me.DTPlus OR Me.Text94 <= Me.DTMinus Then...