Comparing data from two seperate fields to fill another

[MoDiggity]

I want to be able to automatically display one of four actions (ie. A-Accept, W-Watch, R-Resolve, M-Manage)in a textbox [FMRES] when scores 1-4 in two other textboxes [IMP] and [Prob] are entered.
The Actions are based on a simple grid system


PROB RESULTS
4 A R R R
3 A W M R
2 A W M M
1 2 3 4 IMP A A M M

The plan being that if an item has an Impact score value of 4 and a Probability score value of 4 the Result = R

The only idea I came up with was to write some code but I don't know how to apply it to automatically display the result in the Result textbox. Can someone help?

The code below reflects the grid scores that cause one of the four results, the scores are actually representations of Impact and Probability levels and are not used as multipliers.

'Accept
If Me.[IMP].Value = 1 And Me.[PROB].Value = 1 _
Or Me.[PROB].Value = 2 Or Me.[PROB].Value = 3 _
Or Me.[PROB].Value = 4 Then
Me.[FMRES].Value = "Accept"
Me.[FMRES].ForeColor = black
Else
If Me.[IMP].Value = 2 And Me.[PROB].Value = 1 Then
Me.[FMRES].Value = "Accept"
Me.[FMRES].ForeColor = black

'Watch
ElseIf Me.[IMP].Value = 2 And Me.[PROB].Value = 2 _
Or Me.[PROB].Value = 3 Then
Me.[FMRES].Value = "Watch"
Me.[FMRES].ForeColor = black

'Resolve
ElseIf Me.[IMP].Value = 3 And Me.[PROB].Value = 4 Then
Me.[FMRES].Value = "Resolve"
Me.[FMRES].ForeColor = White
Me.[FMRES].BackColor = Blue
ElseIf Me.[IMP].Value = 4 And Me.[PROB].Value = 3 _
Or Me.[PROB].Value = 4 Then
Me.[FMRES].Value = "Resolve"
Me.[FMRES].ForeColor = White
Me.[FMRES].BackColor = Blue

'Manage
ElseIf Me.[IMP].Value = 3 And Me.[PROB].Value = 1 _
Or Me.[PROB].Value = 2 Or Me.[PROB].Value = 3 Then
Me.[FMRES].Value = "Manage"
Me.[FMRES].ForeColor = White
Me.[FMRES].BackColor = Red
ElseIf Me.[IMP].Value = 4 And Me.[PROB].Value = 1 _
Or Me.[PROB].Value = 2 Then
Me.[FMRES].Value = "Manage"
Me.[FMRES].ForeColor = White
Me.[FMRES].BackColor = Red
End If
End If

 

[BillPower]

Hi MoDiggity,

I have assumed that on your form you have:

1. A text box named FMRES
2. A frame with 4 options, value 1 - 4 respectively, default either of 1 - 4 named IMP
3. A frame with 4 options, value 1 - 4 respectively, default either of 1 - 4 named PROB.

Put the code below in your form's module.

Private Sub Form_Load() ' displays default choice in FMRES when form loads.
TestForResponse
End Sub

Private Sub IMP_AfterUpdate() 'displays choice in FMRES after IMP has been updated
TestForResponse
End Sub

Private Sub PROB_AfterUpdate() 'displays choice in PROBafter IMP has been updated
TestForResponse
End Sub


Sub TestForResponse()
If Me!IMP = 1 And Me!PROB = 1 _
Or Me!PROB = 2 Or Me!PROB = 3 _
Or Me!PROB = 4 Then
Me!FMRES = "Accept"
Me!FMRES.ForeColor = 0 'black
Else
If Me!IMP = 2 And Me!PROB = 1 Then
Me!FMRES = "Accept"
Me!FMRES.ForeColor = 0 'black
'Watch
ElseIf Me!IMP = 2 And Me!PROB = 2 _
Or Me!PROB = 3 Then
Me!FMRES = "Watch"
Me!FMRES.ForeColor = 0 'black

