how to use list box contents as query criterion

[Snowman]

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.

 

[Jeff Boyce]

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

 

[Marshall Barton]

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]

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)