Conditional Formatting on a Combo Box

[mjoachim]

Hello Everyone!

I have a continuous form that is used to enter time data. I have a couple fields, such as employee number, that are Combo Boxes. The user is able to enter an employee number that is not in the employee table, so in that situation, I set a conditional format to change the control red to indicate the absence of that number.

My problem seems to be the timing of the many calculations occurring...The conditional formatting is taking just long enough to occur that if I use the drop down list of the combo box, the formatting often occurs after I've expanded the list, which then minimizes it on me.

Any thoughts on why the formatting is taking so long to occur, or suggestions of other ways that I could format the control conditionally that won't impact my ability to use the combo box drop down?

I've tried using VBA code to accomplish this, but my approach highlights the selected control of every record on the continuous form, not just the non-matching record. Is there a piece of code that I should be using to work with just the active record?

Thanks for any help!

[dsatino]

go into design view, right click the field, and look for conditional formatting. From there, just build the expression.

[Rabbit]

Dsatino, I believe they have already done that. The issue is that they're having performance problems.

Mjoachim, what is the expression you're using? That's the most likely cause of the performance issue. I assume you're using a domain lookup function? Those can be very computationally expensive.

[jforbes]

You have already figured out that if using a Continuous Form, setting the background color of the Control changes the background color for every instance of that Control and not just the Current Record. So for Continuous Forms, you have to use Conditional Formatting.

The Evaluation of the Conditional Formatting takes place after all the Data is retrieved and displayed to the User as well as all Calculated fields are calculated and displayed. So it's about one of the last things that is done. Not only is it done last, in my experience, it is done in a separate thread that the main thread, so the UI is returned to the User, before the screen has been fully updated.

The first thing I would try is to see if there is anything to be done to speed up the Expression that is being evaluated as the amount of time this takes to apply the formatting is compounded by how many records are displayed. So, inversely, you could lower the amount of records that are displayed to speed up the time it takes to apply the Formatting.

DLookup(), DMax(), DSum() are slow, so if you are using one of them in your Expression, you could attempt to return the data along with your Form's Record Source and then rewrite the Expression to use that instead and it should complete much faster.

I think you get the point on spending time to make the Expression as efficient as possible.

One last thing to consider is turning off the Screen Echo while the Formatting is taking place. I'm not sure if this is even possible or possible in your situation, it might be possible to turn off the screen at some opportune time like this:

Application.Echo False 

Then turn it back on at the right time:

Private Sub Form_AfterFinalRender(ByVal drawObject As Object)
    Application.Echo True
End Sub
 

I have no idea if this will work, it just a random thought.

[mjoachim]

Thank you jforbes and Rabbit for your insight. You have certainly confirmed my thoughts on the root of the issue. I was using a dlookup expression which was the worst for calculation time. Even just having a conditional format for Has Focus was still minimizing the drop list. Ultimately what worked for me, and this is certainly more of a work around than a fix, was to do a text box behind the entry box, assign the formatting to that box and set the entry box to transparent.

If I get some free time, I may play around with screen echo suggestion.

Thank you again for the information!