validation rule based on a query

[ejoffe1]

Hey,

I have a form with a few fields.
The form is designed for entering patients' lab results into a table.
In another table (tb_parameters) I have fields for parameter, unit_type (mg, dL etc.) min (minimal acceptable value) and max.
I use a combo box based on this table in order to chose the appropriate parameter.
My problem is - I wish to have some validation rules based on the min and max values of each parameters (so that the user won't enter unreasonable values by mistake)

I've designed a query which takes the entered parameter from the form and returnes its unit, min and max, but when use it as the source for the validation rule I get an error (maybe because the validation rule doesn't run the query).
Any advice ?

 

[Tim L]

Perhaps you should ignore the validation rule in the field and use the After Update event of the text box into which the value has been entered.

If you have default min and max values stored in the same table as the units then you could reference the (min/max) column of the combo that is used to select the units.

The VBA that you'd be looking for would be something along the lines of

If Me.txtValueEntered.Value < Me.cboUnits.Column(<use a number here to refernce the appropriate column>) then
        MsgBox "The value you entered is too small."
        Me.txtValueEntered.SetFocus
        Exit Sub
    Else
        If Me.txtValueEntered.Value > Me.cboUnits.Column (<number>) then
            MsgBox "The value you entered is too largel."
            Me.txtValueEntered.SetFocus
            Exit Sub
        End If
    End If

Note, the first column in a combo box is 0, the second 1, etc.

You can check which column the combo is using for the value by clicking on the ... button against the Row Source line of the combo data properties and then viewing the query.

HTH

Tim