run a query

[tony]

hi

after a user selected one or more item from a listbox, i click on a button
and i run this code

Private Sub cmdRecherche_Click()
    Dim strIN As String
    For Each Item In Me.lstTransit.ItemsSelected
        strIN = strIN & "'" &
Me.lstTransit.ItemData(Item) & "',"
    Next
    strWhere = " where transit in (" & Left(strIN,
Len(strIN) - 1) & ")"
    lblDGA.Caption = strWhere
       
    Dim strQuery As String
   
    strQuery = "select * from test3 " + strWhere
       
   
    Set qd = CurrentDb.Querydefs("qry1")
    qd.SQL = strQuery
    qd.Close
    DoCmd.OpenQuery "qry1"
   
       
End Sub

i get

error 3265
item not found in this collection

any idea?

table test3 exist....

 

[Tom Ellison]

Dear Tony:

From a quick scan of your post, I would say that the query "qry1" does not
exist before this code is run. That would cause an error message like what
you show. So, is that the case?

Tom Ellison

 

[Guest]

Also a couple more things

Change this line

strQuery = "select * from test3 " + strWhere

^^
to

strQuery = "select * from test3 " & strWhere
^^

You need to (should) use the Ampersand instead of the Plus (+) to
concatenate strings.


And you didn't declare (Dim) "qd" as a querydef.


(I googled 3265 and found (Paraphrased from a post on 9/1/2005 from Dirk
Goldgar):

"It (the 3265 error) means..... some line of code tried to access a member
of a
collection ("queryDefs"), probably using the name of the member ("qd") as a
key, but there was no member ("qd") in the collection by that name. "

IE, you didn't Dim it.

Try adding this line:

Dim qd As QueryDef


If you are trying to create a new query, HELP shows this syntax:

Set querydef = object.CreateQueryDef (name, sqltext)

"In a Microsoft Jet workspace, if you provide anything other than a
zero-length string for the name when you create a QueryDef, the resulting
QueryDef object is automatically appended to the QueryDefs collection. "


an example is:

' Create permanent QueryDef.
Set qd = CurrentDb.CreateQueryDef("qry1",strQuery)


HTH