Change Color of Box on Report based off Value

[Nuke1096]

What I'm trying to do is make it so that a certain area of my Detail area in my report changes colors based on whether or not a field name for each record is set to "Yes". The effect I wanted was produced easily enough by creating a Rectangle, coloring it Red, and setting it to not visible.

Putting RedBox.Visible = True in the Report On Load procedure works just fine. The problem I am having is attaching a condition to it.

If Me.FormatBox.Value = "Yes" Then

RedBox.Visible = True

End If

This compiles ok, but doesnt do anything to the actual report. Will it not work because the report has x amount of FormatBox's because it gets replicated for each record that is displayed? I should also note that I tried using a DL Lookup within my If statement and that didnt work either. That just colors all the boxes red and ignores the check.

Dim TestVar As String


TestVar = DLookup("Priority", "Table_Lancaster_Dispatch", "Priority = 'Yes'")


If TestVar = "Yes" Then


RedBox.Visible = True


End If

[rpeare]

you likely want to do this with conditional formatting available through the right click menu in your report design. No need to do simple formatting with VBA unless you have more than 3 possible formatting options.

So let's say one of the fields on each record is [Priority] in the detail section of your report you would have a CONDITIONAL FORMATTING something like

EXPRESSION IS

Then in the formula area have something like

[PRIORITY] = "Yes"

Then apply the formatting you want (I think you're limited to background color, font color, bold, italics, font size) if your report would be just as effective with those formatting choices I'd suggest using the conditional formatting option.

[pbaldy]

In case CF isn't available for that type of control, your code would go in the detail section's format event, and would need an Else clause to set the color back to normal.

[Nuke1096]

you likely want to do this with conditional formatting available through the right click menu in your report design. No need to do simple formatting with VBA unless you have more than 3 possible formatting options.

So let's say one of the fields on each record is [Priority] in the detail section of your report you would have a CONDITIONAL FORMATTING something like

EXPRESSION IS

Then in the formula area have something like

[PRIORITY] = "Yes"

Then apply the formatting you want (I think you're limited to background color, font color, bold, italics, font size) if your report would be just as effective with those formatting choices I'd suggest using the conditional formatting option.

I put the following code in the Format Event for the Detail section of the Report, but nothing happens. I have to be missing something basic.

If Me.FormatBox.Value = "Yes" Then


RedBox.BackColor = vbRed


Else


LogBox.BackColor = vbBlue




End If

[pbaldy]

If you're opening the report in Report view, the format events don't fire. Try Preview instead.

I assume the db is in a trusted location or you've explicitly enabled code.

[Nuke1096]

That did it! Unfortunately, launching the Report in Preview mode isnt what I want, so I'll have to use Rpeare's suggestion with the conditional formatting and just get creative with my Box replacement.

[pbaldy]

CF is certainly the first option, but doesn't work on all control types. If you want to use Report view, I think code will work in the paint event or something like that.

[Nuke1096]

I'm actually running into another weird issue now. I got everything working with Conditional Formatting, however, when I close out of my Database and reopen it, the formatting doesnt work anymore. The format info is still typed into the window for it, but it just ignores it. If i change some stuff around, like the color for example, it will start working properly again. But if I close out of the Database and reopen it again, it doesnt work again.

Do I have to do something with the Report On Load or Format events to get Conditional Formatting to stick?

[pbaldy]

That is weird. I've used CF many times and never had to do anything special to have it work consistently. Hopefully somebody will jump in that's familiar with that issue.

[rpeare]

I have run into some really odd things with Access 2010, for instance yesterday I was working on a query it ran perfectly, then I added another table and another field, nothing fancy, and four of my columns of data were suddenly blank where there was data before. The only things I can suggest are:

1. Post a sample database, maybe you've missed something
2. Compact/repair your database and try again.

[Nuke1096]

I actually was able to get it working fine by using code in the Paint Event for my report, so Im going to just ignore the Conditional Formatting problem all together.

This did the trick.

If Me.FormatBox.Value = "Yes" Then


LogBox.BackColor = RGB(255, 0, 0)
'RedBox.Visible = True




Else




If Me.FormatBox.Value = "No" Then


LogBox.BackColor = RGB(255, 255, 255)
'RedBox.Visible = True




End If
End If

Note, it doesn't let you turn something from visible to invisible however.