Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2012
    Posts
    6

    Unanswered: Multi Select on List Box

    Hello all, Iím trying to get the multi select function to work for a list box I have on a form that feeds a report.
    Whatís on the form:
    The listbox is named CCList - lists all records from Query CostCenterListing.
    Two text boxes one named startdate and one named enddate.
    A button that previews the report based on the selections. The report name is Invoice Report by Cost Center.

    When I have the multiselect on none and I choose one item from the listbox, the query works and the report is displayed.

    When I set the multiselect to extended, it allows me to select several items, but it doesnít return all of the items selected, actually, it doesnt return any records at all.

    I know code is involved, and Iíve tried looking at all other posts with code, and Iíve tried to modify the code to fit my needs, but since I donít really know much about VBA, I just canít figure out what Iím doing wrong.



    Any help is appreciated!

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    That's because with Multi-Select set to None, the Listbox has a Value Property.With Multi-Select set to anything other than None, it no longer has a Value Property.

    Here's an example of how you 'retrieve' the selections made from a Multi-Select Listbox when it is set to something other than None:
    Code:
    Dim strNames As String
      Dim varItem As Variant
       
      For each varItem in Me.MyListBox.ItemsSelected
        strNames = strNames & Me.MyListBox.Column(X, varItem) & ";"
      Next varItem
       
      MyVariable = strNames
    The above loops thru the Listbox selections, retrieving the Values from the X column of the Listbox. The Columns read from Left-to-Right, in the Listbox, and are Zero-based, so the first column would be 0, the second column would be 1 and so forth.
    It concatenates the Values that have been selected, separating them a semi-colon, then assigns them to a Variable named
    MyVariable.

    Hopefully this will point you in the right direction.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Apr 2012
    Posts
    6
    linq, thank you so much for responding. I'm very new to this and I appreciate your help.

    I took the code you posted and modified it to fit my box.

    Dim strNames As String
    Dim varItem As Variant

    For Each varItem In Me.CCList.ItemsSelected
    strNames = strNames & Me.CCList.Column(CostCenterToBeBilled, varItem) & ";"
    Next varItem

    MyVariable = strNames

    It didnt work. I probably did it wrong. Sorry. Also, I'm using 2007.

  4. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Code:
    For Each varItem In Me.CCList.ItemsSelected
        strNames = strNames & Me.CCList.Column(CostCenterToBeBilled, varItem) & ";"
      Next varItem

    You need to replace the CostCenterToBeBilled in your code with the number of the column that holds the
    CostCenterToBeBilled Field.

    As I said above,
    The Listbox Columns read from Left-to-Right, and are Zero-based, so the first column would be 0, the second column would be 1 and so forth. Figure out which column CostCenterToBeBilled is in then plug that number into your code, in its place.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  5. #5
    Join Date
    Apr 2012
    Posts
    6
    Linq, once again, thank you so much for your help, but I'm still not getting it.
    The column is the first column, so I replaced it with 0.

    Private Sub CCList_BeforeUpdate(Cancel As Integer)
    Dim strNames As String
    Dim varItem As Variant
    For Each varItem In Me.CCList.ItemsSelected
    strNames = strNames & Me.CCList.Column(0, varItem) & ";"
    Next varItem


    End Sub

    But it's still not working. I'm about to give up.

  6. #6
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    First off, this kind of code needs to be in the CCList_AfterUpdate event, not the CCList_BeforeUpdate event.

    After correcting that, what do you expect the code to do, at this point? I see nothing that you've done to even display the selections you've made. All the code I gave you was simply to illustrate how to retrieve the selected values. You have to decide how you want to use those retrieved values, and code accordingly.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  7. #7
    Join Date
    Apr 2012
    Posts
    6
    Thanks again Linq, you've been extremely helpful!!!

    I changed to after update. I have a query that uses the information on the form to return the selected records.

    For example, the query field is costCenterToBeBilled and it has the criteria [Forms]![Invoice Report Form]![CCList]

    So when I choose the items on the listbox, I want them to go into this query and pull the records for which I chose. Make sense?

Posting Permissions

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