Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2012
    Posts
    3

    Unanswered: Assigning variables from a multi-column Listbox

    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

  2. #2
    Join Date
    Jan 2012
    Posts
    97
    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
    Hope this helps,

    Dave.

    _____________

    Access 2000 User

  3. #3
    Join Date
    Apr 2012
    Posts
    3
    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

  4. #4
    Join Date
    Apr 2012
    Posts
    3
    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

  5. #5
    Join Date
    Jan 2012
    Posts
    97
    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.
    Attached Files Attached Files
    Hope this helps,

    Dave.

    _____________

    Access 2000 User

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •