Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Feb 2004
    Location
    UK
    Posts
    246

    Unanswered: Select all in a list box

    Hi
    Can anyone tell me what code I would need to be able to select all items in a listbox. I want this to run from a command button

    Regards
    John

  2. #2
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    PHP Code:
    Dim i as byte

      
    for 0 to yourList.ListCount
        yourList
    .Selected(i)= True
      Next 
    Hope that what you need

  3. #3
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    You have to adjust the MultiSelect to accept more than one selection.
    i.e
    yourListBox.List_Info.MultiSelect=1

    OR

    yourListBox.List_Info.MultiSelect=2

  4. #4
    Join Date
    Feb 2004
    Location
    UK
    Posts
    246
    Thanks Hammy for that, unfortunately I may need your help a bit further as I thought it may be simpler than that (if its possible to be)

    I'm trying to adapt a bit of code that I had where it went through a list box to pull off the selected items and put them in a query. Unfortunately that didn't cater for the user wanting to see the list box in different orders. So one of the guys on this forum came up with the following bit of code which allowed the user to order the list box in the way they wanted and the selected items were then passed to another listbox.

    Its these selected items that I want to pass to the query, so how do I incorparate the bit of code you kindly gave me to that below, or am I going about this the wrong way?


    Regards
    John


    On Error GoTo Err_procede_Click
    Dim MyDB As DAO.Database
    Dim qdef As DAO.QueryDef
    Dim i As Integer
    Dim strSQL As String
    Dim strWhere As String
    Dim strIN As String
    Dim flgSelectAll As Boolean
    Dim varitem As Variant

    Set MyDB = CurrentDb()


    strSQL = "SELECT * FROM Securities"

    'Build the IN string by looping through the listbox
    For i = 0 To SelectedLORS.ListCount - 1
    If SelectedLORS.Selected(i) Then
    strIN = strIN & "'" & SelectedLORS.Column(0, i) & "',"
    End If
    Next i

    'Create the WHERE string, and strip off the last comma of the IN string
    strWhere = " WHERE [LORSCODE] in (" & left(strIN, Len(strIN) - 1) & ")"

    'If "All" was selected in the listbox, don't add the WHERE condition
    If Not flgSelectAll Then
    strSQL = strSQL & strWhere
    End If

    MyDB.QueryDefs.Delete "Select_security_qry"
    Set qdef = MyDB.CreateQueryDef("Select_security_qry", strSQL)

    'Open the query, built using the IN clause to set the criteria
    DoCmd.OpenQuery "Select_security_qry", acViewNormal

    'Clear listbox selection after running query
    For Each varitem In Me.SelectedLORS.ItemsSelected
    Me.SelectedLORS.Selected(varitem) = False
    Next varitem


    Exit_procede_Click:
    Exit Sub

    Err_procede_Click:

    If Err.Number = 5 Then
    MsgBox "You must make a selection(s) from the list", , "Selection Required !"
    Resume Exit_procede_Click
    Else
    'Write out the error and exit the sub
    MsgBox Err.Description
    Resume Exit_procede_Click
    End If

  5. #5
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1

    Post

    To select all the items in the list box :

    On Error GoTo Err_procede_Click
    Dim MyDB As DAO.Database
    Dim qdef As DAO.QueryDef
    Dim i As Integer
    Dim strSQL As String
    Dim strWhere As String
    Dim strIN As String
    Dim flgSelectAll As Boolean
    Dim varitem As Variant

    Set MyDB = CurrentDb()


    strSQL = "SELECT * FROM Securities"

    'Build the IN string by looping through the listbox

    SelectedLORS.MultiSelect=2
    For i = 0 To SelectedLORS.ListCount - 1
    SelectedLORS.Selected(i) = True
    Next i


    For i = 0 To SelectedLORS.ListCount - 1
    If SelectedLORS.Selected(i) Then
    strIN = strIN & "'" & SelectedLORS.Column(0, i) & "',"
    End If
    Next i

    'Create the WHERE string, and strip off the last comma of the IN string
    strWhere = " WHERE [LORSCODE] in (" & left(strIN, Len(strIN) - 1) & ")"

    The Rest of the code still the same

  6. #6
    Join Date
    Feb 2004
    Location
    UK
    Posts
    246
    Thanks for that Hammy

    Unfortunately I'm getting an error "You can't assign a value to this object"??????


    Also can I ask what the part of the code
    SelectedLORS.MultiSelect=2
    Means?

    Regards
    John

  7. #7
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    The SelectedLORS.MultiSelect=2 allows the ListBox to be able to select more than item by clicking the Ctr key and the Item in the listBox.

    While

    SelectedLORS.MultiSelect=1 allows the ListBox to be able to select more than item by clicking the Item in the listBox, another click on the selected item, will disselect it.

  8. #8
    Join Date
    Feb 2004
    Location
    UK
    Posts
    246
    Hi Hammy,
    So the error is coming from this bit of code then

    SelectedLORS.MultiSelect = 2
    For i = 0 To SelectedLORS.ListCount - 1
    SelectedLORS.Selected(i) = True
    Next i

    Which I assume is selecting the whole of the listbox because when I take it out it informs me that I must make a selection. If I select manually the rest of teh code runs OK.

    Regards
    John

  9. #9
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    Since Varitem is alread defiend as variant then try this:

    For Each varitem In Me.SelectedLORS.ItemsSelected
    Me.SelectedLORS.Selected(varitem) = True
    Next varitem

  10. #10
    Join Date
    Feb 2004
    Location
    UK
    Posts
    246
    That seems to get rid of the error but it now only selects one item form the list

  11. #11
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    Then you have to put this:

    SelectedLORS.MultiSelect = 2

    OR

    SelectedLORS.MultiSelect = 1

  12. #12
    Join Date
    Feb 2004
    Location
    UK
    Posts
    246
    Hammy sorry to be a pain, BUT where would I have this in the code? I tried putting it prior to the "For each varitem.................." but then I get the error message back grrrrrrrrh

    Regards
    John

  13. #13
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    Ok, then go in the property Window of the ListBox and adjust the property MultiSelect to the theired entery. I don't remember the entery in english, I use german Acce$$

  14. #14
    Join Date
    Feb 2004
    Location
    UK
    Posts
    246
    Hi Hammy,
    Thanks for that, I take it you mean the 3rd item on the MultiSelect "Extended". It is already on the that selection.

    If I take the "SelectedLORS.MultiSelect = 2" out of the code and manually select the list box all is fine. But with the "SelectedLORS.MultiSelect = 2" in I get that error of "You can't assign a value to this object"

    I suppose it would help if I knew what I was doing (which I don't )

    Regards
    John

  15. #15
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    Please have a look on the MultiSelect property Help .


    Good Luck

Posting Permissions

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