Delayed Result

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

On my form I have two combo boxes with value lists ([Impact] and
[Probability]). Based on the combined selection of the combo boxes there is a
specific output in a text box. Due to the multiple possible combinations I
used the following Function (see below). The function was working fine and
would give an automatic update once the selection was made in both combo
boxes. However, it will now only update if I go to a new record or toggle
between the design and form view. Any suggestions?

Function GetResult(Impact As String, Probability As String) As String

Dim strResult As String

Select Case Impact
Case "1- Low"
Select Case Probability
Case "1- Not Probable (0-10%)"
strResult = "GREEN"
Case "2- Low (11-25%)"
strResult = "GREEN"
Case "3- Medium (26-50%)"
strResult = "GREEN"
Case "4- High (51-75%)"
strResult = "GREEN"
Case "5- Nearly Certain (76-100%)"
strResult = "GREEN"
Case Else
strResult = "**ERROR**: Unknown Probability " & Probability
End Select
Case "2- Minor"
Select Case Probability
Case "1- Not Probable (0-10%)"
strResult = "GREEN"
Case "2- Low (11-25%)"
strResult = "GREEN"
Case "3- Medium (26-50%)"
strResult = "YELLOW"
Case "4- High (51-75%)"
strResult = "YELLOW"
Case "5- Nearly Certain (76-100%)"
strResult = "YELLOW"
Case Else
strResult = "**ERROR**: Unknown Probability " & Probability
End Select
Case "3- Moderate"
Select Case Probability
Case "1- Not Probable (0-10%)"
strResult = "GREEN"
Case "2- Low (11-25%)"
strResult = "GREEN"
Case "3- Medium (26-50%)"
strResult = "YELLOW"
Case "4- High (51-75%)"
strResult = "YELLOW"
Case "5- Nearly Certain (76-100%)"
strResult = "RED"
Case Else
strResult = "**ERROR**: Unknown Probability " & Probability
End Select
Case "4- Significant"
Select Case Probability
Case "1- Not Probable (0-10%)"
strResult = "GREEN"
Case "2- Low (11-25%)"
strResult = "YELLOW"
Case "3- Medium (26-50%)"
strResult = "YELLOW"
Case "4- High (51-75%)"
strResult = "RED"
Case "5- Nearly Certain (76-100%)"
strResult = "RED"
Case Else
strResult = "**ERROR**: Unknown Probability " & Probability
End Select
Case "5- High"
Select Case Probability
Case "1- Not Probable (0-10%)"
strResult = "YELLOW"
Case "2- Low (11-25%)"
strResult = "YELLOW"
Case "3- Medium (26-50%)"
strResult = "RED"
Case "4- High (51-75%)"
strResult = "RED"
Case "5- Nearly Certain (76-100%)"
strResult = "RED"
Case Else
strResult = "**ERROR**: Unknown Probability " & Probability
End Select
Case Else
strResult = "**ERROR**: Unknown Impact " & Impact
End Select

GetResult = strResult

End Function
 
You'll have to excuse me for my ignorance (I'm a novice user) so forgive me
if I'm not understanding exactly what you're asking for. But here goes...I
created the function in a module. The function is the control source of the
my text box to display the result. Hopefully that helped.

Klatuu said:
where are you calling the function from?

Soroya1920 said:
On my form I have two combo boxes with value lists ([Impact] and
[Probability]). Based on the combined selection of the combo boxes there is a
specific output in a text box. Due to the multiple possible combinations I
used the following Function (see below). The function was working fine and
would give an automatic update once the selection was made in both combo
boxes. However, it will now only update if I go to a new record or toggle
between the design and form view. Any suggestions?

Function GetResult(Impact As String, Probability As String) As String

Dim strResult As String

Select Case Impact
Case "1- Low"
Select Case Probability
Case "1- Not Probable (0-10%)"
strResult = "GREEN"
Case "2- Low (11-25%)"
strResult = "GREEN"
Case "3- Medium (26-50%)"
strResult = "GREEN"
Case "4- High (51-75%)"
strResult = "GREEN"
Case "5- Nearly Certain (76-100%)"
strResult = "GREEN"
Case Else
strResult = "**ERROR**: Unknown Probability " & Probability
End Select
Case "2- Minor"
Select Case Probability
Case "1- Not Probable (0-10%)"
strResult = "GREEN"
Case "2- Low (11-25%)"
strResult = "GREEN"
Case "3- Medium (26-50%)"
strResult = "YELLOW"
Case "4- High (51-75%)"
strResult = "YELLOW"
Case "5- Nearly Certain (76-100%)"
strResult = "YELLOW"
Case Else
strResult = "**ERROR**: Unknown Probability " & Probability
End Select
Case "3- Moderate"
Select Case Probability
Case "1- Not Probable (0-10%)"
strResult = "GREEN"
Case "2- Low (11-25%)"
strResult = "GREEN"
Case "3- Medium (26-50%)"
strResult = "YELLOW"
Case "4- High (51-75%)"
strResult = "YELLOW"
Case "5- Nearly Certain (76-100%)"
strResult = "RED"
Case Else
strResult = "**ERROR**: Unknown Probability " & Probability
End Select
Case "4- Significant"
Select Case Probability
Case "1- Not Probable (0-10%)"
strResult = "GREEN"
Case "2- Low (11-25%)"
strResult = "YELLOW"
Case "3- Medium (26-50%)"
strResult = "YELLOW"
Case "4- High (51-75%)"
strResult = "RED"
Case "5- Nearly Certain (76-100%)"
strResult = "RED"
Case Else
strResult = "**ERROR**: Unknown Probability " & Probability
End Select
Case "5- High"
Select Case Probability
Case "1- Not Probable (0-10%)"
strResult = "YELLOW"
Case "2- Low (11-25%)"
strResult = "YELLOW"
Case "3- Medium (26-50%)"
strResult = "RED"
Case "4- High (51-75%)"
strResult = "RED"
Case "5- Nearly Certain (76-100%)"
strResult = "RED"
Case Else
strResult = "**ERROR**: Unknown Probability " & Probability
End Select
Case Else
strResult = "**ERROR**: Unknown Impact " & Impact
End Select

