contitional format text box

[109bow]

I am trying to conditional format a text box to make text turn red if the number in the box is less than or equal to the number in 3 other text boxes in the same report, is this possible?

 

[Minty]

Yes - use the conditional formatting function. Right click the control to access it. You will need to use the expression option. Something like

[YourField] <= 3

 

[Ranman256]

instead of Conditional Format, use vb code in ON CURRENT event.
maybe even in all text box AFTERUPDATE events.

sub form_OnCurrent()
   Colorize
end sub

sub txtMain_Afterupdate
    Colorize
end sub

sub Colorize
if txtBox1 <=3 and txtBox2 <=3 and txtBox3 <=3 then
    txtMain.backcolor = vbRed
else
    txtMain.backcolor = vbWhite
end if 
end sub

 

[109bow]

Gents, thanks for your help. Unfortunately I don't yet understand VB, so was trying to use conditional formatting.
What I'm after is along the lines of IF "Text box1" <= "Text box2" OR "Text box3" or "Text box4", then text in Text box1 changes colour to red. All text boxes have a numerical value that are based on a query. If anyone can help very grateful.

 

[Minty]

You have to refer to the control names in square brackets otherwise it thinks you are referring to a string value. So in your example

[Text box1] <= [Text box2]  OR [Text box1] <=[Text box3] OR [Text box1] <=[Text box4]

Notice how you need to repeat the entire clause - each part of the OR must be a full expression.

 

[sneuberg]

You can set up conditional formatting for the text box1 like this.

which you can see work in the attached database. Some things to note about this are.

• No "if" is required in these expressions. You just write an expression that will be true when you want the formatting applied.
• I added the CDbl function as I found the textbox values were being handled like strings. You may not need this if your textboxes are bound to numeric fields.
• I first tried this with one rule. The expression I tried was CDbl([Text box1]) <= CDbl([Text box2]) Or CDbl([Text box1]) <=CDbl([Text box3]) Or CDbl([Text box1])<= CDbl([Text box4]) but I could not get it to work. I think it should work and I can't explain why is doesn't. It might work when the textboxes are bound to fields.
• I suggest that you give your controls names that don't have spaces in them, i.e., textbox1 rather than text box1 or better yet give them names that mean something. But when writing expressions for conditional formatting it is best to put them in brackets [] anyway as Access might assume they are a string value.

 

[109bow]

snueberg, thanks for your reply. I have this formatting to repeat 16 time on the one form, so was hoping that one expression for each text box would work, but never mind once its done its done! Thanks for your help

 

[sneuberg]

snueberg, thanks for your reply. I have this formatting to repeat 16 time on the one form, so was hoping that one expression for each text box would work, but never mind once its done its done! Thanks for your help

If you just wanted to apply the same formatting to multiple textboxes all you would have had to do is select them all and then select conditional formatting. Sorry you had to go through all that.

 

[Minty]

If you are using a complex calculation like that lots of times create a calculated field in your query that you then reference on the form - so in your query

TextRed: Your complex calculation goes here, and evaluates to 1 or 0

Then add TextRed as a hidden control to your form and your conditional expression becomes simple.

 

[sneuberg]

You have to refer to the control names in square brackets otherwise it thinks you are referring to a string value. So in your example

[Text box1] <= [Text box2]  OR [Text box1] <=[Text box3] OR [Text box1] <=[Text box4]

Notice how you need to repeat the entire clause - each part of the OR must be a full expression.

@Minty This expression works as is. But if I change it to

CDbl([Text box1])<=CDbl([Text box2]) Or CDbl([Text box1])<=CDbl([Text box3]) Or CDbl([Text box1])<=CDbl([Text box4])

it doesn't seem to see anything beyond the first Or. Do you happen to know why this is?

 

[Minty]

Possibly a rounding error if the values aren't exactly the same. I wouldn't used CDbl unless there was an explicit reason to do so personally.

 

[109bow]

All thanks for your help, I have got the following to work;
[Text1]<=[Text2] And [Text1]<=[Text3] And [Text1]<=[Text4]