Can't access data from a unbound list box

[EAB1977]

Hello everyone,

I am having a hard time displaying information from a unbound list box.
To get a little bit of background information, I have a list box that
is populated with plants. Whatever plant I click on, a query runs (via
code) to populate the second list box. The second list box contains
email addresses of contact people for that specific plant.

My trouble is that I cannot display the information when a contact
person is clicked. Below is my code on the click event of the second
list box:

Private Sub lstEmail_Click()

Dim frm As Form
Dim ctlSource As Control
Dim intCurrentRow As Integer

Set frm = Forms!frmPlant
Set ctlSource = frm!lstEmail

For intCurrentRow = 0 To ctlSource.ListCount - 1
If ctlSource.Selected(intCurrentRow) Then
Me.txtName.Value = Forms!frmPlant!lstEmail.Column(0)
Me.chkQcManager.Value = Forms!frmPlant!lstEmail.Column(2)
Exit For
End If
Next intCurrentRow

frm.Refresh

Set ctlSource = Nothing
Set frm = Nothing
End Sub

 

[Marshall Barton]

I am having a hard time displaying information from a unbound list box.
To get a little bit of background information, I have a list box that
is populated with plants. Whatever plant I click on, a query runs (via
code) to populate the second list box. The second list box contains
email addresses of contact people for that specific plant.

My trouble is that I cannot display the information when a contact
person is clicked. Below is my code on the click event of the second
list box:

Private Sub lstEmail_Click()

Dim frm As Form
Dim ctlSource As Control
Dim intCurrentRow As Integer

Set frm = Forms!frmPlant
Set ctlSource = frm!lstEmail

For intCurrentRow = 0 To ctlSource.ListCount - 1
If ctlSource.Selected(intCurrentRow) Then
Me.txtName.Value = Forms!frmPlant!lstEmail.Column(0)
Me.chkQcManager.Value = Forms!frmPlant!lstEmail.Column(2)
Exit For
End If
Next intCurrentRow

frm.Refresh

Set ctlSource = Nothing
Set frm = Nothing
End Sub

For what you posted, the Column property needs the row
specified, e.g. Column(0, intCurrentRow). But, that doesn't
make much sense because you are only trying to display a
single email. It might make some sense if the list box was
multiselect, but then the rest of the code would be suspect.

For a single select list box, the list box's selected row is
the default row for the Column property. In this case,
there is no need for the loop:

Private Sub lstEmail_Click()
Me.txtName = Me!lstEmail.Column(0)
Me.chkQcManager = Me!lstEmail.Column(2)
End Sub

I have no idea what you expect the frm.Refresh line to
accomplish and I strongly suspect that it will not do
whatever you thought you needed.