Multiple IF statements in a Form (1 Viewer)

[NickDenyer]

Thanks for all your help it is very kind to you. I've done all that but still no activity in that bleeding box! I'm wondering whether there is something more simple that i could do instead!!!!

 

[pr2-eugin]

If it is not much trouble and if possible could you give screencaps as I have done (specially for the combo box, rectangle)? The query? and the Code that you have implemented? Surely it would be easy and simple.

 

[VilaRestal]

A possible alternative to the code:

Select Case Left(Consequence.Value,1) & Left(Likelihood.Value,1)
    Case "1C", "1D", "1E", "2D", "2E"
        GradeBox.BackColor = 65280 'Green
    Case "1A", "1B", "2A", "2B", "2C", "3D", "3E"
        GradeBox.BackColor = 3881966 ' Yellow
    Case "3A", "3B", "3C", "4C", "4D", "4E"
        GradeBox.BackColor = 33023 'Amber
    Case "4A", "4B", "5A", "5B", "5C", "5D", "5E"
        GradeBox.BackColor = 255 'Red
End Select

 

[NickDenyer]

No problem, please find attached!

 

[pr2-eugin]

Hello Nick,

* The code is correct. I did not see anything wrong with it, If you want try using the code given by VilaRestal.

* The ColumnWidth just needs to be blank. Column Count as 1 not 2. The BoundColumn to be 1 not 2.

* I use A2010 so I am not sure if your version has the Back Style property, you can type the name of the control GradeBox follwed by a period(.) it will list all methods/properties available see if it has the 'BackStyle' if it does set it to 1 and then add the color code.. like..

#GradeBox.BackStyle = 1 'Which is nothing but Normal (Transparent is 0)
GradeBox.BackColor = 65280 'Green#

To make sure the code is executed as desired, place Debug.Print inside the select cases, see if they are printed in the immediate window.. A good way of checking execution..

 

[NickDenyer]

Hi Guys!!!!!!

OK I've done what you've said pr2-Eugin, and added VilaRestal's code, and now the boxes turn the colours I want!!!! I am eternally grateful! The finishing line is in sight, as I have just two little requests:

The Numbers and the Letters have been shortened, (actually, the Letters come out as numbers also...) and I need them to appear in full. What would the code be if I want the following, e.g. "1 - Insignificant" "A - Almost Certain" and do i need to do anything to my comboboxes' properties?

Secondly, I want the grade they get (e.g. 3C AMBER) to appear in the main table. How can I do this?

THANKS SO MUCH!!!!!

 

[pr2-eugin]

You are welcome, Sorry it took such a long time to get it sorted, but glad I could help.

I believe the problem is the ComboBox property. Have a play around with it. Change its ColumWidth as before see if works.

Where do you want to Place the values? I mean '3C AMBER' is there a Text field on the FORM? Or it is only in the Table? If in the Form then you can simple do this, say

Dim Clr As String
Select Case Left(Consequence.Value,1) & Left(Likelihood.Value,1)
    Case "1C", "1D", "1E", "2D", "2E"
        Clr="Green"
        GradeBox.BackColor = 65280 'Green
    Case "1A", "1B", "2A", "2B", "2C", "3D", "3E"
        Clr="Yellow"
        GradeBox.BackColor = 3881966 ' Yellow
    Case "3A", "3B", "3C", "4C", "4D", "4E"
        Clr="Amber"
        GradeBox.BackColor = 33023 'Amber
    Case "4A", "4B", "5A", "5B", "5C", "5D", "5E"
        Clr="Red"
        GradeBox.BackColor = 255 'Red
End Select

GradeText.Value=Left(Consequence.Value,1) & Left(Likelihood.Value,1) & " " & clr

If it is in a table then you have to use an UPDATE command to do it.

 

[NickDenyer]

Hey no worries, it took about 1/100th of the time had I been left to my own devices!

I'll have a play zaround; I get can to a point where I open the combo and half is 1-5 and the other is 1-almost certain - 5-rare!

To be honest thinking about it, AMBER would be fine, and I just want it to appear in the table so that we have a record of it. If its easier for the colour to appear in the table thats fine also