Macro to refecence current control

[Theresa]

I have a number of controls (lables) that when clicked I want the value
(caption) to be transferred to another control (textbox). So I don't have to
do this code ten times I want a macro to set the value of the textbox to the
value (caption) of the control that called the macro.
How do I reference the current control from the macro without having to used
the specific name of the calling control?

 

[Theresa]

Thanks Linq, but I know this. What I want to do is simply acces one macro
from several controls with out having to write separate code in each control
except for accessing the masro. The time is saved by using a macro that will
perform the same funstion regardless of the control that called it.

 

[Steve Schapel]

Theresa,

I don't think you can do this directly with a Label.

If you used a textbox (formatted to look like a label), or a command
button, then you could use a SetValue macro, with the arguments set to:
Item: [NameOf YourTextbox]
Expresion: [Screen].[ActiveControl].[Caption] (for command button)
Expresion: [Screen].[ActiveControl] (for textbox)

Even neater would be to get a command button, the same size as the
label, put it over the top of the label (you may need to use the 'Bring
to Front' command), set its Transparent property to Yes, and set its
Caption the same as the caption of the label. Then apply the macro to
the Click event of the command button instead, arguments...
Item: [NameOf YourTextbox]
Expresion: [Screen].[ActiveControl].[Caption]

That way you get the functionality of the combobox (allowing the use of
ActiveControl property) but retaining the appearance of the label.

 

[Theresa]

Hi Steve,
I just tried your code and received the message 'You can't assign a value to
this object'.
Any other suggestions?

Theresa,

I don't think you can do this directly with a Label.

If you used a textbox (formatted to look like a label), or a command
button, then you could use a SetValue macro, with the arguments set to:
Item: [NameOf YourTextbox]
Expresion: [Screen].[ActiveControl].[Caption] (for command button)
Expresion: [Screen].[ActiveControl] (for textbox)

Even neater would be to get a command button, the same size as the
label, put it over the top of the label (you may need to use the 'Bring
to Front' command), set its Transparent property to Yes, and set its
Caption the same as the caption of the label. Then apply the macro to
the Click event of the command button instead, arguments...
Item: [NameOf YourTextbox]
Expresion: [Screen].[ActiveControl].[Caption]

That way you get the functionality of the combobox (allowing the use of
ActiveControl property) but retaining the appearance of the label.

--
Steve Schapel, Microsoft Access MVP

I have a number of controls (lables) that when clicked I want the value
(caption) to be transferred to another control (textbox). So I don't have to
do this code ten times I want a macro to set the value of the textbox to the
value (caption) of the control that called the macro.
How do I reference the current control from the macro without having to used
the specific name of the calling control?

 

[Steve Schapel]

Theresa,

You need to substitute the actual name of your textbox for the [NameOf
YourTextbox] in my example. Did you do that? If so, please post back
with the details of what you have now in your macro.

 

[Theresa]

Hi Steve,
Yes I did use the textbox name. I have tried using a button (transfer
caption to textbox). I have also tried transfering values from textbox to
textbox and I still get the same message.
The macro reads as follows:
MacroName: TransDat
Action: SetValue
Item: [Text22].[Value] 'I have even tried it without the [Value]
Expression: [Screen].[ActiveControl].[Caption] ' when using the button

Theresa,

You need to substitute the actual name of your textbox for the [NameOf
YourTextbox] in my example. Did you do that? If so, please post back
with the details of what you have now in your macro.

--
Steve Schapel, Microsoft Access MVP

Hi Steve,
I just tried your code and received the message 'You can't assign a value to
this object'.
Any other suggestions?

 

[Steve Schapel]

Theresa,

Yes, you are right, there is no need for .[Value] as per my earlier example.

I am very sorry, I can't think of a reason why this would not work. I
have just tested it here with a number of different scenarios
(bound/unbound, locked, enabled) and I have not been able to get the
same error. So I'm afraid I'm stuck, apart from to re-assert that this
the method I have given you is normally a valid and correct one. All I
can suggest is to experiment with it, maybe try again on another form,
just with a textbox and command button, and see what you can figure out.