# Solved: Access - Conditional Formatting



## commander (Jun 4, 2001)

Can someone tell me if the following is possible. I have a very basic database, and one field on a form has the input options of "Completed" or "Outstanding". 

What I want to do is change the bacground colour of a different field to green when the other field is showing as Completed. 

I understand how I can change a given field depending on conditions in it's own field, but I can't see how to change conditions of field A depending on the condition of Field B.

Help much appreciated as always.


----------



## OBP (Mar 8, 2005)

Peter, this is a quote from the Access Help on conditional formatting
"You can set conditional formatting based on the value in a control, an arbitrary expression referencing another control"
Which says that you can do it.
I have never used it as I use VBA for this kind of operation as it gives more control over the formatting.
I will have a look to see if I can figure it out.
If you want to use VBA give me the names of the two fields involved and I will write some for you, please state which field name has the "Colour to be changed" and which one is the "control" for changing it.


----------



## commander (Jun 4, 2001)

Thanks OBP, yes I had seen that in the "help", but I can't figure out how to get it to work.

The two fields are "Status" and "Details" It is the "Details" field which I would like to turn green when "Status" = "Completed"

This is really just a "nice to have" so please don't spend a lot of time on it. I thought I might get a reply saying, "No this can't be done", in which case I could just have forgotten about it and moved on, but untill I crack it or am told it really can't be done I guess I will keep knawing at it!

Thanks for your interest.

Peter


----------



## OBP (Mar 8, 2005)

Peter, I couldn't get it to work either.
Have a look at the attached database.
There is VB code in the Frm's "On Current" event procedure which will handle any records tha you already have set to "Complete" and also in the "Status" field's "After Update" event procedure to change the colour as you change the status.
If you don't like the colour (I am a bit colour blind) just change the "Number" in the VB to the on ethat you want.


----------



## Jimmy the Hand (Jul 28, 2006)

The heart of conditional formatting is how you write the expression. It should be
[Forms]![NameOfForm]![NameOfControl] = Value
where "Forms" is a fix expression, "NameOfForm" and "NameOfControl" should be replaced by the appropriate names. A little example is attached. The editbox changes background color according to the state of the checkbox. I'm sure you can figure out the rest.

Edit:
I had to create an OnClick event for the checkbox, which makes a refresh of form, because otherwise the color change doesn't occur.


----------



## commander (Jun 4, 2001)

Thanks guys, OBP, your solution seems to do just what I want, I will try to incorporate it in the database when I am back in the office on Tuesday.

Jimmy, I get an error message saying an error occurred while Access was communicating with the OLE server or Active X control 
- the expression may not result in the name of a macro, or a user defined function, or [event procedure]
- there may have been an error evaluating the function, event or macro

but I can kind of see how it should work, I hadn't realised you could use an expression like that in the conditional formatting. I'll look into that too.

Thanks again chaps.


----------



## Jimmy the Hand (Jul 28, 2006)

commander said:


> Jimmy, I get an error message saying an error occurred while Access was communicating with the OLE server or Active X control
> - the expression may not result in the name of a macro, or a user defined function, or [event procedure]
> - there may have been an error evaluating the function, event or macro


I didn't expect that kind of error, though I should have, because I had it before when someone with English windows wanted to run my Access applications. I think if you remove the checkbox's OnClick event from my sample database, the error goes away. In this case, however, you will have to initiate a Refresh action on frmTable1 yourself, in order to see background color of the editbox changing. Refreshing the form can be forced by stepping forward to next record then back to this one. 
But I think the concept is easy enough to grasp, so you should experiment towards your own goals, and not spend too much time on sorting out this language realated incompatibility issue.


----------



## commander (Jun 4, 2001)

Just to say I have this working fine thanks, so I'm going to mark this solved.

Another TSG success.


----------

