WHERE items.ITEMCODE IN my list box

  • Thread starter Thread starter Steven M. Britton
  • Start date Start date
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
 
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?
 
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
 
Back
Top