Compare Value in Control with Value in table

[drschlong]

Hi all,

I have the following code attached to one of my controls on a form on the Lost Focus Event.

If IsNull(Me.Compliance) = True Then
Me.ComplianceScore = ""
Else
If Me.Compliance >= 78.4 Then
Me.ComplianceScore = 4
Else
If Me.Compliance >= 77.2 Then
Me.ComplianceScore = 3
Else
If Me.Compliance >= 76 Then
Me.ComplianceScore = 2
Else
If Me.Compliance < 76 Then
Me.ComplianceScore = 1
End If
End If
End If
End If
End If

As you can see I am actually entering the values directly in the code e.g. If me.compliance >=78.4 Then......

Is there any way of instead of putting these values directly in to the code I can look them up in a table.

The reason I ask this is because the actual values may change as time goes on and instead of me having to go in and change the values in the code I would like to be able to change the values in the table and then the code would automatically reflect this change. Another reason is that this code is repeated in numerous places so it would also mean updating the table once instead of updating various parts of code numerous times.

So in theory my Table would look something like this:-

The Name of the Table would be tblComplianceScores.

ComplianceValue (this is the Field Name)
78.4
77.2
76
75

So if the value entered into the control is <= one of the values in the table then the ComplianceScore control on the form is updated with 1 or 2 or 3 or 4 accordingly.

I hope this makes sense.

Thanks in advance for any help anybody can give me.

Steven. C.

 

[Mile-O]

Two things: that's some mighty untidy code you got there, you can tidy it up a couple of ways:


i) from your original structure (IF...THEN...ELSE)

If IsNull(Me.Compliance) = True Then 
   Me.ComplianceScore = "" 
ElseIf Me.Compliance >= 78.4 Then 
   Me.ComplianceScore = 4 
ElseIf Me.Compliance >= 77.2 Then 
   Me.ComplianceScore = 3 
ElseIf Me.Compliance >= 76 Then 
   Me.ComplianceScore = 2 
ElseIf Me.Compliance < 76 Then 
   Me.ComplianceScore = 1 
End If

ii) use the SELECT CASE structure

Select Case Me.Compliance
   Case Is >= 78.4
       Me.ComplianceScore = 4
   Case Is >= 77.2
       Me.ComplianceScore = 3
   Case Is >= 76
       Me.ComplianceScore = 2
   Case Is < 76
       Me.ComplianceScore = 1
   Case Else
      Me.ComplianceScore = Null
End Select

And to your question, you could use the DLookup function (look it up in the Access Help) to do what you want.

It would be something along these lines:

Me.ComplianceScore = DLookup("[FieldName]","Tablename", CRITERIA)