Link to home
Start Free TrialLog in
Avatar of Rick Danger
Rick DangerFlag for United Kingdom of Great Britain and Northern Ireland

asked on

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.
ASKER CERTIFIED SOLUTION
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Rick Danger

ASKER

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?
Yes, it will. But you have only four controls ... to paste that codeline four times is faster than just considering any other solution.

/gustav
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
OK, but I have more than 4 fields potentially, and I need to do this across several reports.
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()
Thanks to Gustave for your help.

But thanks also to Dale Fye for his very generous clarification
You are welcome!

/gustav