Add description to a query 2

[Sandman83]

Hello all,

I have been trying to set up an Access DB as a front end to query an Oracle DB. I have been able to create pass through queries through code and, if the query already has a description, I have been able to retrieve the description through code using the following:

Dim db As DAO.Database
Dim strQueryDescription As String
    
    Set db = CurrentDb()
    db.QueryDefs.Refresh
    
    strQueryDescription = db.QueryDefs_
    (lstQryName.Column(0)).Properties("Description")
        
    lblDescription.Caption = strQueryDescription

For some reason though, when I try to do add a description to a query that doesn't have one I get a "Property not found error."

If anyone can tell me any way to add a description to a query without right clicking on the query and then clicking "properties", I would be greatly appriciate it.

Thanks,

Tim

 

[RickSpr]

The QueryDef object is a DAO object that is "borrowed" by Access. Its Description property isn't built-in, but an add-on property provided by Access as needed. That is, the Description property doesn't exist until Access adds it to the Properties collection of the QueryDef.

When you use the user interface to specify a description, Access creates the property for you, and from then on you can retrieve it like any property. But if a query has never been given a description, you need to create it by using the QueryDef.CreateProperty method.

The help file topic for CreateProperty has an example of how to do this.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[Sandman83]

Hi Rick, thanks for the post. I was trying that without success and thought maybe it didn't apply to queries. At first when I ran the code, it didn't seem to do anything. Not even give an error. So I had removed all that code. After your post I tried again, but now I keep getting error 91: Object variable or With block variable not set. I can't figure out what I am doing wrong. Below is the piece of code that I am using to try and create the property.

Dim prpDesc As Property
Dim strDesc As String

Set db = CurrentDb()
strDesc = "Description"

prpDesc = db.QueryDefs(strQueryName).CreateProperty(strDesc, dbText, strDescription)
db.QueryDefs.Append prpDesc

Note: At the top of the function I have Dim db as database and an input box that puts the description into strDescription. Any and all help is greatly appreciated.

 

[TonyJollans]

Hi Tim,

You are trying to add a description property to the Querydefs Collection. What you want to do is add it to the Properties Collection of the Querydef:

db.QueryDefs

.Properties

.Append prpDesc

Enjoy,
Tony

 

[TonyJollans]

Hi Tim,

Oops, hit the button too soon. It should of course be:

db.QueryDefs

(strQueryName).Properties

.Append prpDesc

Enjoy,
Tony

 

[Sandman83]

Hi Tony, thanks for the reply. I tried your suggestion, but I get a method or data member not found error. So I change it to the following.

db.QueryDefs(strQueryName).Properties.Append prpDesc

That gets rid of the metthod error, but I still get the original object variable not set error. Any ideas? Also, is the change I made correct? Thanks.

 

[TonyJollans]

Hi Tim,

You must've picked up my first post very quickly - the second was as quickly afterwards as I could do it. Yes your change is good

Just checked back at your post, you need to Set the property:

Set

prpDesc = db.QueryDefs(etc

Enjoy,
Tony

 

[Sandman83]

Thank you VERY much. It seems the answer to just about every problem is always something simple. Again thank you Rick and Tony both.