Change the Colour of a TextBox / Field depending on it's value? (1 Viewer)

[Curious]

Hello fellow programmers,

I have a tricky one for you. We have decided to split our local City up into different zones. Giving each zone a number and assigning it a colour.

I have a form with all the customers who want jobs done in different parts of the city. This form has a text box on it called: Zone - (which has a 3 digit number it it).

I want the colour of the text box to change depending on the Zone number, in the continuous Form view.

So all the customers living in Zone=111, will have a Red background colour (color), all living in Zone=222 will have a Green background colour.

I know conditional formatting through the properties of the Form can be used, but it only allows me to select 3 different situations. I have many more than 3 colours that I want to assign.

I am working on a VB module to define what colour numbers from the MS Access Colour Map will be assigned to each Zone number.

How can I now apply this module to the form, so when it opens, all the customers who live in Zone=111 will have a Red coloured background, those living in Zone=222 will have a green colour background, etc?

 

[Smart]

Try this in your code bit long winded but it works

If YourField = ZoneNumber Then
YourField.BackColor = vbRed
ElseIf YourField = ZoneNumber Then
YourField.BackColor = vbBlue
ElseIf YourField = ZoneNumber Then
YourField.BackColor = vbYellow
ElseIf etc


Else
YourField.BackColor = vbGreen
End If

 

[Ascaphus]

Under which event would you put this?
Matt

Try this in your code bit long winded but it works

If YourField = ZoneNumber Then
YourField.BackColor = vbRed
ElseIf YourField = ZoneNumber Then...

 

[Smart]

I would try the on load event of the form maybe even the on open event

 

[Cosmos75]

After reading this thread, I started thinking about how this could be done and worked up a sample and article.

'Conditional Formating' of controls in a continuous form

If anyone has any suggestions on how to improve it, the article and sample files, please feel free to email me. Or if you think I am doing it wrong and there is a better way, I'd love to hear about that as well!

Hope this helps!

 

[Curious]

If value Then Colour ElseIF value Then Colour Changes all fields

Thank you for all your replies.

So far I have tried what you suggested Smart, to use the If value Then colour ElseIF value Then colour statements. I hadn't thought of doing it this way.

After applying that [Event Procedure] VB module to the OnCurrent property of my form, it will change the colour of all the Field / Text Box depending on the value.

However, once I move to another record with a different Zone number, so that it becomes the Current record, the colour of the Zone field/text box changes for all records on the form. So unfortuantely, this hasn't allowed me to change the colour (color) independantly for each record.

I will be working on your idea next Cosmos75. It looks a bit more complicated. I hope it works.

 

[Cosmos75]

I will be working on your idea next Cosmos75. It looks a bit more complicated. I hope it works.

Curious,

Hopefully, I did a good enough job of explaining it but if not let me know if you need any help figuring it out and I'll try my best to respond quickly.

Good luck!

 

[woodman650]

how about conditional formatting to change the words? So for example... if I've got a value of "0" in a table cell or form field, can I change it to display "NA" instead? =D

 

[Curious]

Change the colour of a text box depending on it's value WORKED!!

Thank you Cosmos75,

Your solution was brilliantly simple and yet highly effective.

I read your instructions and thought I understood what to do, but ran into some trouble when trying to do it from scratch. After going through your sample database though, it was all very clear.

I had 32 criteria to setup and I was able to set up colours for every single one of them to sucessfully change fore colour text to white text for the darker background colours, and keeping the fore colour text black for lighter colours.

I highly recommend your work. You've solved my problem entirely.

Thanks again,

Curious.

 

[Cosmos75]

Curious,

I read your instructions and thought I understood what to do, but ran into some trouble when trying to do it from scratch. After going through your sample database though, it was all very clear.

I'm glad that the article and sample were helpful. And thank you for posting back to let me know. It is very gratifying to know that the time I spent writing the article and creating the sample wasn't a waste.

I had 32 criteria to setup ...

Your solution was briliantly simple and yet highly effective.
...
I highly recommend your work. You've solved me problem entirely.