Multi Select list Box

[SG]

I have the following code which is supposed to return the StockTakeReportByProductGroup populated by products dependant on the product selected in ListFilter which is a list box containing only the product groups nothing else. This is a text field not numerical. All records are returned for some reason can anyone point me in the correct direction?

Thannks in advance!



Private Sub Command2_Click()

' Declare variables
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
' Get the database and stored query
Set db = CurrentDb()
Set qdf = db.QueryDefs("Newquery18")
' Loop through the selected items in the list box and build a text string
For Each varItem In Me!ListFilter.ItemsSelected
strCriteria = strCriteria & ",'" & Me!ListFilter.ItemData(varItem) & "'"
Next varItem
' Check that user selected something
If Len(strCriteria) = 0 Then
MsgBox "You did not make a selection from the Labels list!" _
, vbExclamation, "No Labels Found!"
Exit Sub
End If
' Remove the leading comma from the string
strCriteria = right(strCriteria, Len(strCriteria) - 1)

' Build the new SQL statement incorporating the string
strSQL = "SELECT PRODUCTS.Itemcode, PRODUCTS.DESCRIPTION, PRODUCTS.PRODUCTGROUP, PRODUCTS.RRPRICE, PRODUCTS.SALEPRICE, PRODUCTS.BUYPRICE, PRODUCTS.ManufacturerName, PRODUCTS.Manufacturer, tblmanufacturers.ManufacturerName " & _
"FROM tblmanufacturers INNER JOIN PRODUCTS ON tblmanufacturers.ManufacturerID = PRODUCTS.Manufacturer " & _
"WHERE (products.productgroup) In (" & strCriteria & ") " & _
"ORDER BY PRODUCTS.PRODUCTGROUP;"










Debug.Print strSQL



' Apply the new SQL statement to the query
qdf.SQL = strSQL
' Open the query
DoCmd.OpenReport "StockTakeReportByProductGroup", acViewPreview




' Empty the memory
Set db = Nothing
Set qdf = Nothing
End Sub

 

[Mr B]

SG,

Take look at you code:
For Each varItem In Me!ListFilter.ItemsSelected
strCriteria = strCriteria & ",'" & Me!ListFilter.ItemData(varItem) &
"'"
Next varItem

Try placing a breakpoint on the "Next varItem" line. Run you code and check
the value in the strCriteria variable after the first time it sets a value to
the variable. If I am correct, the strCriteria variable will have a "," as
the first character. In your code you simply assign strCriteria to have a
comma and the value from your list box.

You need to have some additional code like
For Each varItem In Me!ListFilter.ItemsSelected
If strCriteria = "" then
strCriteria = '" & Me!ListFilter.ItemData(varItem) & "'"
Else
strCriteria = strCriteria & ",'" &
Me!ListFilter.ItemData(varItem) & "'"
End If
Next varItem

You only need the comma at the right end of your next value not at the
beginning of the string.

 

[Dale Fye]

So, what shows up in the Immediate window when you run this? Is strCriteria
reflected in strSQL?

Dale