Multiple IF statements in a Form

[NickDenyer]

Hi guys,

Can anybody help me with creating something?

In a form, I have a drop-down list called Consequence, which as five levels (1, 2, 3, 4, 5) and another drop-down list called Likelihood which also has five levels (A, B, C, D, E). I then have a label called Grade and what I want is that if Consequence 1 is picked, with Likelihood 5, for example, it will produce text (YELLOW). I know how to conditional format this.

There is a table which contains the Consequence and Likelihood variables, this is called

tblRisk Grading

I have a good understanding IF statements, but not for so many variables. Can this be done?

My list of variables are as follows:

Consequence (1-5) / Likelihood (A-E) / Label (Text)

1 / A or B / YELLOW
1 / C, D, or E / GREEN
2 / A, B, or C / YELLOW
2 / D or E / GREEN
3 / A, B, C / AMBER
3 / D or E / YELLOW
4 / A or B / RED
4 / C, D, or E / AMBER
5 / A, B, C, D, E / RED

Any help would be wonderful!!!

Thanks. N

 

[pr2-eugin]

It can be done by using a combination of Select and If.. That will ease the use of so many if and else and end if.. something like..

Select Case Consequence.Value

Case 1
    If Likelihood.Value= "A" or Likelihood.Value="B" Then
        [COLOR=SeaGreen]'set color to Yellow[/COLOR]
    Else
        [COLOR=SeaGreen]'set color to Green
    [COLOR=Black]End If[/COLOR]
[COLOR=Black]Case 2
    [/COLOR][/COLOR] If Likelihood.Value= "D" or Likelihood.Value="E" Then
        [COLOR=SeaGreen]'set color to Green[/COLOR]
    Else
        [COLOR=SeaGreen]'set color to Yellow
    [COLOR=Black]End If[/COLOR]
'so on until case 5.
[COLOR=Black]End Select[/COLOR][/COLOR]

And make sure this code is placed inside the after update propert of Liklihood or OnFocus of the color field.

 

[NickDenyer]

Hi pr2-eugin

Thanks for your help. I've continued this code, but one question: Do I have to specify that I want it to appear in the label box that I've put below these options? I've put the code in and nothing works...

Thanks

 

[pr2-eugin]

Sorry I did not get that.. Could you explain? Where have you put the code in? I think should be best if you put the code inside the AfterUpdate property of
Likelihood.

 

[NickDenyer]

Certainly, I'll clarify:

- I have the drop-down list of Consequence
- beneath that I have the drop-down list of Likelihood
- Beneath the two, I have an empty Label box, which I've named 'Grade'

The idea is that when you select, for example, 1 from consequence, and A from likelihood, the 'Grade' Label box will turn Yellow.

The code has, of this moment, been placed in the AfterUpdate property of Likelihood.

Cheers

 

[pr2-eugin]

What do you mean by Label box? Is it a Text box or a Rectangle?

 

[pr2-eugin]

If you are using a rectangle to indicate the Grade (Color) then check the properties; under Format --> Back style it should say Normal not Transparent. That will reflect the color..

 

[NickDenyer]

I'm using Access 2007, and I had selected a 'Label' box from the dsign tools. I've now changed it to a rectangle. I can't see anything in the format called 'Back Style' just 'Back Colour' and there is no 'Normal' or 'Transparent' option.

Still nothing happens though. It is perhaps the way I have explained it, I will clarify again:

Drop-down list called 'Consequence'
Below that, another drop-down list called 'Likelihood'
Below the two, a blank 'Rectangle'

Depending on what has been picked in both drop-down lists, I would like the inside of the rectangle to change to a specific colour based on:

Consequence (1-5) / Likelihood (A-E) / Label (Text)

1 / A or B / YELLOW
1 / C, D, or E / GREEN
2 / A, B, or C / YELLOW
2 / D or E / GREEN
3 / A, B, C / AMBER
3 / D or E / YELLOW
4 / A or B / RED
4 / C, D, or E / AMBER
5 / A, B, C, D, E / RED

Thanks!

 

[pr2-eugin]

Okay.. Do the following..

* Delete the rectangle you put in..
* Under design, create a new Rectangle (adjust the size, place it where you want it to be placed)..
* Then on your right wil be the property sheet.. Under Format Tab; there will be an option Back Style (probably the 7th row) It will have its value set to 'Transparent' now change that to 'Normal'.

Then in the code you have implemented.. in the If condition

Box_Name.BackColor=3881966 (for yellow)


For the list of all colors.. go to..
http://www.endprod.com/colors/

Hope this is clear..

 

[NickDenyer]

hi,

Thanks again, but this isn't working :-( does the code need to specify the link between the Number (Consequence) and the letter (Likelihood) because i'm not sure it is defined in the code.