'Resolve
ElseIf Me!IMP = 3 And Me!PROB = 4 Then
Me!FMRES = "Resolve"
Me!FMRES.ForeColor = 16777215 'White
Me!FMRES.BackColor = 16711680 'Blue
ElseIf Me!IMP = 4 And Me!PROB = 3 _
Or Me!PROB = 4 Then
Me!FMRES = "Resolve"
Me!FMRES.ForeColor = 16777215 'White
Me!FMRES.BackColor = 16711680 'Blue

'Manage
ElseIf Me!IMP = 3 And Me!PROB = 1 _
Or Me!PROB = 2 Or Me!PROB = 3 Then
Me!FMRES = "Manage"
Me!FMRES.ForeColor = 16777215 'White
Me!FMRES.BackColor = 255 'Red
ElseIf Me!IMP = 4 And Me!PROB = 1 _
Or Me!PROB = 2 Then
Me!FMRES = "Manage"
Me!FMRES.ForeColor = 16777215 'White
Me!FMRES.BackColor = 255 'Red
End If
End If
End Sub

This will update the text box FRMES based on selections made in IMP and PROB.

You need to do some work on your logic though, it doesn't work. Also, take a look in Access help at the Select Case Statement, works more efficently than If..ElseIf.

Hope this helps.

 

[MoDiggity]

Thanks for taking a look at this, I wasn't able to get it to work by evaluating the scores (1-4), but I did get it to work by evaluating the Text result, ie, (if "Manage" then.. ). As far as using the scores, you were right, some of the logic didn't work, I have it working now in Excel but haven't figured out how to apply it to my mdb yet. I also attempted the Case Select suggestion but could not understand how to use it this application. Nor did I find any suitable examples to use as a model. I'd appreciate you taking a look at the Excel macro and making any further suggestions. I know it's a bunch of redundant code, but it's the only way I know how to make it work...

Sub CE_PMR()
For Each X In Selection

If X.Offset(0, -12).Value = 1 And X.Offset(0, -10).Value = 1 Then
X.Value = "Accept"
ElseIf X.Offset(0, -12).Value = 1 And X.Offset(0, -10).Value = 2 Then
X.Value = "Accept"
ElseIf X.Offset(0, -12).Value = 1 And X.Offset(0, -10).Value = 3 Then
X.Value = "Accept"
ElseIf X.Offset(0, -12).Value = 1 And X.Offset(0, -10).Value = 4 Then
X.Value = "Accept"
ElseIf X.Offset(0, -12).Value = 2 And X.Offset(0, -10).Value = 1 Then
X.Value = "Accept"

ElseIf X.Offset(0, -12).Value = 2 And X.Offset(0, -10).Value = 2 Then
X.Value = "Monitor"
ElseIf X.Offset(0, -12).Value = 2 And X.Offset(0, -10).Value = 3 Then
X.Value = "Monitor"

ElseIf X.Offset(0, -12).Value = 3 And X.Offset(0, -10).Value = 1 Then
X.Value = "Manage"
ElseIf X.Offset(0, -12).Value = 3 And X.Offset(0, -10).Value = 2 Then
X.Value = "Manage"
ElseIf X.Offset(0, -12).Value = 3 And X.Offset(0, -10).Value = 3 Then
X.Value = "Manage"
ElseIf X.Offset(0, -12).Value = 4 And X.Offset(0, -10).Value = 1 Then
X.Value = "Manage"
ElseIf X.Offset(0, -12).Value = 4 And X.Offset(0, -10).Value = 2 Then
X.Value = "Manage"

ElseIf X.Offset(0, -12).Value = 2 And X.Offset(0, -10).Value = 4 Then
X.Value = "Resolve"
ElseIf X.Offset(0, -12).Value = 3 And X.Offset(0, -10).Value = 4 Then
X.Value = "Resolve"
ElseIf X.Offset(0, -12).Value = 4 And X.Offset(0, -10).Value = 4 Then
X.Value = "Resolve"
ElseIf X.Offset(0, -12).Value = 4 And X.Offset(0, -10).Value = 3 Then
X.Value = "Resolve"

End If
Next
End Sub