Assigning variables from a multi-column Listbox

Greycon · #1 (**permalink**) 04-30-12, 14:43

Hello,

I'm at a loss here.
I have a form with an embedded query that populates a multi-column listbox.
The Listbox is too Simple for Multi Select.
The listbox shows 5 different columns.
Date, Div, Event, Cases, Cube.
I need to be able to take the lines that are selected and pass them to an already written query.

I'm not sure how I'm going to go about this yet, but at the very least I need access to the variables.
The back of my mind is saying that I'm going to dump the results to a temp table, and then use that table to limit the data the query pulls.

The issue I am running into is that any time that I try and pull the results of the field, all I get is the Bound Column column.
I need all columns data, not just 1.

I have tried modifying Microsoft’s example from their help to do this, but either I'm missing something, or this will not do what I want.

Code:

Private Sub testmultiselect_Click()
    Dim oItem As Variant
    Dim intColumn As Variant
    Dim sTemp As String
    Dim iCount As Integer
    
    iCount = 0
            
    If Me!NamesList.ItemsSelected.Count <> 0 Then
        For Each oItem In Me!NamesList.ItemsSelected
            If iCount = 0 Then
                
                sTemp = sTemp & Me!NamesList.ItemData(oItem)

                iCount = iCount + 1
            Else
                sTemp = sTemp & "," & Me!NamesList.ItemData(oItem)
                iCount = iCount + 1
            End If
        Next oItem
    Else
        MsgBox "Nothing was selected from the list", vbInformation
        Exit Sub  'Nothing was selected
    End If
    
    Me!mySelections.Value = sTemp
End Sub

Any help or advice would be appreciated.

Jason

dave0810471 · #2 (**permalink**) 04-30-12, 15:09

For three list boxes named lstfirst, lstsecond and lstthird where lstsecond has two columns the following code demonstrates how to loop through the control and pass the selections into a variable that is then used to modify the SQL of an already existing query...

You need to ensure that you have Microsoft DAO 3.6 Object Library selected as a reference in VBA. To do this Tools -> Macro -> Visual Basic Editor -> Tools -> References.

Code:

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String

Set db = CurrentDb()
Set qdf = db.QueryDefs("yourqueryname")

'This bit of the code demonstrates how to loop through controls for single columns

For Each varItem In Me!lstfirst.ItemsSelected
strCriteria = strCriteria & "," & Me!lstfirst.ItemData(varItem) & ""
Next varItem
For Each varItem In Me!lstsecond.ItemsSelected
strCriteria = strCriteria & "," & Me!lstsecond.ItemData(varItem) & ""
Next varItem
For Each varItem In Me!lstthird.ItemsSelected
strCriteria = strCriteria & "," & Me!lstthird.ItemData(varItem) & ""
Next varItem

'This bit shows for multiple columns in lstsecond
For Each varItem In Me.lstsecond.ItemsSelected
strCriteria = strCriteria & "," & Me.lstsecond.Column(0) & "," & Me.lstsecond.Column(1) & ""
Next varItem

If Len(strCriteria) = 0 Then
MsgBox "You did not select anything." _
, vbExclamation, "Nothing to find!"

Exit Sub
End If

strCriteria = Right(strCriteria, Len(strCriteria) - 1)

strSQL = "SELECT* FROM tblname " & _
"WHERE tblname .fieldname IN(" & strCriteria & ");"
qdf.SQL = strSQL

DoCmd.OpenQuery "yourqueryname"
Set db = Nothing
Set qdf = Nothing
End Sub

Greycon · #3 (**permalink**) 04-30-12, 15:55

Dave,

Thank you for the quick reply.
So, trying to think this through I have modified the code to what would seem to work.

Not quite sure what it is doing now since the looping appears basically the same.

When I select multiple rows, the mseeage boxes always shows the same results.
It does not appear to be going to the next oItem.
Interesting thing is that it selects the last record in the listbox to show.

Thank you,
Jason

Code:

Private Sub testmultiselect_Click()
    Dim oItem As Variant
    Dim iCount As Integer
    Dim Rls_date As Date
    Dim Div As Long
    Dim Event_code As String
    Dim Cases As Long
    Dim Cube As Long
    
    iCount = 0
            
    If Me!NamesList.ItemsSelected.Count <> 0 Then
    
        For Each oItem In Me!NamesList.ItemsSelected
                
                Rls_date = Me.NamesList.Column(0)
                Div = Me.NamesList.Column(1)
                Event_code = Me.NamesList.Column(2)
                Cases = Me.NamesList.Column(3)
                Cube = Me.NamesList.Column(4)

    MsgBox (Rls_date & ", " & Div & ", " & Event_code & ", " & Cases & ", " & Cube)
        
        Next oItem
    Else
        MsgBox "Nothing was selected from the list", vbInformation
        Exit Sub  'Nothing was selected
    End If

End Sub

Greycon · #4 (**permalink**) 04-30-12, 16:20

Dave,

Stumbled across this thing called a FAQ.
What a novel concept, I think I will start looking for these kinda things.
With your post and the info in there, I believe I have come to the solution.

I'm kinda new to VBA so this may seem basic to others, but I'm a little perplexed.

Rls_date = NamesList.Column(0, oItem) Works

Rls_date = Me.NamesList.Column(0) Does not work

Seems to me that they would be the same.
But I'm sure the Devil is in the Syntax.

If you could explain the difference that would be great, otherwise, this is working and Thank you for helping me in the right direction!

Thanks again,
Jason

Code:

Private Sub testmultiselect_Click()
    Dim oItem As Variant
    Dim iCount As Integer
    Dim Rls_date As Date
    Dim Div As Long
    Dim Event_code As String
    Dim Cases As Long
    Dim Cube As Long
    
    iCount = 0
            
    If Me!NamesList.ItemsSelected.Count <> 0 Then
    
        For Each oItem In Me!NamesList.ItemsSelected
        
        Rls_date = NamesList.Column(0, oItem)
        Div = NamesList.Column(1, oItem)
        Event_code = NamesList.Column(2, oItem)
        Cases = NamesList.Column(3, oItem)
        Cube = NamesList.Column(4, oItem)

    MsgBox (Rls_date & ", " & Div & ", " & Event_code & ", " & Cases & ", " & Cube)
        
        Next oItem
    Else
        MsgBox "Nothing was selected from the list", vbInformation
        Exit Sub  'Nothing was selected
    End If

End Sub

dave0810471 · #5 (**permalink**) 05-01-12, 14:00

The listbox.column property is as follows: expression .Column(Index, Row) with index being a long integer that can range from 0 to the setting of the ColumnCount property minus one and row being an integer that can range from 0 to the setting of the ListCount property minus 1.

Glad you have got your code to work, in VBA there is more often than not multiple ways to achieve the same thing. I've attached the example file I was playing around with just in case its of interest.

Internet Services

Web Development

Digital Marketing

Consumer Tech