Results 1 to 13 of 13
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    New England (Connecticut)
    Posts
    200
    Thanks
    1
    Thanked 0 Times in 0 Posts

    multi-select list box (access 2K w/ windows 2K)

    I am really stumped!! I am trying to pass values from a multi-select list box (ItemsSelected) to a query parameter. I want to used the bound column in the list box to populate a select query so the query can pull all the records associated with the selected list box values. I have had success in this when the list box is not a multi select box. I seems that the behavior is all together different when it is designated multi-select. Can anyone help? Thanks
    Kevin

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: multi-select list box (access 2K w/ windows 2K)

    See for example <post#=438581>post 438581</post#> for how to handle a multiselect list box.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    New England (Connecticut)
    Posts
    200
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: multi-select list box (access 2K w/ windows 2K)

    Hans,

    Thank you for the tip. I have applied what I thought was the relevent part of the code to my situation. I sent the sql to a text box on the form and if gives me the following :

    "select Pricing_ID from Pricing where Pricing_ID in (N0684.019.1N2100,N0684.019.2N2100,N0684.019N2100) )"

    How do I get rid of the "select Pricing_ID from Pricing where Pricing_ID in"

    and just leave the query parameter that I need which would be

    " N0684.019.1N2100,N0684.019.2N2100,N0684.019N2100 " ?

    Or in my attempt to fit this code to my situation leave something off that I should not have?

    The "fit my situation code" looks something like this:

    "Dim frm As Form, ctl As Control
    Dim varItem As Variant
    Dim strSQL As String
    Dim strWhere As String
    Set frm = Forms!frmQuerySearchResults
    Set ctl = frm!vQuery_Results

    For Each varItem In Me.vQuery_Results.ItemsSelected
    strWhere = strWhere & "," & Me.vQuery_Results.ItemData(varItem)


    Next varItem

    strWhere = Mid(strWhere, 2)

    strSQL = "select Pricing_ID from Pricing where Pricing_ID in (" & strWhere & "))"


    Me![Text8] = strSQL"

    Thanks for your help.
    Kevin

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: multi-select list box (access 2K w/ windows 2K)

    If you want to use it as a query parameter, it should be

    In ("N0684.019.1N2100","N0684.019.2N2100","N0684.019N 2100")

    (quotes around the values since they are strings). To get this:

    Dim frm As Form, ctl As Control
    Dim varItem As Variant
    Dim strWhere As String

    Set frm = Forms!frmQuerySearchResults
    Set ctl = frm!vQuery_Results

    For Each varItem In Me.vQuery_Results.ItemsSelected
    strWhere = strWhere & "," & Chr(34) & Me.vQuery_Results.ItemData(varItem) & Chr(34)
    Next varItem

    strWhere = "In (" & Mid(strWhere, 2) & ")"

    Me![Text8] = strWhere

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    New England (Connecticut)
    Posts
    200
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: multi-select list box (access 2K w/ windows 2K)

    Hans,
    thanks again for the help. I now have the correct (I Think) string being passed to the text box on the form. However, when I set the select query criteria to that text box it produces nothing. If i copy and paste the string directly from the text box into the query criteria and run the query it gives me the results I want. Can I not set the criteria for a select query to the string in the text box? The string that is produced and sent to the text box is the exact string that is built into the select query criteria when I build it manually. The query does not work from the text box but when input directly into the query it works. The code that I am using is below.

    Dim frm As Form
    Dim varItem As Variant
    Dim strSQL As String
    Dim strWhere As String
    Set frm = Forms!frmQuerySearchResults
    Set ctl = frm!vQuery_Results

    For Each varItem In Me.vQuery_Results.ItemsSelected
    strWhere = strWhere & " Or " & Chr(34) & Me.vQuery_Results.ItemData(varItem) & Chr(34)


    Next varItem

    strWhere = Mid(strWhere, 5)

    strSQL = strWhere


    Me![Text8] = strSQL
    MsgBox strSQL

    Any thoughts?
    Thanks
    Kevin

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: multi-select list box (access 2K w/ windows 2K)

    It won't work that way. Let's take an example: you have a number field ID and you set the criteria in the query design grid to 1 Or 2 Or 3. The translation into SQL is WHERE ID=1 OR ID=2 OR ID=3.

    What exactly do you mean by "Can I not set the criteria for a select query to the string in the text box?" How do you intend to use the string in the text box?

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    New England (Connecticut)
    Posts
    200
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: multi-select list box (access 2K w/ windows 2K)

    Thanks for getting back to me so quickly. Here is the scenario for intended use. These are proposals for which I need to pull associated hours out of the database. I want the user to select the appropriate proposals from the list box. Then I want to run a query that takes all of the selected items from the text box and pull the associated hours from the database. Each proposal will have many records associated with it. Each record has a certain number of proposed hours. I want to display and report on all the reords associated with the selections in the list box. Does this help?
    Kevin

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: multi-select list box (access 2K w/ windows 2K)

    End users should never open queries directly. They should open a form or report based on the query instead. You can use a where-condition to filter the form or report.

    Dim frm As Form, ctl As Control
    Dim varItem As Variant
    Dim strWhere As String

    Set frm = Forms!frmQuerySearchResults
    Set ctl = frm!vQuery_Results

    For Each varItem In Me.vQuery_Results.ItemsSelected
    strWhere = strWhere & "," & Chr(34) & Me.vQuery_Results.ItemData(varItem) & Chr(34)
    Next varItem

    strWhere = "PricingID In (" & Mid(strWhere, 2) & ")"

    ' to open a form:
    DoCmd.OpenForm FormName:="frmMyForm", WhereCondition:=strWhere
    ' or if you want to open a report:
    DoCmd.OpenReport ReportName:="rptMyReport", View:=acViewPreview, WhereCondition:=strWhere

  9. #9
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    New England (Connecticut)
    Posts
    200
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: multi-select list box (access 2K w/ windows 2K)

    Hans,
    All of this data is passed to me via a third party and resides in access tables. There is a move a foot here to standarize a report using this data but in excel format. I am investigating our ability to get the data from access to excel. I wanted to use the transfer spreedshett method. I wanted to use a query (Possibly a make table query) to extract the data save it in a table and pass it to excel. In my vision of how this was going to work I wanted to pass the query parameter to the text box. I had a button on the form that would launch the query using the parameter criteria in the text box. This then could pass to excel. Excel is important here because this data is going to be used by another program that can handle excel and not access. If there is no way to accomplish this by using a query, is there another way I can get a record set from selected items in a multi select list box and pass that record set to excel? I am already using automation in word in another database and think that once I can get the recordset I need should be able to use automation with excel also.

    Thanks
    Kevin

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: multi-select list box (access 2K w/ windows 2K)

    There is no way to pass a where-condition to a query the way you can do that for a form or report. You could set the complete SQL for a query using DAO. You need to set a reference to the Microsoft DAO 3.6 Object Library in Tools | References...

    Dim dbs As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim strSQL As String

    ' assemble SQL string in code
    strSQL = "SELECT ... FROM ... WHERE ..."
    Set dbs = CurrentDb
    Set qdf = dbs.QueryDefs("qryTest")
    qdf.SQL = strSQL

    ' export query to Excel
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryTest", ...

  11. #11
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    New England (Connecticut)
    Posts
    200
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: multi-select list box (access 2K w/ windows 2K)

    Hans,
    Thank you for you help. As it turns out, I could was not comfortable using the multi select list box and the degree of programming it required. I came up with a work around that uses a regular list box and once an item is selected it copies the data to a new table for export. The user can select multiple items by selecting each one and clickin an add button. After all are seleced I just transfer the spreadsheet to excel. Thanks again for your help
    Kevin

  12. #12
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: multi-select list box (access 2K w/ windows 2K)

    A belated reply, it looks like you resolved this issue, but if interested in example of how to use a multi-select listbox for query criteria, see attached demo database (A2K format). Open frm_Demo, select one or more items in list, then click Open Query button. A query will open, filtered by selected items. This uses technique described in ACC2000: How to Create a Parameter In() Statement, modified somewhat - instead of a parameter, the query (qry_CustomerOrders) has calculated field that calls a function (GetControlVal) that returns the value of specified control on an open form:

    InParamEx([Customers]![CustomerID],GetControlVal("frm_Demo","SelectedItems_txt"))

    InParamEx is the function based on MSKB article. See code module basInParam and MSKB article for further details. Whenever user selects/unselects items in listbox, an "In" string is generated and stored in textbox (SelectedItems_txt). In actual use, the textbox (or footer) would be hidden from user, visible here for demo purposes.

    I've used this technique with multiple multi-select listboxes to generate more complex query criteria for reports, export functions, etc, & worked OK. The attached demo is simplified version, using stripped-down versions of the Northwind Customers and Orders tables. You may be able to adapt this somewhat Rube Goldberg-like technique for your own project.

    HTH

  13. #13
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,877
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: multi-select list box (access 2K w/ windows 2K)

    Mark

    Nice demo

    Thanks, 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
  •