Function for QueryDefs and CreateQueryDef

[sxschech]

Been using this construct to edit sql statements in code.

If QueryExists("qryQuery") Then
    Set qd = db.QueryDefs("qryQuery")
Else
    Set qd = db.CreateQueryDef("qryQuery")
End If

Would like to find out if this can be put into a function or if can put 'QueryDefs' and 'CreateQueryDef' in a variable so can use one line instead of using the above for all the places I modify a query. I experimented with Eval and either didn't have syntax right or couldn't get it to work.

module:

Function EditQryDef(stQueryName As String)
    'Edit the query based on supplied variables
    'Test for existence.  If query exists, edit
    'querydef, otherwise create querydef.  Use
    'to replace all the instances in the other
    'modules and subs
    '20150918
    Dim db As DAO.Database
    Dim qd As DAO.QueryDef
    
    On Error GoTo NewQueryDef
    
    Set db = CurrentDb
    EditQryDef = "Set qd = db.QueryDefs(""" & stQueryName & """)"

    
QryDef_Exit:
    Exit Function
    
NewQueryDef:
    EditQryDef = "Set qd = db.CreateQueryDef(""" & stQueryName & """)"
    Resume QryDef_Exit
End Function

form:

Eval("EditQryDef('XYZ')")

Eval(EditQryDef("XYZ"))

Eval ("EditQryDef('XYZ')") give run time error 91
Eval (EditQryDef("XYZ")) gives run time error 2482

Or how could I get the statement to run with a variable? example qdqc would contain either "CreateQueryDef" or "QueryDefs"


Set qd = db. & qdqc & ("qryQuery")

 

[pbaldy]

I never bother creating, I just modify the SQL of existing queries. Here's the line from my function:

Set qdf = db.QueryDefs(strQdfName)

 

[sxschech]

Thanks. However sometimes due to various situations, if the code breaks or the query has been deleted or not yet created because it is new, then need to test if the query exists or not.

 

[pbaldy]

That's fine, I was just giving the syntax to use the variable.