changing backcolor based on criteria

[johnlatona123]

hello access guru's!

heres my issue...

i have a multi line report with a mess of fields in it. i would like to change the back color of specifically a field, a header, and a group based on the criteria of a field.

so for example i tried this...

on load
if [field] = "word" then
me![field].backcolor = 4754153
else
me![field].backcolor = 2552420
end if

this did NOT error out, but also did not work, it would not recognize my critera of "word" therefore colored all the field the else color

in addition to that, it would seem it is mixing colors, like if i used RGB code for red as the if and yellow as the else, i would end up with an orange field. which makes even less sense to me.

can anyone spell out some code with the correct syntax so that i can apply it to my DB?

please help!

thanks!

 

[johnlatona123]

can anyone help me please?

i am still stuck on this issue!

 

[vbaInet]

Is your form in Continuous view?

What colours are these --> 4754153 and 2552420?

 

[johnlatona123]

it is a contiunous report, and the colors there are blue and red

 

[johnlatona123]

it turns out that i can set the conditional formatting of specific cells to change color based on field criteria, but i still cant get the backcolor or altbackcolor to match when the critera change

 

[vbaInet]

The BackColor of what? The textbox or the section?

 

[johnlatona123]

i want the color of the header which these fields reside in to change based on the critera of a specific field with conditonal formatting.

 

[vbaInet]

You can't apply Conditional Formatting on the Header section but you have two options:

1. Move your code to the Format event of the Header section

OR

2. Create a textbox to fill the entire section, send it to the back and apply Conditional Formatting on it. This will act as the Background for your controls.

 

[johnlatona123]

great ideas!

how do i phrase the code to do this in the format event for the header section?

 

[vbaInet]

You've got the code already in your first post. Use vbRed for red and vbBlue for Blue in place of the numbers.

 

[johnlatona123]

this is not working.

Private Sub GroupHeader1_Format(Cancel As Integer, FormatCount As Integer)
If [job status] = "job complete" Then
[GroupHeader1].BackColor = vbBlue
End Sub

 

[vbaInet]

That is not the code you have in your first post.

 

[johnlatona123]

nor is this:


Private Sub GroupHeader1_Format(Cancel As Integer, FormatCount As Integer)
If [job status] = "job complete" Then
Me![GroupHeader1].BackColor = vbBlue
Else
Me![GroupHeader1].BackColor = vbRed
End If

End Sub

 

[vbaInet]

If you feel the first solution isn't working try the second. If both aren't working then upload your db.

 

[johnlatona123]

ok ive got the 2nd method working with conditional formatting.

how do i call out a blank field with conditional formatting?

i was using [job status]="word"

but im not sure what word to use for fields that are naturally blank (defaulted empty fields)? i tried "" and null and "null" and blank and "blank"...what word or phrase will call out these blank fields?

 

[vbaInet]

Expression Is
IsNull([job status]) = True

 

[johnlatona123]

ok, we got this working for the most part, but now it would seem that the ordering of the conditional formatting is out of whack. some records are turning colors they should not be turning based off of my criteria in the conditional formatting. what am i missing?

 

[johnlatona123]

here is an attachment of a screenshot of my formatting, things are turning green that should not be green, yellow and red are not displaying at all.

ideas?

 

[vbaInet]

I actually did say IsNull([job_status]) = True and not [job_status] Is Null. Two different things.

 

[johnlatona123]

ok i changed that null deal, but my colors are still not correct.