Form Conditional Formatting Re-evaluation

[johnthemon]

I have a form for the SalesLead table that has a disabled and locked text box that displays a SuggestedClientName. I use conditional formatting to shade red any suggested ClientName (primary key) values that have no matching row in the Clients table (a different table from the record souce of the form). The expression counts the number of matching rows in the Clients table that match the SuggestedClientName value and then subtracts 1. If there is a matching entry, the expression returns 0, i.e. False. If there is no matching entry, the expression returns -1, i.e. True. Since ClientName is the primary key of Clients, the only possible results of the expression are 0 and -1.

Here is the expression:

DCount("ClientName","Clients","ClientName=[SuggestedClientName]")-1.

So far, so good. Works great when I load the form.

One way to handle this discrepancy is for the user to pull up the Clients form and add the new ClientName value. After adding the SuggestedClientName to the Clients table, the user returns to the SalesLead form. I want the conditional formatting to re-evaluate the SuggestedClientName text box in the form when the focus is returned to the SalesLead form.

This is where my problem lies.

No matter what I try, I have to close the form and re-open it to re-evaluate the conditional formtting. I tried adding the following VBA code to the form's On Got Focus event.

Private Sub Form_GotFocus()
Forms![SalesLead].[SuggestedClientName].Refresh
End Sub

I also tried Requery and Recalc. No luck.

Do I have the wrong event? Do I have the wrong code? Does the disabled and/or locked settings have anything to do with my problems?

HELP!!!

[johnthemon]

while waiting for a reply, i kept searching forums. and i found the solution. turns out i had the wrong event. i added the same code to the form's On Activate event. worked like a charm!

whew!

still not sure why On Got Focus did not work. maybe somebody can explain the difference between these two events. thanks in advance!

[johnthemon]

p.s. also had to change the code to requery. refresh and recalc kicked out as invalid.

[NeoPa]

John, you can look in Help for quite detailed explanations of each event and what they do. In this case there is nothing I could find to explain why moving from another form doesn't trigger the form's GotFocus event, but I suspect that this is only triggered when the form, and not any of its controls, gets the focus. Essentially when the form is clicked on itself and not any of its controls. So, if the form itself had the focus before another was opened then when the new one is closed the focus revert to the form. If a Command Button had the focus however, the form's GotFocus event wouldn't fire.

Does that make sense?