Query descriptions created in code (1 Viewer)

[arage]

Query descriptions created in code
I used the below to try & create a temporary query that will be routinely created & deleted whenever it is called. The below code is meant to create it & append a description to the query in case sometime in the future I forget what it’s purpose is. Problem is that I get an invalid use of property message on the fourth line. I’d appreciate any advice.
Set db = CurrentDb
Set qd = db.CreateQueryDef("qryEnterPayDetails", strSQL)
db.QueryDefs("qryEnterPayDetails").Properties ("Test desc")
DoCmd.OpenQuery "qryEnterPayDetails"

 

[Tim K.]

Try the code below.

Function CreateQueryDescrpt(strQuery As String, strDescrpt As String)
Dim dbs As Database
Dim qdf As QueryDef
Dim prop As Property

On Error GoTo Err_CreateQueryDescrpt

Set dbs = CurrentDb

Set qdf = dbs.QueryDefs(strQuery)

With qdf
For Each prop In .Properties
' On Error Resume Next
' Check if Description property already set, change it and exit
If prop.Name = "Description" Then
.Properties("Description") = strDescrpt
Debug.Print "Already set"
Exit Function
End If
Next prop

' If is not yet there, assign it
Set prop = .CreateProperty("Description", dbText, strDescrpt)
.Properties.Append prop
.Properties.Refresh
Debug.Print "Newly set"

End With

Bye_CreateQueryDescrpt:
Exit Function

Err_CreateQueryDescrpt:
Beep
MsgBox Error$, 48
Resume Bye_CreateQueryDescrpt

End Function