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

Topic

New Reply

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

Reply | Quote

Replies

See for example post 438581 for how to handle a multiselect list box.

Reply | Quote

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.

Reply | Quote

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

In (“N0684.019.1N2100″,”N0684.019.2N2100″,”N0684.019N2100”)

(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

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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”, …

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Mark

Nice demo

Thanks, John

Reply | Quote