List Box

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a listbox that is populated depending on what value is selected in a
combo box. Having the listbox should enable users to select multiple values
and then click a button to run a querydef. I am getting an error 'Item not in
collection' whenever I click the button. Any suggestions or thoughts why I am
getting this error?
 
I have a listbox that is populated depending on what value is selected in a
combo box. Having the listbox should enable users to select multiple values
and then click a button to run a querydef. I am getting an error 'Item not in
collection' whenever I click the button. Any suggestions or thoughts why I am
getting this error?
Click to expand...

Not a clue; care to post the VBA code in the Click event? There's
clearly a problem in the code, but without being able to see it, no
way to tell what.

John W. Vinson[MVP]
 
Here's the code from the OnClick event of the View button:

' Declare variables
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String

' Get the database and stored query
Set db = CurrentDb()
Set qdf = db.QueryDefs("qryMultiSelect")

' Loop through the selected items in the list box and build a text string
For Each varItem In Me!LBX_SOFTWARE_PRODUCT.ItemsSelected
strCriteria = strCriteria & ",'" &
Me!LBX_SOFTWARE_PRODUCT.ItemData(varItem) & "'"
Next varItem

' Remove the leading comma from the string
strCriteria = Right(strCriteria, Len(strCriteria) - 1)

' Build the new SQL statement incorporating the string
strSQL = "SELECT s.supplier_name AS MANUFACTURER, p.SFWPRODUCT_NAME,
sp.purchasing_org AS ORG_CODE, "
strSQL = strSQL & " sum(SP.PURCHASE_QUANTITY) AS QUANTITY "
strSQL = strSQL & " FROM SOFTWARE_PURCHASE AS sp, supplier AS s,
SFWPRODUCT AS p "
strSQL = strSQL & " WHERE SP.PURCHASE_DATE Between #1/1/2002# And
#12/31/2005# "
strSQL = strSQL & " And SP.MANUFACTURER_ID=S.SUPPLIER_ID And
SP.MANUFACTURER_ID=P.MANUFACTURER_ID"
strSQL = strSQL & " And SP.SFWPRODUCT_ID=P.SFWPRODUCT_ID And
S.SUPPLIER_NAME Like '" & [Forms]![CSAM_DEMO_FORM2]![CBX_MANUFACTURER] & "*' "
strSQL = strSQL & " And SP.PURCHASING_ORG Like '" &
[Forms]![CSAM_DEMO_FORM2]![CBX_ORG_CODE] & "*' "
strSQL = strSQL & " And P.SFWPRODUCT_NAME IN (" & strCriteria & ")"
strSQL = strSQL & " GROUP BY s.supplier_name, p.SFWPRODUCT_NAME,
sp.purchasing_org;"

' Apply the new SQL statement to the query
qdf.SQL = strSQL

' Open the query
DoCmd.OpenQuery "qryMultiSelect"

' Empty the memory
Set db = Nothing
Set qdf = Nothing
 
Here's the code from the OnClick event of the View button:
Click to expand...

Don't see anything blatantly obvious. Try opening the code in the VBA
editor; put a breakpoint on an executable (i.e. not a Dim) line by
clicking the mouse in the grey bar to the left of the code window.

Run the code, and step through it line by line using F8.

Note where in the code the error occurs, and use the Immediate window
to investigate the values of the variables; or hover the mouse over
variables to see their value. You should be able to identify the one
that's generating the error.

John W. Vinson[MVP]
 
Back
Top