multi-select list box query

[Mark Kubicki]

i'm trying to open a query based on a multi selection from a list box

the query is based on a table: Manufacturers
which includes the fields: Manufacturer and PrimaryCatagory
I would like to show all of the records where the field PrimaryCatagory
matches any of the selections from the list box

the name of the list box is lstCatagorySelect

----> the SQL string "seems" to be formatting correctly, but returns the
error "item not found in this collection"

any suggestions would be greatly appreciated...
thanks in advance,
mark

'-------------- start of code ------------------
Private Sub cmdOpenQuery_Click()
Set MyDB = CurrentDb()
strSQL = "SELECT Manufacturers.Manufacturer,
Manufacturers.PrimaryCatagory FROM Manufacturers"

' Build string by looping through the listbox
For i = 0 To lstCatagorySelect.ListCount - 1
If lstCatagorySelect.Selected(i) Then
If lstCatagorySelect.Column(0, i) = "<All>" Then
flgSelectAll = True
End If
strIN = strIN & "((Manufacturers.PrimaryCatagory)='" &
lstCatagorySelect.Column(0, i) & "')) OR ("
End If
Next i

'Create the WHERE string, and strip off the last comma of the IN string
strWhere = " WHERE " & _
"(" & Left(strIN, Len(strIN) - 4)

'If "<All>" was selected in the listbox, don't add the WHERE condition
If Not flgSelectAll Then
strSQL = strSQL & strWhere
End If

MyDB.QueryDefs.Delete "qryCatagories"
Set qdef = MyDB.CreateQueryDef("qryCatagories", strSQL)

'Open the query, built using the IN clause to set the criteria
DoCmd.OpenQuery "qryCataogies", acViewNormal

 

[John W. Vinson]

i'm trying to open a query based on a multi selection from a list box

the query is based on a table: Manufacturers
which includes the fields: Manufacturer and PrimaryCatagory
I would like to show all of the records where the field PrimaryCatagory
matches any of the selections from the list box

the name of the list box is lstCatagorySelect

----> the SQL string "seems" to be formatting correctly, but returns the
error "item not found in this collection"

any suggestions would be greatly appreciated...
thanks in advance,

Please post the actual value of strSQL.

However I would suggest doing what the comments in the code indicate - using
the IN clause - rather than long batches of OR clauses:

Private Sub cmdOpenQuery_Click()
Dim strSQL As String
Dim strIN As String
Set MyDB = CurrentDb()
strSQL = "SELECT Manufacturers.Manufacturer, " _
& "Manufacturers.PrimaryCatagory FROM Manufacturers "

' Build string by looping through the listbox
strIN = ""
For i = 0 To lstCatagorySelect.ListCount - 1
If lstCatagorySelect.Selected(i) Then
If lstCatagorySelect.Column(0, i) = "<All>" Then
flgSelectAll = True
Exit For ' bail out if you find an <All>
End If
strIN = strIN & "'" & lstCatagorySelect.Column(0, i) & "',"
End If
Next i

'Create the WHERE string, and strip off the last comma of the IN string
'If "<All>" was selected in the listbox, don't add the WHERE condition
If Not flgSelectAll Then
strSQL = strSQL & "WHERE Manufacturers.PrimaryCategory IN(" _
& Left(strIn, Len(strIn) - 1) & ")"
End If

MyDB.QueryDefs.Delete "qryCatagories"
Set qdef = MyDB.CreateQueryDef("qryCatagories", strSQL)

'Open the query, built using the IN clause to set the criteria
DoCmd.OpenQuery "qryCataogies", acViewNormal