Results 1 to 11 of 11
  1. #1
    Join Date
    Dec 2007
    Posts
    49

    Select Multiple value from Listbox in MS Access

    Hi,
    My Project is in MS Access.
    In that I have one form in which I have some textboxes,comboboxes and listboxes.
    Now when I select value from 1st combobox(CustomerID) then it wil generate list for 1st listbox(PalletNo).
    Now I want to select miltiple values from that 1st listbox(PalletNo).
    and based upon this selection from 1st listbox(PalletNo) and combobox(CustomerID) I want to generate list for 2nd listbox(PONo).

    For example

    CustomerID PalletNo PONo
    1000 28300 12345
    1001 28301 12346
    1000 28302 12345
    1000 28303 12345
    1002 28304 12347
    1003 28305 12348
    1000 28306 12350
    1000 28307 12350

    So when I select 1000 from 1st Combobox(CustomerID) then it will generate list for 1st Listbox(PalletNo).
    like this
    CustomerID PalletNo
    1000 28300
    28302
    28303
    28306
    28307


    Now when I select multiple values from that Listbox(PalletNo) then it will generate list for 2nd listbox(PONo)
    like
    CustomerID PalletNo PONo
    1000 28300 12345
    28302 12345
    28307 12350


    I don't know how to do this.
    ->how to select multiple values from listbox?
    ->how can I generate list if I select multiple values from listbox?
    Can anyone help me?
    Thanks in Advance.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,065
    To access multiple selected values you must loop through the contents of the listbox
    Code:
    For i = 0 To Me.myListBox.ListCount - 1
        ...
    Next i
    And check to see if the value is selected
    Code:
    If Me.myListBox.Selected(i) Then
        ...
    End If
    If selected, append the value to an array or delimited string variable
    Code:
    myString = myString & Me.myListBox.Column(boundColumnZeroBasedIndex, i) & ","
    Any good to ya?
    George
    Home | Blog

  3. #3
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    To be able to select multiple items in your listbox, there is a setting called Multi Select in the Other tab of the properties. Change it to Simple or Extended.

    Also, I would recommend using the ItemsSelected collection instead of the way that georgev has proposed. With ItemsSelected you only loop through the items that the user selected. The other way you loop through all the items in the listbox. Here is an example:

    Code:
        Dim varReport As Variant
        
        For Each varReport in Me.lstReports.ItemsSelected
           .
           .
           .
        Next varReport
    Do you know how to program with VBA? The easiest way to get the data for the second listbox is to create a SQL string with all the PO numbers.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,065
    Ha, in all my experience I've never come across the ItemsSelected!

    Oops
    George
    Home | Blog

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Ahhh you ARE human after all
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,065
    Who told you that
    George
    Home | Blog

  7. #7
    Join Date
    Dec 2007
    Posts
    49
    I am using this code .i don't get it what to do?

    Private Sub PalletIDOrTrackingNo_AfterUpdate()
    Dim i As Variant
    Dim myString As String

    For i = 0 To Me.PalletIDOrTrackingNo.ListCount - 1
    If Me.PalletIDOrTrackingNo.Selected(i) Then
    myString = myString & PalletIDOrTrackingNo.Column(1, i) & ","
    Me.LONO = (DLookup("LONo", "PRODUCTION", "((CustomerCode=[Forms]![ShippingAlerts]![CustomerID_Combo]) and (PONo=[Forms]![ShippingAlerts]![PONo])and (PalletNo=myString))"))
    Else
    Me.LONO = 1
    End If
    Next i

    End Sub

    can u give me simple code taking any example, in which u select multiple items from listbox and generate list for another listbox.

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,065
    This will result in a string like this
    Code:
    myString = myString & PalletIDOrTrackingNo.Column(1, i) & ","
    1, 2, 3, 4, 5, ...<snip>... 99,
    And your DLookup is then going
    Code:
    PalletNo=myString
    And I imagine you don't have a PalletNo with the value "1, 2, 34, ..."

    Try this...
    Create a second listbox
    Code:
    Me.mySecondListBox.RowSourceType = "Value List"
    Me.mySecondListBox.RowSource = ""
    
    For i = 0 To Me.PalletIDOrTrackingNo.ListCount - 1
        If Me.PalletIDOrTrackingNo.Selected(i) Then
            Me.mySecondListBox.AddItem PalletIDOrTrackingNo.Column(1, i)
        End If
    Next i
    George
    Home | Blog

  9. #9
    Join Date
    Dec 2007
    Posts
    49
    Thanks buddy for ur help But I got my solution.
    Last edited by billy_pit; 01-17-08 at 15:33.

  10. #10
    Join Date
    Oct 2003
    Posts
    103
    So, to take it the next step, assume we have an intersection table that we'll modify using the multi_select.

    We have an items in the list in our intersection table. We want to "select" those items until the user modifies the list. How would we do that?

    Sorry if this looks funny, I am sending from blackberry.
    Last edited by jdostie; 12-07-08 at 19:01.

  11. #11
    Join Date
    Oct 2003
    Posts
    103

    Clarification

    Back to what I posted yesterday, I have the following (please excuse some of the names, I was rushing through this, and I'll need to fix the names, but: Below is code from a form that I'll use to set up user permissions. I know there are different thoughts on that subject, but for now, this is just about multi-select.

    The idea is to load all the forms (I will probably add reports and queries) to the list as well as items from a table that define some other permissions like (can edit certain fields, can setup permissions, can delete records from table x, whatever). That's not important because there are similar applications for other intersection tables, so let's not get caught up on the question of workgroup security files please.

    For any intersection table, we'd want a way to have a multi-select box populated with all of the possibilites. Hopefully, preselect the values currently in the intersection table, and upon update, remove any selections that were not in the itemsselected collection and add any that were missing from the table.
    __________________________________________________ ___________________
    Option Compare Database

    Private Sub Command6_Click()
    Dim ctl As Control
    Set ctl = PermissionList
    Dim varItm As Variant

    'this is where I would like to next loops to compare everything in dbo_tblPermissionItems
    'with everything in Permission items, deleting from dbo_tblPermissionItems if not selected
    'and adding to dbo_tblPermissionItems if selected and not already in the table
    For Each varItm In ctl.ItemsSelected
    MsgBox ctl.ItemData(varItm)
    Next varItm
    End Sub
    __________________________________________________ _________________________

    Private Sub Form_Close()
    Me.text2.RowSource = ""
    Me.PermissionList.RowSource = ""
    End Sub
    __________________________________________________ __________________________
    Private Sub Form_Load()
    Dim rs As New ADODB.Recordset

    Dim query As String
    Dim permlist As String

    query = "select permissionname from dbo_tblPermissionItems"
    rs.Open query, CurrentProject.Connection, adOpenStatic
    permlist = ""

    While Not rs.EOF
    If Len(permlist) > 0 Then permlist = permlist & ","
    permlist = permlist & rs.Fields!PermissionName
    Wend

    Dim obj As AccessObject, dbs As Object
    Set dbs = Application.CurrentProject
    ' Search for open AccessObject objects in AllForms collection.
    For Each obj In dbs.AllForms

    If Len(permlist) > 0 Then permlist = permlist & ","
    permlist = permlist & obj.Name
    Next obj

    Me.PermissionList.RowSource = permlist
    'this is where I would like to update me.permissionlist to select everything in the table
    'it would make it easier to update later, and also would let the user know what the permissions currently are
    'set for
    Me.text2.RowSource = "Select dbo_tblUserAccounts.UserID, encrypt([login]) as expr1 from dbo_tblUserAccounts;"
    End Sub
    __________________________________________________ ____________________________


    I am guessing I am about half way there with this, I can certainly accomplish what I want simply by deleting all references in the table and adding new records corresponding to the new list, or via some more complicated method. But, I have to believe this has already been hashed out and optimized a million times over. What is the best approach you have seen?

Posting Permissions

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