Page 1 of 2 12 LastLast
Results 1 to 15 of 22

Thread: List boxes

  1. #1
    Join Date
    Feb 2004
    Location
    UK
    Posts
    246

    Unanswered: List boxes

    Hi,
    I'm trying to use a list box (unbound) on a form that will allow the user to select multiple items. Then then have the facilty to run a query where the query will use the selection in the list box as a parameter...............But it isn't working

    Normally if I'm using a combo box as a selection parameter in a query I use the format in the criteria part of the query and it works fine
    [Forms]![MyForm]![ComboboxName]

    But when I use the same format for a list I get no output, even if only one item is selected in the list. What am I obviously doing wrong?

    Regards
    John
    Last edited by Sticker; 06-17-04 at 08:00.

  2. #2
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    Are you using a Primary key or normal tekst?

    Regards

  3. #3
    Join Date
    Jun 2004
    Location
    Terrapin Nation
    Posts
    205
    Its a little more tricky with a list box because you have to reference the selected item(s) by row number. You also have to build the query's criteria string yourself using a loop similar to:

    For Each i In lst.ItemsSelected
    strFilter = strFilter & CM & "=" & "'" & lst.ItemData(i) & "'" & " OR "
    Next i


    Dont forget to get rid of the trailing "Or" and spaces like.

    strFilter = Left(strFilter, Len(strFilter) - 4)

  4. #4
    Join Date
    Feb 2004
    Location
    UK
    Posts
    246
    Hi guy's
    Thanks for the responses.

    I'm not actually using any primary keys as the data in the table that the list box is derived form is constantly being replaced by fresh data.

    I think I understand the need to reference each row as ther could be multiple selection criteria bu I'm not certain how I implement it in my query. Sorry to be very thick

    Regards
    John

  5. #5
    Join Date
    Feb 2004
    Location
    UK
    Posts
    246
    Sorry to be a pain but I'm really lost her, can anyone help me out?

    Regards
    John

  6. #6
    Join Date
    Jun 2004
    Location
    Nottinghamshire, UK
    Posts
    14
    Sticker

    You will find an article on this and a downloadable example at the following:

    Using a Microsoft Access Listbox to pass criteria to a query

    HTH

  7. #7
    Join Date
    Jun 2004
    Location
    Terrapin Nation
    Posts
    205
    You must concatenate a string criteria expression together. You will not be able to do it in the query's criteria (builder) by referencing the text box. Think about it. If you have the first , third, and fifth items selected how is your query going to know to reference:

    MyList.ItemData(1) OR MyList.ItemData(3) Or MyList.ItemData(5)?

    You see what I mean? Therefor you must create a custom criteria string by creating a function something like:

    Funciton StringCriteria() as String

    Dim strFilter as String

    For Each i In lst.ItemsSelected
    strFilter = strFilter & [Field you want in your criteria] & "=" & "'" & lst.ItemData(i) & "'" & " OR "
    Next i

    StringCriteria = Left(strFilter, Len(strFilter) - 4)

    End Function




    Then you would use StringCriteria() in the criteria of your query.

  8. #8
    Join Date
    Feb 2004
    Location
    UK
    Posts
    246
    Thanks for the reply guy's, unfortunately I have a few questions.

    • ItemsSelected - is this the name of my listbox?
    • lst.ItemData - should this be the name of my field?
    • Can I change the references that refer to specific listboxes and fields to be generic, ie can I use the ME statement?


    Regards
    John

  9. #9
    Join Date
    Jun 2004
    Posts
    96
    John,

    These are attributes/properties of the listbox, they gives you the control of the list box.

    1. ItemsSelected - refers you to the item(s) in the list that is being selected
    2. ItemData - allows you to refer to any item in list
    3. Me refers to the current opened form that you are working in. any controls that you created in the form can be referenced from Me. eg: you created a listbox and named it "lstSticker", you can reference to it be saying Me.lstSticker..

    Hope this clears some of your doubt

    Cyherus

  10. #10
    Join Date
    Feb 2004
    Location
    UK
    Posts
    246
    Hi Cyherus,
    Thanks for clearing that up, just one last question (hopefully).

    I've now created this new function (Sec_selection), how do I get the query that pulls the data out to refer to this function in its criteria selection?

    Regards
    John

  11. #11
    Join Date
    Jun 2004
    Posts
    96
    function Sec_selection does what? does it return a value?
    also, you cannot create query that simply gets criteria from functions, you have to manipulate or build these statements.. as what the other good forummers here have stated..

  12. #12
    Join Date
    Feb 2004
    Location
    UK
    Posts
    246
    OK guy's thanks for you help I have achieved the end result with the codes as follows. I just have one more question. There is also another list box on this particular form and what I want to do is exactly the same as on this list box, but combine the two. That is if the user selects 2 items in the first list box and 5 from the second list box, I want to pass all 7 selections to the query.

    Can this be done and if so how

    Regards
    John

    Private Sub Procede_Click()

    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"

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

    strWhere = " WHERE [LORSCODE] in (" & Left(strIN, Len(strIN) - 1) & ")"

    If Not flgSelectAll Then
    strSQL = strSQL & strWhere
    End If

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

    DoCmd.OpenQuery "Select_security_qry", acViewNormal

    For Each varItem In Me.LORSCODE2.ItemsSelected
    Me.LORSCODE2.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
    MsgBox Err.Description
    Resume Exit_procede_Click
    End If

    End Sub

  13. #13
    Join Date
    Feb 2004
    Location
    UK
    Posts
    246
    I've replicated the code for the second list box which works fine, but am I struggling how I would combine the 2 to get one output. anyone got any ideas?

    Regards
    John

  14. #14
    Join Date
    Jun 2004
    Posts
    96
    2nd list box to get data from another table?

  15. #15
    Join Date
    Feb 2004
    Location
    UK
    Posts
    246
    Hi Cyherus,
    The 2nd list box is looking at the same table, it just present the list in a different order.
    For example list box 1 present the user with a list in "code number" order and uses this code number to pull out the data. The 2nd list box looks at the same data but presents it to the user in "Name" order, the "code number" (which is the key) is hidden. So both list boxes would be returning the same field to the query.

    Regards
    John

Posting Permissions

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