how to use list box contents as query criterion Snowman 12/8/2008 9:37:00 PM Good day all, I have a list box of invoice IDs that I'd like to use as criterion in a query that returns records matching the invoice ID(s) selected by the user. Here's what I have so far which generates an error (run time 3265 Item not found in this collection) pointing to the DELETE command; Dim iDb As Database Dim qdef As 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 iDb = CurrentDb Dim stDocName As String stDocName = "qry_900" strSQL = "SELECT * FROM tbl_freight_remit_archive" 'loop through listbox For i = 0 To lstBatchID.ListCount - 1 If lstBatchID.Selected(i) Then strIN = strIN & "'" & lstBatchID.Column(0, i) & "'," End If Next i 'remove the last comma of the IN string strWhere = " WHERE [lstBatchID] in (" & Left(strIN, Len(strIN) - 1) & ")" iDb.QueryDefs.Delete stDocName Set qdef = iDb.CreateQueryDef(stDocName, strWhere) 'Open the query DoCmd.OpenQuery stDocName, acViewNormal thanks for any advice.

Re: how to use list box contents as query criterion "Jeff Boyce" <nonsense[ at ]nonsense.com> 12/8/2008 10:50:44 PM Your procedure appears to be attempting to delete the query you are defining. What happens when the query doesn't already exist? (wait, wait! don't tell me! You get an error 3265?!) If you embed that delete statement in an If Then clause so you can bail out if the query didn't previously exist, that might work. Or if you are using error trapping in your procedure, test for the error 3265 there. Good luck! Regards Jeff Boyce Microsoft Office/Access MVP "Snowman" <Snowman[ at ]discussions.microsoft.com> wrote in message news:AAF6F0CE-3578-4993-B99A-B961353DD96B[ at ]microsoft.com... [Quoted Text] > Good day all, > > I have a list box of invoice IDs that I'd like to use as criterion in a > query that returns records matching the invoice ID(s) selected by the > user. > Here's what I have so far which generates an error (run time 3265 Item not > found in this collection) pointing to the DELETE command; > > Dim iDb As Database > Dim qdef As 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 iDb = CurrentDb > Dim stDocName As String > stDocName = "qry_900" > > strSQL = "SELECT * FROM tbl_freight_remit_archive" > > 'loop through listbox > For i = 0 To lstBatchID.ListCount - 1 > If lstBatchID.Selected(i) Then > strIN = strIN & "'" & lstBatchID.Column(0, i) & "'," > End If > Next i > > 'remove the last comma of the IN string > strWhere = " WHERE [lstBatchID] in (" & Left(strIN, Len(strIN) - 1) & ")" > > iDb.QueryDefs.Delete stDocName > > Set qdef = iDb.CreateQueryDef(stDocName, strWhere) > 'Open the query > DoCmd.OpenQuery stDocName, acViewNormal > > thanks for any advice.

Re: how to use list box contents as query criterion Marshall Barton <marshbarton[ at ]wowway.com> 12/8/2008 10:56:38 PM I edited your code below. Note that if you know that the query always exists. it would be better to set it's SQL property instead of deleting and recreating it every time. -- Marsh MVP [MS Access] Snowman wrote: [Quoted Text] >I have a list box of invoice IDs that I'd like to use as criterion in a >query that returns records matching the invoice ID(s) selected by the user. >Here's what I have so far which generates an error (run time 3265 Item not >found in this collection) pointing to the DELETE command; > >Dim iDb As Database >Dim qdef As 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 iDb = CurrentDb >Dim stDocName As String >stDocName = "qry_900" > >strSQL = "SELECT * FROM tbl_freight_remit_archive" > 'loop through selected items in listbox For varItem In lstBatchID.ItemsSelected strIN = strIN & ", '" & lstBatchID.ItemData(varItem) Next varItem > 'remove the first comma of the IN string If Len(strWhere) > 0 Then strWhere = " WHERE [lstBatchID] IN(" & Mid(strIN, 3) & ")" End If > >iDb.QueryDefs.Delete stDocName > Set qdef = iDb.CreateQueryDef(stDocName, strSQL & strWhere) >'Open the query >DoCmd.OpenQuery stDocName, acViewNormal