Results 1 to 9 of 9
  1. #1
    Lounger
    Join Date
    Sep 2001
    Location
    NSW, Australia
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using multu-select list box values as query parame (Access97)

    I have a multi-select list box on one of my forms. I want to pass the selected items to stored query as a parameter. I know how to concatenate all the selected items, however, I can not work out how to get the query to accept the concatenated string as a parameter. For example, we have various grant applicants in our database, ie. A- Area, C- CERP, U - University and so on. I need to open a stored query based on the applicants selected in the multi-select list box.

    I tried concatenating selected applicants and storing the concatenated string in the text box called txtApplicants and then referring to txtApplicants in query criteria. For instance, if the user selects A and U list items, I would store "A", "U" in the textbox and refer to it in the criteria using "IN" statement, ie. IN ([forms]![frmGrants]![txtApplicants]).

    Normally if I type IN ("A", "U") in the criteria box I would get a certain number of records, however, referring to a form field generates an empty recordset. I also tried using LIKE statement without success.

    I would appreciate any help I can get.

    PS: I took a look at an example posted by Dev Ashish on The access Web (httt://www.mvps.org/access/forms/frm0007.htm), which discussed this topic.

    He actually did have a note stating that his method can be used in a parameterized query provided I pass the entire Where clause to it via code as a parameter. The question remains, how do I pass the entire Where clause to parameterized query via code as a parameter?

    Following is the article from httt://www.mvps.org/access/forms/frm0007.htm. Could any one let me know how to achive my objective? Better still, if you work it out using a database, could you send me the solution database to have a look it at john.fejsa@hunter.health.nsw.gov.au. Much appreciated.

    ================================================== =========
    (Q) I have a MultiSelect listbox control on my form. I want to pass the selected items to a query as a parameter. How do I do this?

    (A) Unlike simple listbox controls which can be referenced as a parameter by a query, MultiSelect listboxes cannot be used directly as a parameter. This is because calling the listbox (Forms!frmMyForm!lbMultiSelListBox) from anywhere will not automatically concatenate all the selected items. You need to build the criteria yourself.

    Note: You can still use a parameterized query provided you pass the entire Where clause to it via code as a parameter. (eg. Have the query reference a hidden control to which you manually assign the complete WHERE clause using the following logic.)

    For example,
    '******************** Code Start ************************
    Dim frm As Form, ctl As Control
    Dim varItem As Variant
    Dim strSQL As String
    Set frm = Form!frmMyForm
    Set ctl = frm!lbMultiSelectListbox
    strSQL = "Select * from Employees where [EmpID]="
    'Assuming long [EmpID] is the bound field in lb
    'enumerate selected items and
    'concatenate to strSQL
    For Each varItem In ctl.ItemsSelected
    strSQL = strSQL & ctl.ItemData(varItem) & " OR [EmpID]="
    Next varItem

    'Trim the end of strSQL
    strSQL=left$(strSQL,len(strSQL)-12))
    '******************** Code end ************************
    ================================================== =========

    Thanks in advancejohn.fejsa@hunter.health.nsw.gov.au <img src=/S/surrender.gif border=0 alt=surrender width=31 height=23>

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Using multu-select list box values as query parame (Access97)

    It sounds like you're trying to do this with a saved query referencing either a multiselect listbox or a textbox that holds the concatenated values of a multiselect listbox. What Dev's article is suggesting is that you create the query on the fly in code by parsing out the individual selections from the multiselect listbox and using them to create a Where clause for the query SQL you build in code.
    Charlotte

  3. #3
    Lounger
    Join Date
    Sep 2001
    Location
    NSW, Australia
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using multu-select list box values as query parame (Access97)

    You are right, I am trying use a saved query referencing either a multiselect listbox or a textbox that holds the concatenated values of a multiselect listbox.

    I probably misread Dev's solution. I know it's easy to create an SQL string and base the recordset on the SQL string but that

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Using multu-select list box values as query parame (Access97)

    Not directly. You have multiple answers in the multiselect or in the concatenated string, and the only way to use multiple answers in a query is either with them separated by an OR operator or in a comma-delimited array using the IN keyword. That means that you need to manipulate your answers in order to use them as criteria. Multiselects look like a good idea, but they are only really useful for batch processing, not for what you're trying to do.
    Charlotte

  5. #5
    fsbco
    Guest

    Re: Using multu-select list box values as query parame (Access97)

    Instead of putting your list of choices into a concatenated text box, why not put them into a temporary table and use a subselect as the criteria of your saved query - ie:
    in (select applicant from tblTempApplicant)
    just my <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15>
    fsbco

  6. #6
    Lounger
    Join Date
    Sep 2001
    Location
    NSW, Australia
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using multu-select list box values as query parame (Access97)

    That's what I eventually had to do. Bit of a work around but it

  7. #7
    New Lounger
    Join Date
    May 2003
    Location
    Detroit, Michigan, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using multu-select list box values as query parame (Access97)

    As a newcomer to the Lounge, I was thrilled to see this! It is exactly the topic I've been struggling with off and on for the last two weeks!!

    I've been trying to populate a table with the selected items from several multi-select list boxes so that I can then query it as a 'parameter table' in an sql sp. However, the code for populating the table has eluded me to the point of frustration!!

    I'd be very grateful if you could show me how you did this, or at least point me in the right direction.

    Thanks in advance!

  8. #8
    Lounger
    Join Date
    Sep 2001
    Location
    NSW, Australia
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using multu-select list box values as query parame (Access97)

    Two ways you can use my solution.

    SOLUTION ONE.
    ==============

    1) Populate selected criteria table
    2) Base your query on selected criteria table

    Step One: Populate Selected criteria table
    --------------------------------------------------------
    Private Sub cmdInListboxTest_Click()
    'Opens a query depending on the selected applicant(s)
    Dim ctl As ListBox, var As Variant
    Dim dbs As Database
    Dim rst As Recordset
    Dim strCriteria As String, temp As String

    Set ctl = Me!lstApplicants

    'If no selection, display warning and exit
    If ctl.ItemsSelected.Count = 0 Then
    MsgBox "Please select one or more applicants"
    Exit Sub
    Else
    'builds a selected applicants table
    'using each of the selected list applicants
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "qryDeleteCriteriaTable" 'Delete previously selected applicants
    DoCmd.SetWarnings True
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("select * from tblApplicantsCriteria")
    For Each var In ctl.ItemsSelected
    With rst
    .AddNew
    .strCriteria = ctl.ItemData(var)
    .Update
    End With
    Next var
    End If

    rst.Close
    Set ctl = Nothing
    Set dbs = Nothing

    End Sub

    Step Two: Base your query on selected criteria table
    ----------------------------------------------------------------------
    SELECT tblGrants.GrantID, tblGrants.Title, tblGrants.SponsorGrantID, tblGrants.CenterGrantID, Left([CenterGrantID],1) AS ApplicantCode
    FROM tblGrants
    WHERE (((Left([CenterGrantID],1)) In (select strCriteria from tbltblApplicantsCriteria)));


    SOLUTION TWO
    =============

    1) Create query definintion
    2) Open created query or report base on created query

    Step On: Create query definintion
    ----------------------------------------------

    Private Sub cmdRunQuery_Click()
    Dim dbs As Database
    Dim ctl As ListBox, var As Variant
    Dim strCriteria As String, temp As String
    Dim qdf As QueryDef
    Dim strSQLSelection As String
    Dim strSQL As String


    Set dbs = CurrentDb
    Set ctl = Me!lstApplicants

    'Setup query header
    strSQLHeader = "SELECT DISTINCT tblGrants.GrantID, tblGrants.Title, " _
    & "tblGrants.SponsorGrantID, tblGrants.CenterGrantID, " _
    & "FROM tblGrants " _
    & "WHERE CenterGrantID In ("

    ' Check listbox
    'If no selection, display warning and exit
    Set ctl = Me!lstApplicants

    If ctl.ItemsSelected.Count = 0 Then
    MsgBox "Please select one or more applicants"
    Exit Sub
    'builds SQL WHERE clause
    'using each of the selected applicants
    Else
    'Save selected items
    For Each var In ctl.ItemsSelected
    strSQLSelection = ctl.ItemData(var)
    Next var
    End If

    'Join SQL statements
    strSQL = ""
    ' Make sure everything's OK with strSQLSelection, just in case...
    If strSQLSelection, <> "" Then
    strSQL = strSQLHeader & strSQLSelection & ";"
    ' Create query that selects requred applicants filter
    Set qdf = dbs.CreateQueryDef("qrySelectedApplicants", strSQL)
    else
    Beep
    MsgBox "Please call John Fejsa on 46336 immediately to report this error.", vbCritical, "Unexpected error occured!"
    Exit sub
    End If

    ' You can now use the new query just like any other query
    ' for example to use as a source for your report or form.

    set dbs = Nothing
    Set ctl = Nothing

    Step Two: Open new query
    ------------------------------------
    'open query
    DoCmd.OpenQuery "qrySelectedApplicants"
    <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  9. #9
    New Lounger
    Join Date
    May 2003
    Location
    Detroit, Michigan, USA
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using multu-select list box values as query parame (Access97)

    John -
    FANTASTIC!! Thanks alot!! It works like a charm!!

    I was actually thinking about using the recordset approach, but I just couldn't get it to work. Thanks for turning on the light bulb!!

    I sure appreciate it.

Posting Permissions

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