...
If FindQuery("qryTemp") = True Then
CurrentDb.QueryDefs.Delete "qryTemp"
End If
Function FindQuery (strQueryName) as Boolean
For Each QueryDef in CurrentDB.Querydefs
If Querydef.Name = strQueryName Then
FindQuery=True
Exit Function
End if
Next
FindQuery=False
Exit Function
End Function
Or you could just cheat and
Code:
On Error Resume Next
CurrentDb.QueryDefs.Delete "qryTemp"
Sorry to come back on this again but i'm getting a 'variable not defined' error on the line "For Each QueryDef in CurrentDB.Querydefs" pointing at "QueryDef". Why is this? I thiought as it is an object not a variable it shouldn't be defined...
Function FindQuery (strQueryName) as Boolean
For Each QueryDef in CurrentDB.Querydefs
If Querydef.Name = strQueryName Then
FindQuery=True
Exit Function
End if
Next
FindQuery=False
Exit Function
End Function
Public Function FindQuery(QName) As Boolean
Dim QryDef As QueryDef
'----------
For Each QryDef In CurrentDb.QueryDefs
If LCase(QryDef.Name) = LCase(QName) Then
FindQuery = True
Exit For
End If
Next
End Function
you do need
Dim qdef As QueryDef
For Each qdef in CurrentDB.Querydefs
btw, you can use the same looping idea for modules, forms, tables, etc.
i'm still searching for a really good explanation as to when this is necessary and when it isn't. there's a thread around here about setting or not setting recordset variables that helps explain it somewhat but not completely. it seems to be a top-down thing. currentdb is what it says and doesn't need defining. the querydefs collection is automatically a part of that and doesn't need defining. but a single querydef in the collection needs defining because it could be one of many. (?)
if its STILL failing, it might be the currentdb falling out of scope-
try this
Code:
Function FindQuery (strQueryName) as Boolean
dim dbs as database
set dbs=currentdb
For Each QueryDef in DBS.Querydefs
If Querydef.Name = strQueryName Then
FindQuery=True
Exit Function
End if
Next
FindQuery=False
Exit Function
End Function
HOWEVER - this method reads every query, to see if one exists - very inefficient - if you just want to see if one query exists, just try reading it directly - so instead
Code:
Function FindQuery (strQueryName) as Boolean
dim dbs as database
dim found as boolean
set dbs=currentdb
on error goto fail
'next line succeeeds without error if qry exists
found = len(dbs.querydefs(strqueryname).name)>0
exithere:
findquery = found
exit sub
fail:
'no query of that name
found=false
resume exithere
End Function
and I actually agree with the early point by redneckgeek
if you just want to delete the query then simply the following code
either deletes the query, or fails, because there is no query - either way you are OK
Code:
On Error Resume Next
CurrentDb.QueryDefs.Delete "qryTemp"