Item not found in this collection

[David Ball]

Hi,

I have a listbox on a form where a value can be selected and then command button pressed to see a report filtered by the selection from the listbox.

I am getting an error "Item not found in this collection" when I press the command button.

This is code I got from this site years ago and have used many times without any problems. I have checked and re-checked and can't find the problem.

The field "Job No" that I am filtering by is a simple short text field with values such as K495, K516, etc.

Can anyone see any errors in my code?

Private Sub cmdJobNo_Click()
On Error GoTo Err_cmdJobNo_Click
Dim MyDB As DAO.Database
Dim qdef As DAO.QueryDef
Dim i As Integer
Dim strSQL As String
Dim strWhere As String
Dim strIN As String
Dim flgSelectAll As Boolean
Dim varItem As Variant
Set MyDB = CurrentDb()
strSQL = "SELECT * FROM Spool Data"
'Build the IN string by looping through the listbox
For i = 0 To lstJobNo.ListCount - 1
If lstJobNo.Selected(i) Then
If lstJobNo.Column(0, i) = "All" Then
flgSelectAll = True
End If
strIN = strIN & "'" & lstJobNo.Column(0, i) & "',"
End If
Next i
'Create the WHERE string, and strip off the last comma of the IN string
strWhere = " WHERE [Job No] in " & _
"(" & Left(strIN, Len(strIN) - 1) & ")"
'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 "qryByJobNo"
Set qdef = MyDB.CreateQueryDef("qryByJobNo", strSQL)
'Open the query, built using the IN clause to set the criteria
DoCmd.OpenReport "rptByJobNo", acViewReport
'Clear listbox selection after running query
For Each varItem In Me.lstJobNo.ItemsSelected
Me.lstJobNo.Selected(varItem) = False
Next varItem

Exit_cmdJobNo_Click:
Exit Sub
Err_cmdJobNo_Click:
If Err.Number = 5 Then
MsgBox "You must make a selection(s) from the list" _
, , "Selection Required !"
Resume Exit_cmdJobNo_Click
Else
'Write out the error and exit the sub
MsgBox Err.Description
Resume Exit_cmdJobNo_Click
End If
End Sub


Thanks very much

Dave

 

[isladogs]

Its telling you that a field (possibly more than one) is missing from the recordset being built in the code

Probably you deleted a field that used to be part of this routine when it still worked

Try one or more of the following
When the error occurs click debug and see which code is highlighted
Or add break points or step through the code to achieve the same

I expect the error will be where your QueryDef occurs
So you could also add the line Debug.Print strSQL immediately before the QueryDefs line

Copy & paste the text from the immediate window into SQL view of the query design window then change to design view. Hopefully the error will be obvious

Have fun

 

[arnelgp]

check this line:

    strSQL = "SELECT * FROM Spool Data"

you need to add squre parenthesis if your table/field has space between them:

    strSQL = "SELECT * FROM [Spool Data]"

 

[David Ball]

Thanks very much,

I found the problem. My query name was spelled wrong.