Form Datasheet view cell colouring

[skyline666]

Hi,

I have a subform in a form, in Access 2003. The subform is in datasheet view. I have multiple fields, but only 5 are relevant to this, these are Probability, Consequence, RiskRating, Manageability and Criticality. RiskRating is equal to Probability mulitplied by Consequence, and Criticality is equal to RiskRating multiplied by Manageability. What I would like is for the cell in RiskRating/Criticality to go a certain colour for a certain value. I have tried something already but it hasn't worked, can you actually change the colour of the cell (and also still see the data in the cell) in datasheet view? My code I have is as follows (putting just RiskRating in as Criticality is and will be the same apart from the field names):

Private Sub Consequence_AfterUpdate()

Me.RiskRating = Me.Probability * Me.Consequence
    If Me.RiskRating < 3 Then
        Me.RiskRating.BackColor = 8454143
    ElseIf Me.RiskRating >= 3 Or Me.RiskRating < 6 Then
        Me.RiskRating.BackColor = 33023
    Else
        Me.RiskRating.BackColor = 255
    End If

End Sub

So if RiskRating is below 3, I want the cell to go light yellow.

Many thanks for your help,

Andrew

 

[randy700]

Not in datasheet view. You could do it with continuous forms but, if I'm not mistaken, all will change to display the same attributes as the current (selected) record.

Your code should work fine for single forms.

You don't need the OR condition on the second if, though.
I'd just say

If Me.RiskRating < 6 Then

It won't get to this line if the first condition (< 3) is met.


Randy

 

[skyline666]

Hi,

I decided to use Conditional Formatting for this, this does only allow three different conditions, but I only need 3 so im alrite! I did the conditional formatting and thought there must be code behind this somewhere, so me and a work colleague started looking around on vba and looking on the internet aswell and got the conditional formatting working using code. Here is what I am using:

Dim format As FormatCondition

Set format = Forms!RiskRegister!Risk.Form.RiskRating.FormatConditions.Add(acExpression, acLessThan, "riskrating < 3")
Set format = Forms!RiskRegister!Risk.Form.RiskRating.FormatConditions.Add(acExpression, acLessThan, "riskrating < 6")
Set format = Forms!RiskRegister!Risk.Form.RiskRating.FormatConditions.Add(acExpression, acGreaterThanOrEqual, "riskrating >= 6")

Forms!RiskRegister!Risk.Form.RiskRating.FormatConditions(0).BackColor = vbYellow
Forms!RiskRegister!Risk.Form.RiskRating.FormatConditions(1).BackColor = 33023
Forms!RiskRegister!Risk.Form.RiskRating.FormatConditions(2).BackColor = vbRed

Note that there are only 3 lines of formatting for setting, you can only set 3 different conditions using code, as you can when doing it from Format->Conditional Formatting.

Hope anyone else finds this useful.

Andrew