Multi Select Code

[hi there]

Hi everyone,

i've been trying to create a multi select list box that passes the items selected as parameters to a query. i search the forum and found some posts related to this subject, but i could not understand them. i did some research and found a procedure that uses DAO to perform this action from the OnClick event for a control button on a form. i compiled the code and it seems to work, but i think i have my references mixed up.

here's a little reference background on my dbase:

i created the form "multi select form" that has a list box named "MultiSelectListBox". this list box uses a table named "Units" as the control source. i created a query based on a table named "Master Table". this is the table i want the query to search from. within the "Master Table" the query is suppose to query on the "Unit Description" field.

i used the following code on the OnClick event for a command button on the "multi select form".

Private Sub MultiSelectQuery_Click()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String

Set db = CurrentDb()
Set qdf = db.QueryDefs("MultiSelectQuery")

For Each varItem In Me!MultiSelectListBox.ItemsSelected
strCriteria = strCriteria & ",'" & Me!MultiSelectListBox.ItemData(varItem) & "'"
Next varItem

If Len(strCriteria) = 0 Then
MsgBox "You did not select anything from the list" _
, vbExclamation, "Nothing to find!"
Exit Sub
End If

strCriteria = Right(strCriteria, Len(strCriteria) - 1)
strSQL = "SELECT * FROM [Master Table] " & _
"WHERE [Master Table].[Unit Description] IN(" & strCriteria & ");"

qdf.SQL = strSQL
DoCmd.OpenQuery "MultiSelectQuery"

Set db = Nothing
Set qdf = Nothing

End Sub

whenever i run this query, a little parameter dialog box appears asking for input for the [Master Table.Unit Description]. then even when i manually type (single selection) the information i selected from the list box into the dialog box i still get an empty dynaset. can someone tell me what i'm doing wrong? any help will be GREATLY appreciated.

thanks

 

[Tim K.]

I think you need space after IN in your code. Try to add a message box in your code. Then check what returns from the code when you run it. What you see is the strSQL string. It should be sthg. like this

SELECT * FROM [Master Table] WHERE [Master Table].[Unit Description] IN ('FirstItem','SecondItem','ThirdItem',...);

...
strCriteria = Right(strCriteria, Len(strCriteria) - 1) 
strSQL = "SELECT * FROM [Master Table] " & _ 
"WHERE [Master Table].[Unit Description] IN (" & strCriteria & ");" 
[color="blue"]MsgBox strSQL[/color]
...

 

[WayneRyan]

hi there,

strCriteria = Right(strCriteria, Len(strCriteria) - 1)
strSQL = "SELECT * FROM [Master Table] " & _
"WHERE [Master Table].[Unit Description] IN(" & strCriteria & ");"

The syntax for the IN clause requires:

IN ('a', 'b', 'c')

unless strCriteria has the exact value: 'a', 'b', 'c'
with the punctuation, it will not work.

You will have to loop through your items selected in the listbox
and build strCriteria.

strCriteria = strCriteria & ", '" & listbox(i).value & "'"

Something like that.

hth,
Wayne

 

[hi there]

thanks for the responses Tim K and Wayne Ryan. tim i tried adding a message box in the code and it returned the following:

SELECT * FROM [Master Table] WHERE [Master Table].[Unit Description] IN ('4', '6');

where '4' and '6' correspond to the position of the items in the list box instead of the actual list box items (e.g. WWTP, coker). i'm not sure what this means.

Wayne i also looked at your post and i'm not sure exactly what to do. are you suggesting altering the string definition? if so i'm pretty new to VBA so i'm not exactly sure how to do this. could you please explain exactly what code i need to replace the following line with:

strCriteria = strCriteria & ",'" & Me!MultiSelectListBox.ItemData(varItem) & "'"
Next varItem


thanks again for the responses.

 

[WayneRyan]

hi there,

Other than having the indexes, rather than the values, your
select statement looks great! I don't know how your code
puts in the delimiters ...

I haven't used this technique before but I would have thought
that your syntax should be something like:


Me!MultiSelectListBox(index).Value

If I get time today, I'll try to check into this.

Wayne