Setting text box using index expression

[Bill]

I have a textbox "tbSession1" (Actually I have several)
that when double-clicked will cause a related combo
box to become Visable and dropped. I can obviously
capture the name of the textbox in it's Double-click event,
but as the combo box gets the focus when an item is
chosen from the list, how do I code the assignment
statement as I set the text box with whatever string
was chosen from the combo box? Also, in the code
below I've not coded the capturing of the text box
control name correctly?

Some experimental code:

Option Compare Database
Option Explicit
Dim tbcntl As Control

Private Sub cmboNameList_Click()
Me(tbcntl) = Me.cmboNameList.Column(1)
Me.cmboNameList.Visible = False
End Sub

Private Sub tbSession1_DblClick(Cancel As Integer)
tbcntl = tbSession1 <<<<<<<<<FAILS
Me.cmboNameList.Visible = True
Me.cmboNameList.Dropdown
End Sub

 

[Bill]

I was missing the "Set" on the offending statement
below. Now my failing statement is:
Me(tbcntl) = Me.cmboNameList.Column(1)
The error is type mismatch. Both source and
target are text, so I'm not seeing the problem.
Bill

 

[Dirk Goldgar]

I have a textbox "tbSession1" (Actually I have several)
that when double-clicked will cause a related combo
box to become Visable and dropped. I can obviously
capture the name of the textbox in it's Double-click event,
but as the combo box gets the focus when an item is
chosen from the list, how do I code the assignment
statement as I set the text box with whatever string
was chosen from the combo box? Also, in the code
below I've not coded the capturing of the text box
control name correctly?

Some experimental code:

Option Compare Database
Option Explicit
Dim tbcntl As Control

Private Sub cmboNameList_Click()
Me(tbcntl) = Me.cmboNameList.Column(1)
Me.cmboNameList.Visible = False
End Sub

Private Sub tbSession1_DblClick(Cancel As Integer)
tbcntl = tbSession1 <<<<<<<<<FAILS
Me.cmboNameList.Visible = True
Me.cmboNameList.Dropdown
End Sub

Try this revision to your code, noting that I'm creating a function for
you to use in the event property of several different text boxes. More
on that below.

'----- start of revised code -----
Option Compare Database
Option Explicit

Dim tbcntl As Control

Function GetFromCombo()

Set tbcntl = Me.ActiveControl
Me.cmboNameList.Visible = True
Me.cmboNameList.Dropdown

End Function

Private Sub cmboNameList_Click()

If Not tbcntl Is Nothing Then
tbcntl.Value = Me.cmboNameList.Column(1)
Me.cmboNameList.Visible = False
End If

End Sub

'----- end of revised code -----

NOTE: I'm trusting you, that what you want to assign to the text box is
the value in the *second* column of the combo box. That's what
..Column(1) gets you, since the Column property is zero-based.

Now, just creating that function won't get it called. What you do is,
you set the OnDblClick event property of each text box you want to
behave this way to

=GetFromCombo()

Note that you won't create an event procedure for these text boxes; you
just set the OnDblClick property to the function expression. That ought
to do it.

 

[Bill]

Okay, two things: One is that I'd forgotten that the
columns were zero based, so that was the cause of
my problem. Second, I like your function approach
much better, lest I replicate a bunch of code for each
of the text boxes.

I'll post back in a few minutes with the results of
integrating your code approach.

Bill

 

[Dirk Goldgar]

Okay, two things: One is that I'd forgotten that the
columns were zero based, so that was the cause of
my problem.

Part of it, maybe. But ...

.... is still wrong. You've set tbcntl to be an object reference to the
text box, but "Me(tbcntl)" expects tbcntl to be either a string value or
a number value, to be used as an index into the form's Controls
collection. So what that would do is evaluate the text box and use its
value as an index to a particular control. If I understand you, that's
not what you had in mind. You just want to set the text box,
represented by the object variable tbcntl, to the string you extract
from the combo box.

