Check if number is between two other numbers in table

[mrbboy]

I have a tbl for masses that also contains the low and high tolerance limits.

tbl_Mass

MassID (Autonumber)
Mass (number)
low mass (number)
high mass (number)

My form contains a combo box, cbo_Mass, for people to choose the mass they wish to calibrate and a text box, txt_Mass, for people to enter the measure mass. How do I compare the number in txt_Mass to the low mass and high mass values in table tbl_Mass. If the measured mass doesn't fall inside the low and high masses, a message would pop up. Please help.

 

[TheAceMan1]

How are ya mrbboy mrbboy . . .

Post the [blue]RowSource[/blue] for the combo! . . . If its a query post the SQL! . . .

See Ya! . . . . . .

Be sure to see thread181-473997

 

[mrbboy]

The row source for the combo is Mass in tbl_Mass.

 

[TheAceMan1]

mrbboy . . .

Hmmm . . . post exactly what you have in there! . . .

See Ya! . . . . . .

Be sure to see thread181-473997

 

[TheAceMan1]

mrbboy . . .

Perform the following:
[ol][li]Copy/paste the following to the [blue]RowSource[/blue] of the combo:

[blue]SELECT MassID, Mass, [Low Mass], [High Mass] FROM tbl_Mass ORDER BY [Mass];[/blue]

[/li]
[li]Next set the following [blue]properties[/blue] of the combo:
[tt] Name [blue]cbxMass[/blue]
Column Count [blue]1[/blue]
Column Widths [blue]0;1;1;1[/blue]
List Width [blue]3[/blue]
Width [blue]3[/blue]
Bound Column [blue]1[/blue][/tt][/li]
[li]Finally . . . in the [blue]BeforeUpdate[/blue] event of [blue]txt_Mass[/blue], copy/paste the following:

[blue]   Dim Msg As String, Style As Integer, Title As String
   
   Set cbx = Me!cbxMass
   Style = vbCritical + vbOKOnly
   
   If Trim(cbx & "") = "" Then
      Msg = "Select a Mass from the combobox 1st!"
      Title = "No Mass Limits Selected! . . ."
      MsgBox Msg, Style, Title
      Cancel = True
      Me!txt_Mass.Undo
      cbx.SetFocus
   ElseIf Me!txt_Mass < cbx.Column(2) Or Me!txt_Mass > cbx.Column(3) Then
      Msg = "Entered Mass is not within limits"
      Title = "Mass Limit Error! . . ."
      MsgBox Msg, Style, Title
      Cancel = True
   End If
   
   Set cbx = Nothing[/blue]

[/li][/ol]

See Ya! . . . . . .

Be sure to see thread181-473997