Using second column from multiselect listbox in VBA 1

[MrMode]

I am trying to use the second column from a multiselect list box, when I use this code for a different command button on the same form looking at the first column it works fine.

 'add selected values to string
    Set ctl = Me.List2
    For Each varItem In ctl.ItemsSelected
        strWhere = strWhere & ctl.ItemData(varItem) & ","

But when I try and use the second column I am getting this error:

"Runtime error 424 object required"

Below is the code that is throwing up the error. I am guessing that I have not properly set up the Set ctl line.

Private Sub Command8_Click()

    Dim dbs As DAO.Database
    Dim rsSQL As DAO.Recordset
    Dim strSql As String
    Dim strWhere As String
    Dim ctl As Control
    Dim varItem As Variant
    
    Set dbs = CurrentDb
   
   
    'make sure a selection has been made
    If Me.List2.ItemsSelected.Count = 0 Then
    MsgBox "Must select at least 1 company"
    Exit Sub
    End If

    'add selected values to string
    Set ctl = Me.List2.Column(1, Me.List2.ItemsSelected(0))
    For Each varItem In ctl.ItemsSelected
        strWhere = strWhere & ctl.ItemData(varItem) & ","
    'Use this line if your value is text
    'strWhere = strWhere & chr34 & ctl.ItemData(varItem) & chr34 & ","
    Next varItem
      
    'trim trailing comma
    strWhere = Left(strWhere, Len(strWhere) - 1)
    
    MsgBox "These are the harvested IDs " & strWhere
    
End Sub

 

[PHV]

Why not simply this ?

For Each varItem In Me!List2.ItemsSelected
    strWhere = strWhere & Me!List2.Column(1, varItem) & ","
Next varItem

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

 

[MrMode]

Works perfectly - I got lost in the wool there.

Thanks!