Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665

    Unanswered: List box For Multi Selection

    Im using a list box so that I can have multi selection on one field in my table. I need to know what that selection looks like so that I can write it correctly into a query, any ideas on how to find out what a list box outputs?
    Jim

  2. #2
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    I am making some progress with the following code

    Dim ctlSource As Control
    Dim strItems As String
    Dim intCurrentRow As Integer
    Set ctlSource = Customer_List
    For intCurrentRow = 0 To ctlSource.ListCount - 1

    If ctlSource.Selected(intCurrentRow) And ListIndex = 0 Then
    strItems = strItems & "'" & ctlSource.Column(0, _
    intCurrentRow) & "', "
    End If
    Next intCurrentRow

    Test_List_Box.Value = strItems


    my problem now is that I cant diferentiate the last selection so that I can tell it not to put the comma at the end of the statment. I cant have the comma because this is going into a where clause in statment and the last comma will throw off the sql statment. Any suggestions?
    Jim

  3. #3
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665

    understanding .listcount

    Ok new twist, Im getting closer but i still need some help and it may be in the understanding of a few functions. Code is as follows

    Dim ctlSource As Control
    Dim strItems As String
    Dim intCurrentRow As Integer
    Set ctlSource = Customer_List

    For intCurrentRow = 0 To ctlSource.ListCount - 1

    If ctlSource.Selected(intCurrentRow) Then
    strItems = strItems & "'" & ctlSource.Column(0, _
    intCurrentRow) & "' "
    Exit For
    End If

    Next intCurrentRow
    For intCurrentRow = 1 To ctlSource.ListCount - 1
    If ctlSource.Selected(intCurrentRow) Then
    strItems = strItems & ", '" & ctlSource.Column(0, _
    intCurrentRow) & "' "
    End If

    Next intCurrentRow

    Test_List_Box.Value = strItems


    This is almost doing what I need except for the fact that the first item selected is in the list twice. and Im not sure I completely understand .listcount if i had a better grasp of that command id think id be able to figure this out. Any help would be grand

  4. #4
    Join Date
    Nov 2002
    Location
    San Francisco
    Posts
    251
    this is my way..... even with 15 listboxes it is super easy to read....

    1. I have simple function, which returns the list (integer, string or date)

    2. Listbox has usually first ITEM with id -1 and description <All>, if user selects <All> nothing is filtered (WHERE seq. is not created), I have also special other values like <none>

    'this is on the form with multilistbox
    tempstrSQL3 = FillFromList(Me.lstCounty, vbInteger)
    If Not IsEmptyString(tempstrSQL3) Then
    tempstrSQL3 = " AND [aCounty#] In ( " & tempstrSQL3 & ")"
    End If



    Public Function FillFromList(ctl As Control, Devider As DataType) As String
    Dim varItm As Variant
    Dim myList As String

    myList = ""
    If ctl.ItemsSelected.Count > 0 Then
    For Each varItm In ctl.ItemsSelected
    If Trim(ctl.ItemData(varItm)) = "<All>" Or Trim(ctl.ItemData(varItm)) = "-1" Then
    FillFromList = ""
    Exit Function
    End If
    If Trim(ctl.ItemData(varItm)) = "<None>" Or Trim(ctl.ItemData(varItm)) = "-2" Then
    FillFromList = "<None>"
    Exit Function
    End If
    If Trim(ctl.ItemData(varItm)) = "<Blank>" Or Trim(ctl.ItemData(varItm)) = "-3" Then
    FillFromList = "<Blank>"
    Exit Function
    End If

    If Devider = vbString Then
    myList = myList & Chr(34) & ctl.ItemData(varItm) & Chr(34) & " , "
    End If
    If Devider = vbInteger Then
    myList = myList & ctl.ItemData(varItm) & " , "
    End If
    If Devider = vbDate Then
    myList = myList & Chr(35) & ctl.ItemData(varItm) & Chr(35) & " , "
    End If

    Next varItm
    FillFromList = Left(myList, Len(myList) - 2)
    Else
    FillFromList = ""
    End If
    End Function

  5. #5
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    Thats fine to use list boxes as filters, im trying to get a list box to great an in statment for a SQL query IE:

    dim variable as string
    variable = listbox

    select *
    from table
    where company in (variable)

    i have goten it to give me the list but im havinb problems with the commas. eather I can get it to have the comma at the frount of the list the back of the list or no commas at all, i need it to be none in the frount none in the back but in the middle

    any ideas?

  6. #6
    Join Date
    Nov 2002
    Location
    San Francisco
    Posts
    251
    did you read my reply and the code? What do you think it is doing..... it is one piece in long WHERE ..... tempstrSQL3 .... and tempstrSQL3 gets feed from listbox. You can have 15 listobex and you can easily put them together and get SQL.

    i have form and fast find form, there are 15 listboxes and few combos on fast find form, it generates RECORDSOURCE with WHERE and sticks it into FORM.


    each listbox generate
    SQL1 = AND myVariable IN (value1, value2, ...)

    If you select value with ID -1 it does not create anything (value -1 is <ALL> so why to slow down the SQL). It is also smart and puts value1, value2 into brackets based on datatype - string, number, date....
    after all you put together all SQL1 & SQL2 & .... and delete first AND and replace it with WHERE





    jiri
    Last edited by playernovis; 01-03-03 at 13:01.

  7. #7
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    Originally posted by playernovis
    did you read my reply and the code? What do you think it is doing..... it is one piece in long WHERE ..... tempstrSQL3 .... and tempstrSQL3 gets feed from listbox. You can have 15 listobex and you can easily put them together and get SQL.

    i have form and fast find form, there are 15 listboxes and few combos on fast find form, it generates RECORDSOURCE with WHERE and sticks it into FORM.



    jiri
    Im only dealing with ONE list box. using multipule selections allowing the end user to select as many companies as he/she wants to from where ever in the list. I need his sleections from this ONE list box to go into a where clause becuae my list is dynamic and it will change, and it is too long to create one list box for every posability.

    I need to cycle threw ONE list box with if then logic to say
    Find first record, add " marks find second selection if there is one and add , " " and so on till the end of the selection.

    Jim

  8. #8
    Join Date
    Nov 2002
    Location
    San Francisco
    Posts
    251
    it is exactly what is my code doing! As I said, it can work with <ALL> and it can work with different datatypes.....

    tempstrSQL3 = FillFromList(Me.lstCounty, vbInteger)

    me.lstcounty is a listbox from the form



    it put's into tempstrSQL3 list of all selected values, because it is integer, it does not put quotes between them, if one selected item has value -1, it returns blank string.


    note: when I create listbox I do union query of lisbox table and one value -1 with name <All> I use -1 cauze it puts it at the begin of the list, then I select it by default.



    jiri
    Last edited by playernovis; 01-03-03 at 13:09.

  9. #9
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    so lets disect this thing then shall we

    Trim(ctl.ItemData(varItm)) = "-1" ---what is this acomplishing

    and this bit of code: (varItm)) = "-2" Then
    seems to me that you are walking threw the list box testing for every combination or row number. If that is true, I cant do that if that is not true then what is that bit needed for?
    Jim

    Originally posted by playernovis
    Public Function FillFromList(ctl As Control, Devider As DataType) As String
    Dim varItm As Variant
    Dim myList As String

    myList = ""
    If ctl.ItemsSelected.Count > 0 Then
    For Each varItm In ctl.ItemsSelected
    If Trim(ctl.ItemData(varItm)) = "<All>" Or Trim(ctl.ItemData(varItm)) = "-1" Then
    FillFromList = ""
    Exit Function
    End If
    If Trim(ctl.ItemData(varItm)) = "<None>" Or Trim(ctl.ItemData(varItm)) = "-2" Then
    FillFromList = "<None>"
    Exit Function
    End If
    If Trim(ctl.ItemData(varItm)) = "<Blank>" Or Trim(ctl.ItemData(varItm)) = "-3" Then
    FillFromList = "<Blank>"
    Exit Function
    End If

    If Devider = vbString Then
    myList = myList & Chr(34) & ctl.ItemData(varItm) & Chr(34) & " , "
    End If
    If Devider = vbInteger Then
    myList = myList & ctl.ItemData(varItm) & " , "
    End If
    If Devider = vbDate Then
    myList = myList & Chr(35) & ctl.ItemData(varItm) & Chr(35) & " , "
    End If

    Next varItm
    FillFromList = Left(myList, Len(myList) - 2)
    Else
    FillFromList = ""
    End If
    End Function

  10. #10
    Join Date
    Nov 2002
    Location
    San Francisco
    Posts
    251
    i told you, my code is universal. I had listboxes, where I had next to individual values also <ALL> value and sometimes <NONE> value
    that-s why I have -1 and -2. If you don't have one value <ALL> or <NONE>, you can easily ignore it.

    my listbox in the form was union query of listbox lookup table and
    -1 <ALL>

    I always used <ALL> and when I opened the form I selected <ALL> as default.

    jiri

  11. #11
    Join Date
    Nov 2002
    Location
    San Francisco
    Posts
    251
    something like this....
    Attached Thumbnails Attached Thumbnails sample.bmp  

  12. #12
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    Your code is not making any sence to me what so ever. Instead of telling me that its in the code why dont you explane it
    Jim

  13. #13
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    Dim ctlSource As Control
    Dim strItems As String
    Dim intCurrentRow As Integer
    Set ctlSource = Customer_List

    For intCurrentRow = 1 To ctlSource.ListCount - 1
    If ctlSource.Selected(intCurrentRow) Then
    strItems = strItems & ", '" & ctlSource.Column(0, _
    intCurrentRow) & "' "
    End If
    Next intCurrentRow

    Test_List_Box.Value = strItems


    This simple code produces the list almost as I need it with the exception of the fact that I have a leading comma. How can I test for the first record in the list box, format it a certain way and then format the rest of the selected list box another way?

    Jim

  14. #14
    Join Date
    Nov 2002
    Location
    San Francisco
    Posts
    251
    check this Access97 simple form.... it has THREE Lisboxes (multi list boxes) and creates SQL based on selection... it uses STRING, INTEGER and DATE as part of generated query....


    jiri

    p.s. if you would look at last 4 rows of my code above, you would see how to cut leading comma.....
    Attached Files Attached Files

  15. #15
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    ok here is the last few lines of your code

    Next varItm
    FillFromList = Left(myList, Len(myList) - 2)
    Else
    FillFromList = ""
    End If
    End Function



    and this is my code:




    Dim ctlSource As Control
    Dim strItems As String
    Dim intCurrentRow As Integer
    Set ctlSource = Customer_List

    For intCurrentRow = 1 To ctlSource.ListCount - 1
    If ctlSource.Selected(intCurrentRow) Then
    strItems = strItems & ", '" & ctlSource.Column(0, intCurrentRow) & "' "
    End If
    Next intCurrentRow


    Test_List_Box.Value = Left(strItems, Len(strItems) - 2)


    Notice I added the bit of code to take off the leading comma, or so I thought. That bit of code didnt work. Im applying it to the string variable that is holding the data. Why didnt it work?
    Jim

Posting Permissions

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