Refer to Text Box Indirectly 1

[shannonp]

I'm sure this can be done just don't know how.

On a form I have 4 combo boxes:
[Player1]
[Player2]
[Player3]
[Player4]

And 4 text boxes:
[Player1 Details]
[Player2 Details]
[Player3 Details]
[Player4 Details]

For each combo box I have an Update event which does a Dlookup which sends its result to its corresponding Details text box. For example:

Sub Player1_AfterUpdate()
     [Player1 Details] = Dlookup("[code]","PlayerDetails", "[Player] = " & Me.ActiveControl)
End Sub

OK....so each of my combo boxes has an afterupdate event which works fine. I want to now change my code so that I don't have to explicitly refer to the text boxes ([Player? Details]) and keep the Dlookup line exactly the same for each field event. I was thinking that I could use the Name of the combo box that is currently active and append the word "Details" on the end, which would in hand give me the name of the text box.

So Instead of:

[Player1 Details] = Dlookup("

","PlayerDetails", "[Player] = " & Me.ActiveControl)[/b]

it would now look something like this (I know it doesn't work!):

[b]"[" & Me.Activecontrol.Name & " Details]" = Dlookup("[code]","PlayerDetails", "[Player] = " & Me.ActiveControl)[/b]

I hope I've explained things well enough and thanks to anyone that can help


[yinyang]
Shann

 

[PHV]

Something like this ?
Me.Controls(Me.Activecontrol.Name & " Details") = Dlookup("

","PlayerDetails", "[Player] = " & Me.ActiveControl)


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[shannonp]

GREAT! Thats it exactly...thanks heaps...


Shann

 

[shannonp]

Taking this one step further...am I able to run this from a macro so that I can enter directly into the properties window?


Shann

 

[shannonp]

Hrmm...I think I got it. Am I using the best/easiest method?

Screen.ActiveForm.Controls(Screen.ActiveForm.ActiveControl.Name & " Details") = DLookup("[Code]", "Player Selection", "[Name] = [" & Screen.ActiveForm.ActiveControl.Name & "]")

Thanks again for your help PH.


Shann