cannot show record from listbox

[uoghk]

I create a textbox "PhoneSearch", a listbox "PhoneList", and 3 labels "Name", "Phone" and "Address".


when enter a phone no. in PhoneSearch, then show the result in PhoneList.
if only one record is found, show the corresponding values into the 3 labels.
but in debug mode, one record is found, it call PhoneList_Click,
but Me.PhoneList.Column(0), Column(1), Column(2) all are null values.
what wrong in my coding?

Private Sub PhoneSearch_LostFocus()
    Me.PhoneList.RowSource = "SELECT Name,Phone,Address FROM Contacts WHERE Phone LIKE '*" & Me.PhoneSearch & "*'"
    If Nz(DCount("Phone", "Contacts", "Phone LIKE '*" & Me.PhoneSearch & "*'"), 0) = 1 Then
        Me.PhoneList.Requery
        PhoneList_Click
    End If
End Sub
Private Sub PhoneList_Click()
    Me.Name.Caption = Me.PhoneList.Column(0)
    Me.Phone.Caption = Me.PhoneList.Column(1)
    Me.Address.Caption = Me.PhoneList.Column(2)

End Sub

[June7]

Instead of VBA setting label caption, could have expression in textbox referencing listbox columns. But why do you need to show this data in labels or textboxes since the listbox already displays this info?

Did you step debug?

Code looks fine so hard to say why no data shows without knowing your db. You could provide it for analysis. Follow instructions at bottom of my post.

[uoghk]

Of course, I step the debug.
Actually, the listbox rowsource retrieves other data from the Contacts table.
The listbox just show the first 3 data, the rest will be shown on the labels if click on the listbox.

[June7]

I am confused. SQL shows only 3 fields. How could 'other' data show?