Retreiving value from List Box 2

[jamez05]

Hi, I have a query that populates a list box:

    strSQL = "SELECT [tblSite-Type].[User Entered], [tblSite-Type].[Site ID], [tblSite-Type].[Superfund Site] "
    strSQL = strSQL & "FROM [tblSite-Type] "
    strSQL = strSQL & "ORDER BY [tblSite-Type].[Site ID], [tblSite-Type].[Superfund Site];"
    Me.listSitesEdit.ColumnCount = 3
    Me.listSitesEdit.ColumnHeads = True
    Me.listSitesEdit.RowSourceType = "table/query"
    Me.listSitesEdit.RowSource = strSQL

I'm trying to populate a text box with the value from the second column of the listSitesEdit listbox (which is [tblSite-type].[Site ID]. For some reason, It populates it with the first column value. Here's the code I have for trying to retrieve the value:

    If Me.listSitesEdit.ListCount > 0 Then
        For i = 0 To Me.listSitesEdit.ListCount - 1
            If i = Me.listSitesEdit.Selected(i) Then
             Me.txtSiteID.Value = Me.listSitesEdit.Column(i, 2)
            End If
        Next i
            
    End If

Not sure what I'm doing wrong

 

[randy700]

If I'm not mistaken, the first column is the bound column by default.
You need to include [blue]Me.listSitesEdit.BoundColumn = 2[/blue].

Make the control source of the text box [blue]=listSitesEdit[/blue].


Randy

 

[PWise]

try
Me.txtSiteID.Value = Me.listSitesEdit.Column(1, i)

 

[PHV]

Replace this:
If i = Me.listSitesEdit.Selected(i) Then
With this:
If Me.listSitesEdit.Selected(i) Then

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jamez05]

Thanks everyone, getting closer. I've got it to loop through all the values, but the if statement is still not working (doesn't fire the msgbox):

    If Me.listSitesEdit.ListCount > 0 Then
        For i = 0 To Me.listSitesEdit.ListCount - 1
           If Me.listSitesEdit.Selected(i) Then
            MsgBox Me.listSitesEdit.Column(1, i)
           End If
            'MsgBox Me.listSitesEdit.Column(1, i)
           Next i
    End If

 

[jamez05]

Got it -
if me.listSites.ListIndex = i

 

[PHV]

If the ListBox is a MultiSelect one, you may have to play with the ItemsSelected collection.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886