Thanks!

 

[pr2-eugin]

could you give a snippet of the code that you have placed under the AfterUpdate?

 

[NickDenyer]

Certainly, please find below:

Private Sub Form_AfterUpdate()

Select Case Consequence.Value

Case 1 - Insignificant
If Likelihood.Value = "A - Almost Certain" Or Likelihood.Value = "B - Likely" Then
GradeBox.BackColor = 3881966 ' Yellow
Else
GradeBox.BackColor = 65280 'Green
End If
Case 2 - Minor
If Likelihood.Value = "D - Unlikely" Or Likelihood.Value = "E - Rare" Then
GradeBox.BackColor = 65280 'Green
Else
GradeBox.BackColor = 3881966 'Yellow
End If
Case 3 - Moderate
If Likelihood.Value = "D - Unlikely" Or Likelihood.Value = "E - Rare" Then
GradeBox.BackColor = 3881966 'Yellow
Else
'GradeBox.BackColor = 33023 'Orange
End If
Case 4 - Major
If Likelihood.Value = "A - Almost Certain" Or Likelihood.Value = "B - Likely" Then
'GradeBox.BackColor = 255 'Red
Else
'GradeBox.BackColor = 33023 'Orange
End If
Case 5 - Catastrophic
If Likelihood.Value = "A - Almost Certain" Or Likelihood.Value = "B - Likely" Or Likelihood.Value = "C - Possible" Or Likelihood.Value = "D - Unlikely" Or Likelihood.Value = "E - Rare" Then
'GradeBox.BackColor = 255 'Red
End If

End Select

End Sub

 

[pr2-eugin]

Okay the problem is simple, you have to put the code inside the Likelihood_AfterUpdate() not Form_AfterUpdate() as simple as pie.. just replace that and it will work wonders..

Something other I have noticed.. if the Consequence.Value is a Text make sure you enclose them inside double quotes.. such as..

Case "1 - Insignificant"

And also the last case in the select (Case 5), by defenition regardless of the Likelihood value it is going to be Red. so no need to check the condition..

 

[NickDenyer]

Thanks for your support in all this

I've put this in the right section, amemded the code as instructed...still nothing :'( I feel it has something to do with the rectangle, what do you think?

Thanks, as always!

 

[pr2-eugin]

What about the 'RowSource' of the two ComboBoxes? Is it typed in values or is it looking up at some tables? I am sure that should work.. If the RowSource are values that you typed in the above code should work for sure, if it is table or query, then we need some modifications.. it might be bound to some other colum of the table or query that might cause problem..

Do the following:
* Check the Rectangle's BackStyle (again).. It should be Normal
* Check the RowSource of Consequence.. Should be typed in values, seperated by commas (for the code to work) or else if table/query check which colum it is bound to..

 

[NickDenyer]

Hi again!

Sorry I've been away for 4 days. I've checked all the information you asked me to, and the RowSource is bound to a table that I have.
So I guess this is where it is going wrong I've selected a table because I need the information that people select to be added to the table for record purposes.

Hope this helps!

 

[NickDenyer]

Hi again!

Sorry I've been away for 4 days. I've checked all the information you asked me to, and the RowSource is bound to a table that I have.
So I guess this is where it is going wrong I've selected a table because I need the information that people select to be added to the table for record purposes.

Hope this helps!

 

[pr2-eugin]

It is fine, hope you had a good Bank holiday weekend. Well anyway, since the rowsource is bound to the Table/Query.. You have to check the columns in the table that it is linked to. (if possible, let me know the structure of the table i.e. the columns in the table and also the query for Row Source)

AND ALSO

in the Data tab(of the control) there will be a property called Bound Column.. tell me the number it holds..

 

[NickDenyer]

Ok,

I won't give you all the columns because there are LOADS as it is a big form but basically there is three columns called 'Consequence', 'Likelihood' and 'Grade' in a table called tblRisk. For the format of Con and Like I used the lookup wizard and both have a query each called qrconsequence and qrlikelihood. Both of these simply have the values in. I want the information picked in these comboboxes placed into the table tblRisk.

When you say the data tab, what do you mean by 'the control'? If its Consequence and Likelihood, they are both 1.

Hope this helps!

 

[pr2-eugin]

Okay Nick, All I wanted was the Query's column names. Anyway. I have used the attachment to explain what you have to see and what needs to be done.

Check the diagram now !!

Now, as I mentioned if the ComboBox is bound to the column of the Query's ID column(Bound column : 1) not the Result(Column number is 2) You will have this problem, as it might display the Result but in reality the value returned from CmbBox.Value might be a number. So you have to use the Select..Case values as ID (Or) change the Bound Column to 2 (Or) Change the Query to import only the Result column. Hope this helps.

Post back if you still find trouble.