Conditional Formatting Problem

[ExcelMan]

I have two text boxes - let's call them txtBox1 and txtBox2 -- on a
form that both have a fairly complex DLookup function as their
controlsource.

I want the back color on txtBox2 to be pale yellow when it has a higher
value than txtBox1. So I set up an Expression Condition as:

[txtBox2] > [txtBox1]

Problem is, that it only updates after I click on txtBox2, not when
other controls on the form cause the value of txtBox2 to update to an
amount greater than txtBox1. Funny thing, both controls are meant for
display only and are enabled, but locked. However, when the
conditional formatting is active, I can actually click on the text
boxes and they take the focus.

I've tried using code to Requery the txtBox2 when the other controls
change, but that is way too expensive and doesn't even seem to work
properly.

Any ideas?

Thanks.

 

[Allen Browne]

There are problems with CF, particularly with calculated controls, so there
may not be a good way to solve the problem you are experiencing.

Calculated controls are low-priority to Access. You can force it to update
them with:
Me.Recalc
But be careful you don't put that into an event that changes something else
and triggers an endless loop of events. (Very easy to do with CF and calc
controls.)

You could try creating a query to act as the RecordSource of the form, and
moving the calculations into the query. Since they are then in a source
field of the form, the CF may work better.

If this form is not intended to be edited, you could then use a subquery
instead of a DLookup() in the source query, and it will be *way* more
efficient. If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

DLookup() is very inefficent. A slightly more efficient replacement is here:
http://allenbrowne.com/ser-42.html

The problem with conditionally formatted fields taking focus even if they
are disabled is also a known issue.

If txtBox1 and txtBox2 have numeric results, you might be able to help
Access make the comparison between them more reliably by setting their
Format property to General Number. This helps Access understand the intended
data type of the lookups/calculations.

Hopefully some of that proves useful for you.

 

[ExcelMan]

Thanks Allan for the guidance. I was experimenting with Me.Recalc and
beginning to beat my hed against the wall. Time to get off this path
and try a different approach.

Thanks.