IF U ceate query dynamically, can U create query description too?

[Guest]

If you ceate a query dynamically, can you create the query description (in Db
window) on the fly too?

Also, For all the experts oput there, Please check out question: "creating
query from string". That one's really buggin me

 

[John Viescas]

Matt-

After you set the SQL property of the QueryDef object, also set its
Description property. Refresh the QueryDefs collection to see the new
description in the Database window.

--
John Viescas, author
"Building Microsoft Access Applications" (Coming Soon!)
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

 

[Guest]

Thanks John, but could you explain further, maybe give and example? here is
my create statement:

Set qdf = dbs.CreateQueryDef("qrySumOnAcct", strSQL)

 

[John Viescas]

Follow that with:

qdf.Description = "This query sums accounts."

--
John Viescas, author
"Building Microsoft Access Applications" (Coming Soon!)
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

 

[Guest]

That doesn't get past the debugger. ".description" is not a valid property.

 

[John Viescas]

Ooops. Sorry. I should have noticed you said Access 2000. The Description
property is a built-in property in Access 2002. You need to use a
CreateProperty:

Dim prp As DAO.Property

Set prp = qdf.CreateProperty("Description", dbText, "This query sums
accounts.")
qdr.Properties.Append prp

--
John Viescas, author
"Building Microsoft Access Applications" (Coming Soon!)
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

 

[Guest]

John,

I am getting any error: "Cannot append. An Object with that name already
exsists int he collection" Do you know what that means? Thanks.

dbs.QueryDefs.Delete "qrySumOnAcct"
Set qdf = dbs.CreateQueryDef("qrySumOnAcct", strSQL)

Set prp = qdf.CreateProperty("Description", dbText, "This query sums
accounts")
qdf.Properties.Append prp

 

[Guest]

I got it to work using:

StrQueryDesc = FundName & " Rollup Query"
Set qdf = dbs.QueryDefs("qrySumOnAcct")
qdf.Properties("Description") = StrQueryDesc

Even though I created the query in the prior step, Access seems to think the
description property is populated and won't allow me to append.

 

[John Viescas]

Yes. If this is an existing query that already has a Description property,
the code I gave you earlier will error. You originally told me you were
creating a new query, which should not have a Description property in A2000.
You can also find sample code in Help to set a property that you're not sure
exists. The code first attempts to assign the value in an error trap, and
if it gets property not found, then it creates the property and appends it.

--
John Viescas, author
"Building Microsoft Access Applications" (Coming Soon!)
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)