GetResult = strResult

End Function
Click to expand...
Click to expand...
 
Try this. In the After Update evens of both Combo Boxes Do this:

Me.MyTextBox = GetResult(Me.Impact, Me.Probability)


Now change your function arguments to Variants instead of strings so it wont
blow up if either of the combo boxes is null. I would also change the name
of strResult to varResult. Also, add the code below right after your Dim
statement so if either of the combo boxes is null, it will return Null.

Function GetResult(Impact As Variant, Probability As Variant) As Variant

Dim strResult As Variant

If IsNull(Impact) Or IsNull(Probability) Then
GetResult = Null
Exit Function
End if

Soroya1920 said:
You'll have to excuse me for my ignorance (I'm a novice user) so forgive me
if I'm not understanding exactly what you're asking for. But here goes...I
created the function in a module. The function is the control source of the
my text box to display the result. Hopefully that helped.

Klatuu said:
where are you calling the function from?

Soroya1920 said:
On my form I have two combo boxes with value lists ([Impact] and
[Probability]). Based on the combined selection of the combo boxes there is a
specific output in a text box. Due to the multiple possible combinations I
used the following Function (see below). The function was working fine and
would give an automatic update once the selection was made in both combo
boxes. However, it will now only update if I go to a new record or toggle
between the design and form view. Any suggestions?

Function GetResult(Impact As String, Probability As String) As String

Dim strResult As String

Select Case Impact
Case "1- Low"
Select Case Probability
Case "1- Not Probable (0-10%)"
strResult = "GREEN"
Case "2- Low (11-25%)"
strResult = "GREEN"
Case "3- Medium (26-50%)"
strResult = "GREEN"
Case "4- High (51-75%)"
strResult = "GREEN"
Case "5- Nearly Certain (76-100%)"
strResult = "GREEN"
Case Else
strResult = "**ERROR**: Unknown Probability " & Probability
End Select
Case "2- Minor"
Select Case Probability
Case "1- Not Probable (0-10%)"
strResult = "GREEN"
Case "2- Low (11-25%)"
strResult = "GREEN"
Case "3- Medium (26-50%)"
strResult = "YELLOW"
Case "4- High (51-75%)"
strResult = "YELLOW"
Case "5- Nearly Certain (76-100%)"
strResult = "YELLOW"
Case Else
strResult = "**ERROR**: Unknown Probability " & Probability
End Select
Case "3- Moderate"
Select Case Probability
Case "1- Not Probable (0-10%)"
strResult = "GREEN"
Case "2- Low (11-25%)"
strResult = "GREEN"
Case "3- Medium (26-50%)"
strResult = "YELLOW"
Case "4- High (51-75%)"
strResult = "YELLOW"
Case "5- Nearly Certain (76-100%)"
strResult = "RED"
Case Else
strResult = "**ERROR**: Unknown Probability " & Probability
End Select
Case "4- Significant"
Select Case Probability
Case "1- Not Probable (0-10%)"
strResult = "GREEN"
Case "2- Low (11-25%)"
strResult = "YELLOW"
Case "3- Medium (26-50%)"
strResult = "YELLOW"
Case "4- High (51-75%)"
strResult = "RED"
Case "5- Nearly Certain (76-100%)"
strResult = "RED"
Case Else
strResult = "**ERROR**: Unknown Probability " & Probability
End Select
Case "5- High"
Select Case Probability
Case "1- Not Probable (0-10%)"
strResult = "YELLOW"
Case "2- Low (11-25%)"
strResult = "YELLOW"
Case "3- Medium (26-50%)"
strResult = "RED"
Case "4- High (51-75%)"
strResult = "RED"
Case "5- Nearly Certain (76-100%)"
strResult = "RED"
Case Else
strResult = "**ERROR**: Unknown Probability " & Probability
End Select
Case Else
strResult = "**ERROR**: Unknown Impact " & Impact
End Select

GetResult = strResult

End Function
Click to expand...
Click to expand...
Click to expand...
 
Back
Top