Checking data input against limits

  • Thread starter Thread starter Phil Cousins
  • Start date Start date
P

Phil Cousins

I am working on a database to store analytical test data.

when data is input in a form I would like to check it against some set
limits from a limits table and make the user aware if a paticular value lies
outside range for this type of sample and set a value in the data table to
show an out of limits sample. Different limits will be needed for each
sample type.

Can anyone point me along the right path?

Thanks

Phil
 
Hi Phil,

I'd do this with VBA code in the form's BeforeUpdate event procedure,
calling a function somewhat like the air-code one below for each. Set
the Tag property of controls bound to fields whose values need checking.

....
Dim C as Access.Control
Dim blOutsideLimits As Boolean
....
blOutsideLimits = False
For Each C in Me.Controls
If Me.Tag = "CheckMyValue" Then
If OutsideLimits(C.Value, C.Name) Then
blOutsideLimits = True
Exit For
End If
End If
Next
....



Public Function OutsideLimits(TheValue As Variant, _
ValueName As String) As Boolean

Dim varMax As Double, varMin As Double
Dim blTemp as Boolean


'Handle null data
If IsNull(TheValue) Then
OutSideLimits = True
Exit Function
End If
blTemp = False
'Get limits
varMax = DLookup("MaxValue", "Limits", "ValueName=""" _
& ValueName & """")
varMin = DLookup("MinValue", "Limits", "ValueName=""" _
& ValueName & """")
If IsNull(varMax) Then
'No upper limit
Else
If cDbl(TheValue) > cdbl(varMax) Then blTemp = True
End If
'similar for varMin
...
OutsideLimits = blTemp
End Function
 
I am working on a database to store analytical test data.

when data is input in a form I would like to check it against some set
limits from a limits table and make the user aware if a paticular value lies
outside range for this type of sample and set a value in the data table to
show an out of limits sample. Different limits will be needed for each
sample type.
Click to expand...

I presume you have a table of all the sample types; this table should
contain fields for the limits which pertain to this sample.

You can put VBA code in the BeforeUpdate event of the textbox in which
you enter these values; it could use DLookUp to check the user value
against the limits in the table. Air code:

Private Sub txtValue_BeforeUpdate(Cancel as Integer)
Dim vEntered As Variant
vEntered = Me!txtValue
If vEntered < DLookUp("[LowerLimit]", "SampleTypes", "[SampleType] = "
& <some criteria I don't know>) OR vEntered > DLookUp("[UpperLimit]",
"SampleTypes", "[SampleType] = " <criteria> Then
MsgBox "This value is not in limits! Accept anyway?", vbYesNo
If iAns = vbNo Then
Cancel = True
End If
End If
End Sub
 
Thanks for having a look John.

I'm new at this (VBA) so its going to take me a while to try this out but
I'll try your suggestion.

Phil
 
John Vinson said:
I am working on a database to store analytical test data.

when data is input in a form I would like to check it against some set
limits from a limits table and make the user aware if a paticular value lies
outside range for this type of sample and set a value in the data table to
show an out of limits sample. Different limits will be needed for each
sample type.
Click to expand...

I presume you have a table of all the sample types; this table should
contain fields for the limits which pertain to this sample.

You can put VBA code in the BeforeUpdate event of the textbox in which
you enter these values; it could use DLookUp to check the user value
against the limits in the table. Air code:

Private Sub txtValue_BeforeUpdate(Cancel as Integer)
Dim vEntered As Variant
vEntered = Me!txtValue
If vEntered < DLookUp("[LowerLimit]", "SampleTypes", "[SampleType] = "
& <some criteria I don't know>) OR vEntered > DLookUp("[UpperLimit]",
"SampleTypes", "[SampleType] = " <criteria> Then
MsgBox "This value is not in limits! Accept anyway?", vbYesNo
If iAns = vbNo Then
Cancel = True
End If
End If
End Sub
Click to expand...

Thanks John I'll give it a go.

Phil
 
Back
Top