Modifying a controls LostFocus event at runtime

[lution]

I have a listbox with 2 columns: an error message, and the form control with the error. The 2nd column is hidden from the user. What I'm trying to do is when the user clicks on a row in the textbox it will setfocus for the control with the error. That part I got to work but even in my simple testing I noticed it was hard to see that the focus had changed to a different field so... what I'd like to do is change the background color for the error field and then when it looses focus set the background color back to the default. I can set the background color fine example:

strfield = Me.mylistbox.column(1)
Forms("Ticket").Controls(strfield).BackColor = dbYellow

But I'm having a hard time changing the lost focus event for the control at run time. If I do what I did for BackColor I get a "Object doesn't support this property or method"

example: Forms("Ticket").Controls(strfield).LostFocus = "somefunction"

I guess I could set the lostfocus event on every control to "somefunction" on the properties tab but that seems like overkill. Thoughts?

 

[MSAccessRookie]

I have a listbox with 2 columns: an error message, and the form control with the error. The 2nd column is hidden from the user. What I'm trying to do is when the user clicks on a row in the textbox it will setfocus for the control with the error. That part I got to work but even in my simple testing I noticed it was hard to see that the focus had changed to a different field so... what I'd like to do is change the background color for the error field and then when it looses focus set the background color back to the default. I can set the background color fine example:

strfield = Me.mylistbox.column(1)
Forms("Ticket").Controls(strfield).BackColor = dbYellow

But I'm having a hard time changing the lost focus event for the control at run time. If I do what I did for BackColor I get a "Object doesn't support this property or method"

example: Forms("Ticket").Controls(strfield).LostFocus = "somefunction"

I guess I could set the lostfocus event on every control to "somefunction" on the properties tab but that seems like overkill. Thoughts?

I don't think that Me.mylistbox.column(1) is the correct syntax. The individual columns do not have the property that you want to modify, but instead, the whole listbox does. Would Me.mylistbox work for you instead?

 

[pbaldy]

Look at Format/Conditional Formatting. You can format based on focus.

 

[lution]

Thanks pbaldy, that helped me find what I was looking for. I wasn't using the correct property name. I also had to play with the string format some to get it to work.

End result

I created a function:

Public Function SpecialEffectExit()
   screen.activecontrol.SpecialEffect = 0 ' set back to flat
   screen.activecontrol.BackColor = RGB(255,255,255) ' set back to white
   screen.activecontrol.Properties("OnLostFocus") = "" ' clear out the lost focus event
end function

Then on my listbox, in the OnClickEvent I have the following:

...
   strField = Me.lstErrors.Column(1) ' pulls the name of the control with the error from the list box
   Forms("Myform").Controls(strfield).Visible = True ' not sure why but if I don't the set focus doesn't work
   Forms("Myform").Controls(strfield).SetFocus ' move focus to the field with the error
   Forms("Myform").Controls(strfield).BackColor = RGB(255,255,0) ' change the back color to yellow (actually I do this with a const but wanted to show what it was for others)
   Forms("Myform").Controls(strfield).OnLostFocus = "=SpecialEffectExit()"  ' you need the = and the () otherwise you'll get an run time error.
 
....

What this does is change the background color to yellow and set the focus on controls where the user has errors but then as soon as they move to a different field, it sets the control back to the defaults (well my defaults anyway). I don't want to leave the OnFocus color changes because I expect the user to have corrected the error.

-Lution