Populate field based on another field's value

[jh]

I am trying to autopopulate a field. I need help! Thanks
in advance.

The user picks a service and it has CommitDays associated
with it. So let's say CommitDays = 10 - this is
automatically populated.
DUEDATE is automatically populated to calculate 10 days
from the STARTDATE which is entered. What I am trying to
do is autopopulate the field "SLAStatus" based on the
below criteria:

The criteria for SLASTATUS is:

StartDate DueDate Percent* SLAStatus
should be
9/21 10/5 110 "Red"
9/22 10/6 100 "Red"
9/23 10/7 90 "Yellow"
9/24 10/8 80 "Green"

Legend:
0 - 85% of time passed = Green
If Actual End Date <= Target Date, Green
86 - 90% of time passed = Yellow
91% and over of time passed = Red
If Actual End Date >= Target Date, Red

ElseIf

If there is an ActualEndDate, then calculate the StartDate
to ActualEndDate.
If ActualEndDate is >DueDate, then SLAStatus should
be "Red".
If ActualEndDate is <=DueDate, then SLAStatus should
be "Red".


'HERE'S MY CODE:

If IsDate(Me.ActualEnd) Then

intOverDueDays = (CalcBusinessDays(datStart,
Me.ActualEnd)) - Me.CommittedDays
intLapsedDays = CalcBusinessDays(datStart,
Me.ActualEnd)

Select Case intOverDueDays
Case Is > 0
strMsg = "Red"
Case Is = 0
strMsg = "Green"
Case Is < 0
strMsg = "Green"
End Select

intPercent = SetStatusPercent(intDaysToTarget,
intLapsedDays, "Y")

Else

intOverDueDays = CalcBusinessDays(datStart,
datCurrent) - Me.CommittedDays
intLapsedDays = CalcBusinessDays(datStart, datCurrent)


Select Case intOverDueDays
Case Is > 0
strMsg = "Red"
Case Is = 0
strMsg = "Now Due!"
Case Is < 0
strMsg = "Green"
End Select




'AND HERE'S MY MODULE
Public Function SetStatusPercent(daystotarget As Integer,
dayslapsed As Integer, ActualEndDateFlag As String) As Long
Dim Percent As Integer
Dim color As Long
Dim green As Long
Dim yellow As Long
Dim red As Long

red = 255
green = 32768
yellow = 65535

If ActualEndDateFlag = "Y" Then

If dayslapsed <= daystotarget Then
Percent = 0
Else
Percent = 100
End If

Else

Percent = (dayslapsed / daystotarget) * 100

End If

SetStatusPercent = Percent

End Function

 

[Bruce]

To what end are you trying to populate the field with
various words? Tomorrow the calculation could yield a
different response. Do you intend to store the
information that yesterday the percent was 80 and the code
was "green", and that today the percent is 90 and the code
is "yellow"? Do you want a record in your table
containing the information that in the past the item was
not late?
If you are trying to populate controls in a form or
report, that is an entirely different matter. Where is
the code located? Without being able to quite understand
your database function and structure it is difficult to be
specific, but I would consider performing the calculations
in a query and basing the form (?) on the query. You can
also perform calculations in text boxes, but I like to
have them all in one place when I can do so.
If the user selects a service, what else does the user
need to see? When is the code run? Somebody else might
understand what you are attempting, but I think you will
need to provide details of your table structure and a
clear description of what is supposed to happen step by
step.