Conditional Format doesn't work

[JimS]

I have a text box (more like 50 of 'em) on a simple form. I went to set a
conditional format for 30 of them at once, and the conditional formatting
didn't seem to work. I tried several variations, but it just failed to
perform properly.

I tried, instead, to just format one box at a time. That also failed. It's
simple, I want the box to turn red when the contents of the box is less than
0. I realize it's a "text" box, but I've had this work for me before, why not
now? What should I check? How can I debug this?

I'm using AC2003 with AC2000 db format. Been doing cond format for many
moons on this db. Sometimes it acts up, but usually it's a rounding issue.

Help!

 

[JimS]

They are text boxes, which are, I assume type Text. I am using the simplest
form of the conditional format..."Field is less than 0".

 

[Klatuu]

The data type is determined by the bound field. If it is a text field, you
might try
< "0" If it is a numeric field then < 0 should be working.

 

[JimS]

unbound box
--
Jim

The data type is determined by the bound field. If it is a text field, you
might try
< "0" If it is a numeric field then < 0 should be working.

 

[Klatuu]

Can't recall if I've ever done any Conditional Formatting on an unbound text
box.
Wish I could be more help.

 

[JimS]

OK, I tried this:

"The Expression is: text77 < 0", and for some reason, it seems to work. I do
have scores of these boxes to code. Is there a way to code them using vba?
Can I set conditional formatting using vba?

 

[Klatuu]

I don't believe you can control conditional formatting from VBA, but you can
control the format of controls through VBA. You would want to use the
Forecolor property.

You could loop through all your controls and turn the text boxes that are <
0 red and >= 0 black using a routine like this:

Private Sub SetColors()
Dim ctl As Control
For Each ctl In Me.Controls
If ctl.ControlType = acTextBox Then
If ctl < 0 Then
ctl.Forecolor = vbRed
Else
ctl.Forecolor = vbBlack
End If
End If
Next ctl
End Sub

But, then the question is where to call it from. Since I don't know
anything about your form, I can't help with that.

Now, if you need to do it on a control, by control basis, you could use the
AfterUpdate event each control to call a function like this:

Private Function SetTextColor()
Dim ctl As Control

Set ctl = Screen.ActiveControl
If ctl < 0 Then
ctl.Forecolor = vbRed
Else
ctl.Forecolor = vbBlack
End If
Set ctl = nothing
End Function

Then just put this in the Control Source box for each text box in the
properties dialog

=SetTextColor

 

[JimS]

Dave, you've been very helpful, thank you. I have a fairly simple set of 5
text boxes across and about 20 down. Each time a new record is selected (on
current...), I clear all 100 text boxes, make them invisible, then
recalculate their contents, and make them visible if it's appropriate. It's
essentially a datagrid, but a little more complicated. Would that I could use
a datagrid of some type. Or a VB6 array of controls....

Anyway, I use the box name to access its attributes in the controls
collection. It appears that conditionalformat is one of the objects in the
collection, and it can be set pretty much as easily as in the wizard. So, I
think I'll write myself a little code to rifle through all the 100 boxes and
set their conditional code. It'll be good for me.

I can't use an event because no event will be fired. VBA loads the box. but,
of course, I could just have vba set the colors when it loads them up. That's
also a consideration.

Thanks, Dave.

Jim

 

[Klatuu]

Sometimes I forget about the differences with unbound controls because I
seldom use them except for combos to use for searching. So if I remember,
you are correct. the After Update event might not fire, but the Lost Focus
will. Or are you saying you don't enter values manually, that they are
populated programmatically?

In any case, it seems you pretty much have it under control now.
Good luck.