Conditional formatting of a field

[Linda]

I have two fields on my form, consequence and probability. They are filled
from drop down menus that contain the phrases very low, low, medium, high and
very high. Each of those are assigned a value of 1 through 5 respectively.
A third field, exposure, is calculated as the product of the two values. The
customer would like the background of the exposure field to be colored. The
criteria for the color is based on a chart of the products where:
Consequence = 1 Probablitily = 1-5 => green (exposure value of 1,2,3,4,5)
Consequence = 2 Probablitily = 1-3 => green (exposure value 2,4,6)
Consequence = 2 Probablitily = 4-5 => yellow (exposure value 8,10)
Consequence = 3 Probablitily = 1 => green (exposure value 3)
Consequence = 3 Probablitily = 2-4 => yellow (exposure value of 6,9,12)
(exposure value 5)
Consequence = 3 Probablitily = 5 => red (exposure value 15)
Consequence = 4 Probablitily = 1-3 => yellow (esposure level 4,8,12)
Consequence = 4 Probablitily = 4-5 => red (exposure level 16, 20)
Consequence = 5 Probability = 1-2 = > yellow (Exposure level 5,10)
Consequence = 5 Probability = 3-5 = red (exposure level 15,20,25)


Ultimately when a report is generated I need to be able to print the matrix
with the values of 1-5 on the the X and Yaxis. The matrix will consist of 25
blocks where each block contains the product of the values on each axis.
Then the value from the form for the exposure needs to be marked on the
matrix. Each box will be colored according to the chart given above.

Can anyone tell me how to determine the color for the exposure field?
Can anyone tell me how to create the matrix for the report and mark the
correct block.
Thank you!

 

[Marshall Barton]

I have two fields on my form, consequence and probability. They are filled
from drop down menus that contain the phrases very low, low, medium, high and
very high. Each of those are assigned a value of 1 through 5 respectively.
A third field, exposure, is calculated as the product of the two values. The
customer would like the background of the exposure field to be colored. The
criteria for the color is based on a chart of the products where:
Consequence = 1 Probablitily = 1-5 => green (exposure value of 1,2,3,4,5)
Consequence = 2 Probablitily = 1-3 => green (exposure value 2,4,6)
Consequence = 2 Probablitily = 4-5 => yellow (exposure value 8,10)
Consequence = 3 Probablitily = 1 => green (exposure value 3)
Consequence = 3 Probablitily = 2-4 => yellow (exposure value of 6,9,12)
(exposure value 5)
Consequence = 3 Probablitily = 5 => red (exposure value 15)
Consequence = 4 Probablitily = 1-3 => yellow (esposure level 4,8,12)
Consequence = 4 Probablitily = 4-5 => red (exposure level 16, 20)
Consequence = 5 Probability = 1-2 = > yellow (Exposure level 5,10)
Consequence = 5 Probability = 3-5 = red (exposure level 15,20,25)

Ultimately when a report is generated I need to be able to print the matrix
with the values of 1-5 on the the X and Yaxis. The matrix will consist of 25
blocks where each block contains the product of the values on each axis.
Then the value from the form for the exposure needs to be marked on the
matrix. Each box will be colored according to the chart given above.

Can anyone tell me how to determine the color for the exposure field?
Can anyone tell me how to create the matrix for the report and mark the
correct block.

I strongly suggest that you create a table (named
ExposureColors) with 3 fields, Consequence, Probablitily and
ColorCode. Add 25 records to the table with every
combination of Consequence and Probablitily and the desired
color code numbers 1 (green), 2 (yellow) and 3 (green).

Then, you can join that table to the form/report's record
source table/query to include the color code field.

If the form is displayed in Continuous or datasheet view,
the Exposure text box can then use Conditional Formatting to
apply the actual color.

If the form is displayed in Single view, the table could use
the RGB value instead of 1, 2 or 3 and a line of code in the
form's Current event to set the text box's BackColor
property. The report can use the same line of code in the
Format event. This is preferable because it removes the RGB
values from your code so they can be changed by simple edits
to the table without touching your code /form.

 

[Dale Fye]

I agree with Marshall. Since there is inconsistency in the color values as
they relate to specific Exposure Values, his idea makes the most sense.

Example:
Cons Prob Exp Color
1 4 4 Green
2 2 4 Green
4 1 4 Yellow
2 3 6 Green
3 2 6 Yellow


--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[Linda]

I understand the concept of what you are saying to do. I'ver created the
table with the appropriate color code for each record. But I'm having
trouble with the implementation. If either the consequence value changes or
the probability value changes the exposure value is recalculated and the
background color needs to be set accordingly. I've created a relationship
between the exposure color field in the record source table and the color
field in the risk value table. Do I still need to write code to get that
value into the source table? Once I get the value in the table how do I get
the color set using conditional formatting? Field value is based on the
value for the box to be colored, right? That is different than the color
value. Do I write an expression to check the value from the table? I've
tried different things but I can't seem to get syntax right and I'm just not
sure I'm doing what makes sense.
Your help is greatly appreciated.

 

[Marshall Barton]

I understand the concept of what you are saying to do. I'ver created the
table with the appropriate color code for each record. But I'm having
trouble with the implementation. If either the consequence value changes or
the probability value changes the exposure value is recalculated and the
background color needs to be set accordingly. I've created a relationship
between the exposure color field in the record source table and the color
field in the risk value table. Do I still need to write code to get that
value into the source table? Once I get the value in the table how do I get
the color set using conditional formatting? Field value is based on the
value for the box to be colored, right? That is different than the color
value. Do I write an expression to check the value from the table? I've
tried different things but I can't seem to get syntax right and I'm just not
sure I'm doing what makes sense.

Please use your real table and field names so we don't
confuse each other with semi descriptive phrases for these
items.

Do not put the color code in the data table. The form's
record source should be a query (what are you actually
using?) that Joins the data table to the color codes table.
If you are using color numbers like 1, 2, and 3 (please
provide this kind of detail in your posts), then you can set
the text box's Conditional Formatting to:
Expression Is [color field] = 1 and back color green
Expression Is [color field] = 2 and back color yellow
Expression Is [color field] = 3 and back color red

If your form is displayed in Single view (please provide
this information so we don't waste time chasing alternatives
that you will not use), then you can use VBA code instead of
CF and make it easier to get colors beyond those in the CF
color picker.