Access 2013 VBA - retrieving name of clicked field

I am using this code to put the value of a field into a MsgBox.
Private Sub pre_race_comment_Click()
field_name = Screen.ActiveControl
If Not IsNull(field_name) Then
    MsgBox field_name
End If
End Sub

The reason is that I have 4 fields which are very long, and do not fit into the report, so they are truncated in the report. If they click on one of these fields, it brings up the whole field in a MsgBox. But it would be much better from my point of view of they could click on any field in the report, and it brings up the whole value in a MsgBox.

So what I would need is for the VBA to be triggered by a report event rather than a field event, then the VBA would work out which field was clicked, and bring it up in a MsgBox.
rick_dangerAsked:
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.

Gustav BrockCIOCommented:
You can have:

Private Sub pre_race_comment_Click()
    Call ShowValue
End Sub

Private Sub post_race_comment_Click()
    Call ShowValue
End Sub

Private Sub ShowValue()
    MsgBox Nz(Screen.ActiveControl.Value)
End Sub

/gustav
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
rick_dangerAuthor Commented:
Gustav
Thanks, but doesn;t this require me to use this code against each individual field? I need it to be able to determine which field was clicked, and then display that value. But I only want to do this once, at report level, not field level, as per my question. Is this possible?
0
Gustav BrockCIOCommented:
Yes, it will. But you have only four controls ... to paste that codeline four times is faster than just considering any other solution.

/gustav
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.

Gustav BrockCIOCommented:
You can also use WithEvents, but that takes a little. Here is an intro:

http://jwcolby.blogspot.dk/2013/02/object-wrappers-and-event-sinks-problem.html

/gustav
0
rick_dangerAuthor Commented:
OK, but I have more than 4 fields potentially, and I need to do this across several reports.
0
Dale FyeCommented:
No points please.

An easier method would be to change Gustav's ShowValue subroutine into a function.  By doing that, you could then open your report in design view, select all of the controls where you want this functionality, open the properties dialog, and on the event tab, simply enter:

On Click: =ShowValue()
0
rick_dangerAuthor Commented:
Thanks to Gustave for your help.

But thanks also to Dale Fye for his very generous clarification
0
Gustav BrockCIOCommented:
You are welcome!

/gustav
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 Access

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.