Function to Evaluate using QueryDef or CreateQueryDefs 1

[sxschech]

Been using this construct to edit sql statements in code.

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

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

Could above 6 lines of code be simplified something like this:

stsql="Select..."
EditQryDef("qryQuery")



Here is what I tried.


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')")

Code:

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")

 

[1DMF]

You need to think about what you are trying to return from the function.

Looks to me you want to return a querydef object?

So you need to ensure that's what your function returns...

Function EditQryDef(ByVal stQueryName As String) As DAO.QueryDef
    
    On Error GoTo NewQueryDef
    
    Set EditQryDef = CurrentDb.QueryDefs(stQueryName)
    
QryDef_Exit:
    Exit Function
    
NewQueryDef:
    Set EditQryDef = CurrentDb.CreateQueryDef(stQueryName)
    Resume QryDef_Exit
End Function

Then to use it...

Dim qd As DAO.QueryDef
Set qd = EditQryDef("XYZ")

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[strongm]

That's odd. I posted a reply to the OP that has completely vanished.

 

[1DMF]

Maybe they didn't like your post and reported it for deletion

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

 

[sxschech]

Thanks so much 1DMF, that worked great.

 

[Andrzejek]

Would that work also:

Sub EditQryDef(ByVal stQueryName As String, ByRef qd As DAO.QueryDef)
    
    On Error GoTo NewQueryDef
    
    Set qd = CurrentDb.QueryDefs(stQueryName)
    
QryDef_Exit:
    Exit Sub
    
NewQueryDef:
    Set qd = CurrentDb.CreateQueryDef(stQueryName)
    Resume QryDef_Exit
End Sub

Then to use it...

Dim qd As DAO.QueryDef
[s]Set qd = EditQryDef("XYZ")[/s] 
Call EditQryDef("XYZ", qd)

Just wondering...

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[1DMF]

@Andrzejek That all depends if you were taught at school to treat all methods as a black box, and never use 'byref' variables (like I was ;-) )

@sxschech In all seriousness though, go with which ever one floats your boat (and you prefer to work with), I'm glad you found my code helpful.

"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music