access control name from the sub

[Souris]

I have several same controls on the form like spin buton.
Because it runs exactly same logic, but its name, I would like to have one
even code called by all the controls.

I need to know the name of the controls to do some actions.

I tried to use name, but it gives me the name of the form.
I tried self but VBA does not recognize it.

How can I access the control name from its events?

Your information is great appreciated,

 

[Klatuu]

Multiple controls cannot share the save event procedure; however, you can do
what you want easily.
Since you have a form and a subform to deal with, my suggestion would be to
create a public function from the event code and put it in a standard module.
Then just call the sub from the on event property in the properties dialog.
You don't need to know the name of the control. You can use
Screen.ActiveControl.

 

[Souris]

Thanks for the message,
I have multi spin buttons and text box to let user enter intger data using
spin button or text box on the form.

I want to synchronized the value between the text box and spin button values.

I have onSpinButtonUpdate even like followin

If Screen.ActiveControl.Value > 100 Then
Response = MsgBox("You may enter up to 100 ", vbOKOnly)
Screen.ActiveControl.Value = 100

End If

If Screen.ActiveControl.Value < 0 Then
Response = MsgBox("You have enter 0 ", vbOKOnly)
Screen.ActiveControl.Value = 0

End If
txtFrequency.Value = spbFreq.Value

Screen.ActiveControl works to access the value and name.
I can move above code to a module to validate.

The problem is that I am unable to synchronize the value of text, since I
can not access text control without its name.

Are there any way to access text box value to synchronize from spin button
vise versa?

Thanks millions again for helping,

 

[Douglas J. Steele]

Does each spin button correspond to a single text box? If so, you need to
introduce a naming convention so that Spin03 is related to Text03.

If Screen.ActiveControl.Value > 100 Then
Response = MsgBox("You may enter up to 100 ", vbOKOnly)
Screen.ActiveControl.Value = 100
ElseIf Screen.ActiveControl.Value < 0 Then
Response = MsgBox("You have enter 0 ", vbOKOnly)
Screen.ActiveControl.Value = 0
Else
Me.Controls("Text", Right$(Screen.ActiveControl.Name, 2) =
Screen.ActiveControl.Value
End If

 

[Klatuu]

Access will not know which spin button relates to which text box. You will
have to control that yourself. I can think of two possibilities.
1. A Select Case statement that determines the matching control name.
2. Use the Tag property of each control to identify its matching control.

 

[Souris]

Thanks millions,

I have name conversion all the spin button have prefix spb and text boxes
have prefix txt and use following code

Me.Controls("txt" & Right(Screen.ActiveControl.Name, Len
(Screen.ActiveControl.Name - 3))).Value = Screen.ActiveControl.Value

I got type mismatch error

Do I missing anything here?

Thanks again,

 

[Souris]

I think that text box value is string and spind button value is integer.
Are there any way convert integer to string and string to integer?

Thanks again,

 

[Douglas J. Steele]

Me.Controls("txt" & Mid(Screen.ActiveControl.Name, 4)).Value =
Screen.ActiveControl.Value

Or if you use Dave's suggestion of putting the name of the text box in the
spin buttons Tag property, in which case it would be:

Me.Controls(Screen.ActiveControl.Tag).Value = Screen.ActiveControl.Value

 

[Douglas J. Steele]

Putting the numeric value into the text box should work fine.

I don't see where you'd need to convert from string to integer.

 

[Souris]

I tried following but got type mismatch.

Dim MyValue as string

MyVlaue = CStr(Screen.ActiveControl.Value)
Me.Controls("txt" & Right(Screen.ActiveControl.Name,
Len(Screen.ActiveControl.Name - 3))).Value = MyVlaue

any idea?
Thanks again,

 

[Souris]

Thanks millions,

both work,

Me.Controls("txt" & Mid(Screen.ActiveControl.Name, 4)).Value =
Screen.ActiveControl.Value

Or if you use Dave's suggestion of putting the name of the text box in the
spin buttons Tag property, in which case it would be:

Me.Controls(Screen.ActiveControl.Tag).Value = Screen.ActiveControl.Value

 

[Marshall Barton]

I have name conversion all the spin button have prefix spb and text boxes
have prefix txt and use following code

Me.Controls("txt" & Right(Screen.ActiveControl.Name, Len
(Screen.ActiveControl.Name - 3))).Value = Screen.ActiveControl.Value

I got type mismatch error

The Len(... - 3) needs to be Len(...) - 3

 

[Souris]

Thanks milions for helping and information,

It works fine.
Due to the code is in module, when I clear or reset the spin button value
which triggle the code synchronize code failed because screen.activecontrols
does not support.

Are there any work around?

Your help is great appreciated,