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

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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!

Reply | Quote

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”

Reply | Quote

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.

Reply | Quote