How do I write a Module to be called when a control on a form is clicked?

I have a form with a  number of text boxes on it. There is a list box for "Task". Depending on the task selected I want any of the text boxes clicked to change its back color. How can I write a module that can be called when a text box is clicked rather than write code within every individual text box on the form?
LVL 11
EMCITAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Easy.
Select all the text boxes at once, then enter the name of the Function on the On Click event on the property sheet - see image.

mx
Capture1.gif
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Create your Function either in the Form module or a regular vba module ... either way works.

mx
0
EMCITAuthor Commented:
In the On-Click of each text box I need a module to call that will identify which control has been clicked, determine the appropriate color and backcolor that control.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:

Sure.  Lets say this is your Function (you don't 'call' modules, you call Functions in Modules).

Public Sub mMyFX()
Dim x As String
Dim y As Variant

x=Screen.ActiveControl.Name  ' name of control clicked
y=Screen.ActiveControl  ' returns an object of the control clicked.

' other code for color

End Function

mx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
EMCITAuthor Commented:
On my form the list box is named WhichTask. When clicked the color for that task (eg-255) is held in a field called HoldColor. Then I have text boxes A, B, C, D, E, F, G, H, I, J. Based on this:

Public Sub mMyFX()
Dim x As String
Dim y As Variant

x=Screen.ActiveControl.Name  ' name of control clicked
y=Screen.ActiveControl  ' returns an object of the control clicked.

x.backcolor = Forms!Form1.HoldColor ' other code for color

End Function

Then on the OnClick for all the textboxes I put = mMyFX()

This looks like it should work but I'm getting an error: The expression you entered has a function name that MS Access can't find.

0
EMCITAuthor Commented:
I got it! I had to change "Public Sub mMyFX()" to "Function mMyFX()"

Thanks much for the help!
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Yes ... no can do Sub ... on Function when calling from prop sheet.  Which is one of the many reasons I never use Subs ... I just see no need.

You are welcome.
mx
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.