I'll post back in a few minutes with the results of
integrating your code approach.

Do that, please.

 

[Bill]

Other than needing to add the SetFocus on a control
before referencing it, your code worked perfectly.
By the way, I also learned that one DOES NOT use a
control variable as an index, i.e., Me(tbCntl). That's
what all the type mismatch was all about.

Here's what I ended up with:
Function GetFromCombo()

Set tbcntl = Me.ActiveControl
Me.cmboNameList.Visible = True
Me.cmboNameList.SetFocus
Me.cmboNameList.Dropdown

End Function

Private Sub cmboNameList_Click()

If Not tbcntl Is Nothing Then
tbcntl.SetFocus <<<<<<<<<<<<<<<needed this
tbcntl.Value = Me.cmboNameList.Column(0)
Me.cmboNameList.Visible = False
End If

End Sub

 

[Dirk Goldgar]

Other than needing to add the SetFocus on a control
before referencing it, your code worked perfectly.
By the way, I also learned that one DOES NOT use a
control variable as an index, i.e., Me(tbCntl). That's
what all the type mismatch was all about.

Here's what I ended up with:
Function GetFromCombo()

Set tbcntl = Me.ActiveControl
Me.cmboNameList.Visible = True
Me.cmboNameList.SetFocus
Me.cmboNameList.Dropdown

End Function

Private Sub cmboNameList_Click()

If Not tbcntl Is Nothing Then
tbcntl.SetFocus <<<<<<<<<<<<<<<needed this
tbcntl.Value = Me.cmboNameList.Column(0)
Me.cmboNameList.Visible = False
End If

End Sub

I think the reason you needed this:

tbcntl.SetFocus <<<<<<<<<<<<<<<needed this

is not really that you had to set the focus to the text box, but rather
that you had to set the focus *away* from cmboNameList. I didn't think
of that before, but you can't hide a control that has the focus, and you
had previously set the focus to the combo so that you could call its
Dropdown method. You could have set the focus someplace else than the
text box if you had wanted, so long as you set it away from the combo
box you wanted to hide.

Most control properties and methods, including the Value property, can
be accessed even when the control doesn't have the focus. Some of them,
however, including the Dropdown method and -- notoriously -- the Text
property, require that the control have the focus.

 

[Bill]

I think I'm all set, thank you!! I think I understand
your points about the focus issues.

Here's the final code segment:

Option Compare Database
Option Explicit
Dim tbcntl As Control

Private Sub Form_Open(Cancel As Integer)
Me.ctlYear = Year(Date) ' Assume it's current year
End Sub

Function GetFromCombo()
'================================================================================'
' This function captures the current text-box control in the control
variable
' tbcntl. It is called (invoked) from the double-click event of every text
box
' that needs to select from a combo-box of names.
'================================================================================'

Set tbcntl = Me.ActiveControl ' Save current control
Me.cmboNameList.Visible = True ' Okay, show the dropdown list
Me.cmboNameList. ' Can't drop it unless it
has the focus
Me.cmboNameList.Dropdown ' Open the list

End Function

Private Sub cmboNameList_Click()
Dim strWrk() As String
'================================================================================'
' Combo box is dropped and the user has made a choice. Make sure we have a
valid
' target text box "set" for use and capture the selection. Name list is of
the
' form "lastname, firstname". Text box is to receive an informal version of
' "firstname lastname".
'================================================================================'

If Not tbcntl Is Nothing Then '
Control set?
tbcntl.SetFocus
' Need text box to have the focus
strWrk = Split(Me.cmboNameList.Column(0), ",") ' Okay, capture the
choice
tbcntl.Value = strWrk(1) & " " & strWrk(0) ' Format
user's choice
Me.cmboNameList.Visible = False ' Hide the
combo until it's needed again
End If

End Sub

 

[Dirk Goldgar]

I think I'm all set, thank you!! I think I understand
your points about the focus issues.

Very good.