Access 2007 - Conditional Formatting Report Fields with VBA

[Rhys Gottwald]

Hi All,

I have a report that is going to have 30 odd fields each containing a number, the number is the wind speed.

I want to format the background of the field based on the value of the text box. I have code that formats the background, but I am having issues with it. They are listed below
1. I cannot get it to use # values i.e. #e4fefc
2. I can only get it to format when I put in detail_click, detail_format does not seem to work
3. When I click on a line all the other fields are formatted according to that selected lines value not it’s own value.

This is the code I am using

Select Case WindSpeed_03h.Value
        Case 6
            Me.WindSpeed_03h.BackColor = "#e4fefc"
        Case 7
            Me.WindSpeed_03h.BackColor = "#adfbf7"
        Case 8
            Me.WindSpeed_03h.BackColor = "#9afaf6"
        Case 9
            Me.WindSpeed_03h.BackColor = "#73f8f2"
        Case 10
            Me.WindSpeed_03h.BackColor = "#e4fefc"
        Case 11
            Me.WindSpeed_03h.BackColor = "38fb83"
        Case 12
            Me.WindSpeed_03h.BackColor = "#09fe15"
            Me.WindSpeed_03h.FontBold = True
        Case 13
            Me.WindSpeed_03h.BackColor = "#09fe15"
            Me.WindSpeed_03h.FontBold = True
        Case 14
            Me.WindSpeed_03h.BackColor = "#13fe00"
            Me.WindSpeed_03h.FontBold = True
        Case 15
            Me.WindSpeed_03h.BackColor = "#43fb00"
            Me.WindSpeed_03h.FontBold = True
    End Select

Basically I want it to look like windguru.

Can someone give me a hand on these problems please.

[NeoPa]

Rhys, First and foremost, you can only have one question per thread. The site has rules and this one makes working much easier.

As for your hex values, they are not strings. You need the format &H2B5C2.

As for number three - it sounds like you're using Continuous Forms. Why Values in Unbound Form Controls do not Persist may help you to understand why this is, even if your controls are bound (only the value is maintained separately for bound forms. Not the definition).

If there's anything you still need help with (The hex value part can stay in here) then please post it separately.

[Rhys Gottwald]

Hi NeoPa,

Sorry about that, I assumed that because I was reffering to one block of code, I was asking "one" question.

I have reposeted point 3, please note I am creating a report not a form.

[NeoPa]

Rhys Gottwald:
please note I am creating a report not a form.

The concept covered by the article holds as well for reports as it does for forms. I assume you looked at the contents rather than just the title? I can explain in more detail if you feel that is necessary.

PS I looked for another post of point #3, but only found a thread which had no specific questions in it (which I deleted).

If you post the third question clearly and post a link to it here, I will look at it for you.