Conditional formatting custom colour / color

[Christine Pearc]

Is it possible to set a custom background color for conditional formatting? Don't particuarly want to do it via code, but I suspect it might be the only way. If coding is required, can someone advise how and where to put it?

You see, I have a form with a custom background colour. In the conditional formatting criteria, all I want to change is the font color for the control, but when the critiera is set it automatically changes the background to white.

Cheers,
Christine

 

[Tim L]

Yes, see the attached picture.

If you want to use code (which is more flexible) then something like this might help you.

If [B]condition[/B] then
     me.controlName.backcolor = vbRed
else
     me.controlName.backcolor = vbWhite
end if

Will change the background colour of a control depending on whether the condition is met or not.
(vbRed and vbWhite are constants defined in VBA - search access VBA help or define your own additional ones)

Where condition might be something like

me.controlName.value = aValue [COLOR=DarkGreen]' has a certain value[/COLOR]
Not IsNull(me.controlName.value) [COLOR=DarkGreen]'is not null[/COLOR]

If you want the change to occur after entering data use the AfterUpdate event, if you want the change to occur when you move to different records use the OnCurrent event.

Note: Conditional formatting can only be used on a control whose value can be measured; if you use code you can change the format of one control based on the value of another, thus you can change the background of a form based on the value of a control.

Tim

 

[godofhell]

I have attached a database that changes the color of the field that has been edited using conditional formating. Check it out.

 

[jrjr]

For the life of me I can't see how you did this. The form properties say that it has a module but yet there are no modules listed. In the properties of all the different items, I see no code that changes color. I do see where a dependency is listed for fc but no color value is listed in vb anywhere.....

Yet it works, could you explain this for me??

Bewildered/confused

 

[Tim L]

jrjr,

godofhell just used Conditional Formatting, as mentioned by Christine who started the thread.

To find out more about Conditional Formatting search the Access help file using that term. To get into it quickly select a control (such as a text box - something that can have a changing value) and then click on the menu, Format\Conditional Formatting...

Tim

 

[godofhell]

Tim L, thank you for clarifying this for him. That is correct on the Main Menu of the application you will see FORMAT\Conditional Formatting. Don't feel bad I had the same problem trying to find it when I read about it.

 

[Christine Pearc]

Thank you Tim.

Christine

 

[reeve13]

I would like to add this in a form but i noticed when I exit out and go back in it, it does not save the highlights. How do I get it to save the highlights?

Thanks.

 

[Tim L]

I would like to add this in a form but i noticed when I exit out and go back in it, it does not save the highlights. How do I get it to save the highlights?

That would be the correct behaviour. The 'update' flag is only set during a single viewing of a record, once the form has been closed, or a different record has been displayed, the flags are reset.

If you would like to continually 'track' which fields have been updated you will need to do several things:

• Add an 'updated' date/time field for each field that you wish to change the formating.
• Add a control field (possibly two, or even more), with date/time information on which to base your comparisons.

You could then compare each field against the control field(s) and set the formatting to something different for those that have been updated since the control field was last changed. This is why you might want two or more control fields. One to record the initial date the record was created and one to record the last date any changes were made. You could then indicate using conditional formatting which field(s) had been updated after the creation of the record. Of course, if you have multiple fields updated on different dates then you are likely to end up with most, if not all, of the fields eventually showing (formatted) as having be changed at some point, is this really what you want?

You could also, although this might lead to a cluttered form, actually display the date/time that that field was specifically updated.

If you are intending to go as far as keeping track of changes perhaps it would be better to just create a new record, along with the date, each time some of the information was changed. You could then, somehow, compare the current record to the previous one and highlight changed fields that way. In this manner you would have a changes history since the record was first created.

HTH

Tim