Solved Update broke Conditional Formatting? (1 Viewer)

Gasman

Enthusiastic Amateur
Local time
Today, 21:27
Joined
Sep 21, 2011
Messages
17,260
I updated from 19029.20184 to 19029.20208 in helping a member on another forum. I have Office 2019.

Now I have discovered that my CF for a control is not working.

AFAIAA I have not modified anything for ages.

I am using an expression Val([txtCarbsCalc])>TempVars("CarbLimit"). I have quickly changed it to a Field Value > 230 and that works, however I would like to use the expression which was working fine. The Tempvar is set on the first form load.

Code:
    TempVars("CarbLimit").Value = 230              ' Carb limit has increased from 130, 206, 225 now 230
Could someone on either version please test and see if they have the same issue please?
I have checked it is set

? TempVars("CarbLimit").Value
230
 
OK, I have got it working by using the Field Value option again, but still comparing against TempVars("CarbLimit")
 
What happens if you try using bang syntax?
Code:
Val([txtCarbsCalc])>TempVars!CarbLimit
 
Spoke too soon. :(
1756033094580.png
 
What happens if you try using bang syntax?
Code:
Val([txtCarbsCalc])>TempVars!CarbLimit
Nope, does not change to Red. :(
I changed it back to Field > 230 and that still shows todays as Red when only 44.42 ?

However If I go back to previous records under 230, they are black?

1756033576870.png
 
Last edited:
I changed it back to Field > 230 and that still shows todays as Red when only 44.42 ?
Conditional formatting can tend to treat anything like text. And vba will coerce comparisions into text on the right side.
That would answer why "44.2" is greater than "230"

So I would do the calculation in the query and return true or false in a calculated control.

IsGreaterThan230: iif([do carb calculation here] > 230, True, False)

Then your CF becomes
Expression is: [IsGreaterThan230] = True

Here is an example of CF coercing to string
 
Last edited:
That would account for why 189 is black.
I am not using a query just the table and that control calculates the total carbs for the day, using DSum()
Even hardcoding 230 and Field Value option shows 44.2 as red. :(

I have never noticed this before and the day always starts off with values like that and just increments during the day.
I'll just do it in code.
 
I am nothing if not stubborn at times. :-)
I have changed it back from code to CF purely as a test amd now 35 shows as black as it always had?
However changing back to CF as an Expression means it no longer works as it should.

1756107060989.png

If I go back to Field Value
1756107169833.png

1756107203289.png

If I reduce that to 14, it becomes Black, but putting back the correct value it turns to red.

This was all working fine, as it should before the update. :(
Anyway, I have a workaround, but really that has mucked up CF immensely. :(
 
What if you coerce the expression TempVars!CarbLimit to CSng(TempVars!CarbLimit)? Does that change anything?
 
I updated from 19029.20184 to 19029.20208 in helping a member on another forum. I have Office 2019.

Now I have discovered that my CF for a control is not working.

I tried to reproduce this issue based on your description in A365 V2507 Build 16.0.19029.20208 32-bit, but I had no luck.
Using the expression Val([txtCarbsCalc])>TempVars("CarbLimit") does work as expected for me.

If you want to pursue this further, I suggest you create and upload a minimal sample db that reproduces the problem on your end.
 
What if you coerce the expression TempVars!CarbLimit to CSng(TempVars!CarbLimit)? Does that change anything?
Nope.
That is what is currently in the db uploaded in CF. Code in form current commented out.

Control is txtCarbsTotal on mainform.
1756111257986.png


I am happy enough being able to do it in code, but then what is the point of CF if it does not work? :(

I *might* find out how to rollback and see if I get back what was working before.
 

Attachments

@Gasman , I suggested *you* create a minimal sample db primarily because in the process it is very likely that *you* will find the cause of the problem.

That is what is currently in the db uploaded in CF.
Excuse me?
There are several problems with this database.
First, there are references missing (Excel, VBA Extensibility) and thus the whole thing does not compile.

But, nonetheless, back to the issue of the conditional formatting.
- The format condition you mentioned in this thread is bound to the control txtCarbsCalcTotal.
- The CF is: Val([txtCarbsCalc])>CSng(TempVars!CarbLimit)
- A control txtCarbsCalc does not exist in this form! A control of that name does exist in the subform, but that control is not referenced by the expression.

I don't think, this could have worked ever.
 
Last edited:
Sorry, I have changed so much in trying to debug this.
I can assure you it was working. I will check a saved version.

Please try a simple Field Value > the tempvar and tell me what you get.
I have now tried that and it highlights 35 despite using @MarkK 's suggestion of Csng()

Sorry I did not realise I would lose all the references. :(
Here is one that compiles.
 

Attachments

OK, stop working on this please.
I have gone back to a backup and copied the CF and now it is working again.:rolleyes:
Honestly, I believe I had not touched that since I implemented the tempvar and not a hardcoded number.

Sorry for wasting people's time. :mad:
 

Users who are viewing this thread

Back
Top Bottom