MS-ACCESS Conditional Formatting while scrolling

Question

I have a continuous subform (Data) within a single form (Main) where I use conditional formatting. The problem is, when I click the scroll bar to go down the list, the conditional formatting is removed until I un-click the scroll bar. Scrolling through the list with the mouse wheel works perfectly.

I suspect this is due to a refreshing or repainting of the formatting that is inherently triggered by a click?

I tried using Application.Echo False, and Me.Repaint = False, with no luck (although I am not even sure where I would need to put these).

Does anyone have any experience with this? Note, the data displayed via subform Data is static, meaning the user cannot update the fields within this particular subform (not sure if that simplifies anything).

Answer 1

On a continuous form, conditional formatting is only performed on records that are within view. As new records are scrolled into view, there is a brief delay while the conditional formatting is calculated. As records are scrolled out of view, their conditional formatting is discarded. You should find that scrolling with the mouse wheel does have a bit of a delay in the conditional formatting.

In some situations, one solution is to build the conditional formatting into the recordsource. For example, suppose negative values for Balance should be shown with a yellow background, and you would like to be able to scroll quickly through all the records looking for yellow ones.

I do this by adding another field to the recordsource:

IIf([Balance] < 0, String(30,ChrW(9608)), "") AS BalanceBG

and then binding this to a TextBox with text color of yellow and font of Courier.

The 9608 character is a solid block, and a string of them fit tightly together to make a solid bar.

With this TextBox positioned behind the TextBox for [Balance], the yellow background will show even as the scrollbar thumb is dragged up and down.