Change "Back colour" based on criteria

[krutoigoga]

Hi everyone.
Created a subreport from a query and I am trying to change the background colour of a text box in a report based on a simple criteria. If the text box displays "open" no background color or #FFFFFF and if the text box is closed then back ground color is to be #A5A5A5.
Currently the report displays a list of items, some being closed and some open.
Any help would be appreciated.
Thank you.

 

[lcook1974]

you have to put an "IF" statement on the report when it opens.

if me.yourtextbox = "Open" then
me.yourtextbox.backgroundcolor = #FFFFFF
End if

if me.yourtextbox = "closed" then
me.yourtextbox = #A5A5A5
End if

 

[pbaldy]

You can also use Format/Conditional Formatting (on the ribbon starting in 2007).

 

[krutoigoga]

Getting expression errors when trying the code provided.
I went to report property sheet > on open > code builder > and the following code was addded:

Private Sub Report_Open(Cancel As Integer)
If Me.Status = "Open" Then
me.Status.backgroundcolor = #FFFFFF
End If

If Me.Status = "closed" Then
me.Status = #A5A5A5
End If
End Sub

Also tried changing line 3 to

me.Status.backcolor = #FFFFFF

with no success.

Also tried conditional formatting and for some reason it does not change the background - only the text colour.

Any ideas?

 

[pbaldy]

The provided code would only be appropriate in the format event of the section containing the controls. That said, I would still use Conditional Formatting. If it's changing the text color, that's what you selected. The icon for the back color looks like a little bucket.

 

[krutoigoga]

The provided code would only be appropriate in the format event of the section containing the controls. That said, I would still use Conditional Formatting. If it's changing the text color, that's what you selected. The icon for the back color looks like a little bucket.

Thanks man, I have used conditional formatting before in excel and am familiar with it. The report does not display the background color and i could not figure out why.
Could you expand a bit more on "format event of the section containing the controls"?

 

[pbaldy]

You have the code in the report's open event. You want it in the format event of the section containing the status, which I assume is the detail section. Double clicking on the section should bring up its properties.

 

[krutoigoga]

Sorry guys - I still cannot find where to insert the provided code.
Please see the two pictures attached. "Con-for" is how the conditional formatting is set as (when print previewing report BG colour does not change)
Other picture (Status) is of the property sheet tabs - Can you please direct me where to add the code above?
Thank you

 

[pbaldy]

The conditional formatting setup looks fine, providing the value returned is appropriate. Make sure it's not a numeric key value instead of the text value.

To use code, as I mentioned you'd double click on the bar that says "Detail" to bring up its properties. Then you want its format event.

 

[krutoigoga]

Followed the instructions to add code to Detail properties (see pic "Detail").
The code was giving me syntax errors (see pic "error"). Made a slight change to the code:

If Me.Status = "Open" Then
Me.Status.BackColor = 16777215
End If
If Me.Status = "Closed" Then
Me.Status.BackColor = 12632256
End If

16777215 being "white" and 12632256 grey
(referenced http://www.utteraccess.com/forum/Convert-Hex-RGB-Access-t113526.html).
Still cannot get the background color to change.
The data type is "Text".
any other ideas?
Please help
Thanks

 

[krutoigoga]

Forgot to attach pictures

 

[gemma-the-husky]

try the print or format event of the section (detail?) in which the textbox appears. i doubt if the open event is any use.

 

[pbaldy]

try the print or format event of the section (detail?) in which the textbox appears. i doubt if the open event is any use.

You must not have read the thread Dave.

I don't have Access on this machine, but I'm pretty sure it's

Me.Status.Backcolor

While it's in debug mode, hover over

Me.Status

and see what value is in the control.

 

[krutoigoga]

You must not have read the thread Dave.

I don't have Access on this machine, but I'm pretty sure it's

Me.Status.Backcolor

While it's in debug mode, hover over

Me.Status

and see what value is in the control.

Me.Status.BackColor. The code I have now is:

If Me.Status = "Open" Then
Me.Status.BackColor = 16777215
End If
If Me.Status = "Closed" Then
Me.Status.BackColor = 12632256
End If

Report print preview does not show any change in background color.
Everything else seems to work ok in the report - controls are ok, displays required data and proper status.

 

[pbaldy]

Did you confirm via debug what the contents of the field are? Are you sure you're in Print Preview mode? In the new Report view, the detail format event does not fire.

 

[boblarson]

Make sure the controls' back style is not set to TRANSPARENT but instead set to NORMAL.

 

[krutoigoga]

Paul,
I have attached a sample database, would you be able to review it please?

 

[krutoigoga]

Did you confirm via debug what the contents of the field are? Are you sure you're in Print Preview mode? In the new Report view, the detail format event does not fire.

p.s. I know you do not have access to access right now. I checked the code in debug mode. Not sure what I was looking for. Was it about the backcolor?

 

[boblarson]

Paul,
I have attached a sample database, would you be able to review it please?

Just as I suspected - the Conditional Formatting WILL work if you change the text box's BACK STYLE property from TRANSPARENT to NORMAL. They are all currently set to TRANSPARENT.

 

[boblarson]

Oh, and by the way - if you want to see it in REPORT VIEW (the ON FORMAT event will do it for Print Preview and Print) you will need to use the same code in the ON PAINT event for the section. You will probably want to put a function in the Report's module and then just call it from both places.