Multi Select list Box

  • Thread starter Thread starter SG
  • Start date Start date
S

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
 
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.
 
So, what shows up in the Immediate window when you run this? Is strCriteria
reflected in strSQL?

Dale
 
Back
Top