WHERE items.ITEMCODE IN my list box

S

Steven M. Britton

I have an SQL query that just isn't working for me. Can
someone look at this and tell if you see anything I am
missing? What I am trying to do is have a query that can
run off of mulitiple selections from a listbox. My items
table has 6 columns: Record_Nbr, ID, ITEMCODE, ITEMQTY,
ITEMDESC, ITEMPRICE

The ID relates to my orders table... Here is the code I
could really use some help. Thanks.

Dim sInList As String
Dim vSelectedItem As Variant

' Populate sInList with a comma seperate list of values
For Each vSelectedItem In List246.ItemsSelected
sInList = sInList & List246.ItemData(vSelectedItem)
& ","
Next vSelectedItem

' Remove the trailing comma if it exists
If Len(sInList) > 1 Then
sInList = Left(sInList, Len(sInList) - 1)
End If
MsgBox sInList
' Change the SQL to use the IN list
CurrentDb.QueryDefs("qryItemsSelect").SQL = "SELECT *
FROM items WHERE items.ITEMCODE IN (" & sInList & ");"
MsgBox "Made it to Here"
DoCmd.OpenQuery ("qryItemsSelect"), acViewPreview, acEdit

The MsgBox sInList show all of the selections that I have
made, but I never get tot he MsgBox "Made it to Here". I
get an system error message that says: Item not found in
this collection.

-Steve
 
D

Douglas J. Steele

Is Itemcode text or numeric? If it's text, you need quotes around all of the
values.

On the other hand, the error message implies that you don't have a query
named qryItemsSelect. Perhaps you've made a typo in its name?
 
S

Steven M. Britton

Thanks Doug,

It's text so I changed it to read this.
CurrentDb.QueryDefs("qryItemsSelect").SQL = "SELECT *
FROM items WHERE items.ITEMCODE IN (" & " sInList " & ");"

Also I didn't have a query named qryItemsSelect, I made
that query and then was able to reach the MsgBox "Made it
to Here." So my next question is, what should I have in
the actual query that is in Access not in VB? Does it
require the sInList field? The reason I am asking is that
when I now click the button to run these commands I get a
pop up box that is labeled "Enter Parameter Value" and has
the Parameter as sInList...

What else am I missing? The current Query only has the
ITEMCODE in it.

-Steve
 
Top