Can You Call the Description of a Database Object in VBA?

[Curious]

Hi people,

I'm using the following code to generate a list of the reports I want for a combo box:

For Each accObjectQry In CurrentData.AllQueries

   Me.cboSelectReport.AddItem accObjectQry.name 

Next

This grabs the queries for the reports I want to generate on my form through the combo box.

However, the name of the report, is taken from the name of the query I use to generate the report, e.g.

Report: rptSale has query name = qryRptSales.

I want to keep the names as they are for consistency in my naming conventions for the database and to avoid programming problems with spaces in db object names.

I thought I could use the Description field of the query. So I right mouse clicked on my query, then went to Properties, and under Description, wrote a nicely formatted name for the query ("My Sales Report").

I was hoping to call that Description field through VBA. But it doesn't seem to be an option when I try to reference the

Dim accObjectQry As Access.AccessObject

variable I setup.

Is it possible to call the Description of a Database Object (in this case a query) through VBA?

Thanks in advance.

 

[Moniker]

Use the QueryDefs object, like so:

CurrentDb.QueryDefs("Your_Query_Name").Properties("Description")

If the description of Your_Query_Name is "This is my fancy description", then this will set the variable "txtDesc" to that:

txtDesc = CurrentDb.QueryDefs("Your_Query_Name").Properties("Description")

 

[Curious]

Works like a charm! Thanks!

 

[RoyVidar]

I have a tendency of creating one or more extra tables for report information. Using that, I can use simple SQL to populate combos/listboxes etc, and have all the extra information I wish, for instance user friendly report name and description, margins, user preferences for the report...

 

[Curious]

RoyVidar, I like your idea. If I do it the way I setup, I have to make sure each query / report has a Description in the database properties windows. If not, my code returns an error when the combo box is populated.

However, if I put my names in a table, I could still lookup the query names from the database object names, through a combo box on my table, but in the table, I can enforce the "Description" of the Report as a required field, so the combo box will display the Report Description correctly.

I was curious (I am Curious ), why would you include margins as well?

Do you just have the one standard Report that you use and just alter it's setup according to the Report parameteres in your table?

Do you use a combo box in your table to lookup the query names of database objects (i.e. to identify the recordsource for the Report) from the list of queries in the database? If this is possible, how would you do it in SQL? I know how to do it through VBA.

 

[RoyVidar]

> I was curious (I am Curious ), why would you include margins as well?

There was a bug in the a2k version, were report margins could be reset. It was supposedly fixed by an SP, but not all of my clients are up to date, therefore storing and setting margins ensure the app works in 2000-2007 regardless of how up to date their systems are (I had a request for a97 some months ago, but turned it down). But, when I said margins, it was meant as an example of what one might store.

> Do you just have the one standard Report that you use and just alter it's setup according to the Report parameteres in your table?

I'll usually have umpteen reports and subreports. I think it's interesting to differentiate between a report object found in the database window and what constitutes a report in the business. Typically, one "business report" will contain at least one, but up to three, maybe four subreports. Also, some access reports can be used for different purposes/have different business meaning.

With a setup like that, I find it much more convenient to manually create one or more tables for this, then my "rptWombats" can be used for, and have separate entries for both foo, bar, whatnots and guzunders and have names that are recognizable to the users in stead of some programming naming convention gibberish.

> Do you use a combo box in your table to lookup the query names of database objects (i.e. to identify the recordsource for the Report) from the list of queries in the database? If this is possible, how would you do it in SQL? I know how to do it through VBA.

I may use the same query, or dynamic SQL for more than one report, I may switch between umpteen different queries in one report so I have problem understanding your question due to how I use reports.

 

[Curious]

> Do you use a combo box in your table to lookup the query names of database objects (i.e. to identify the recordsource for the Report) from the list of queries in the database? If this is possible, how would you do it in SQL? I know how to do it through VBA.

I may use the same query, or dynamic SQL for more than one report, I may switch between umpteen different queries in one report so I have problem understanding your question due to how I use reports.

I think you are dealing with more complicated reports than I.

I have a Main form that allows a user to Select a Report from my list of reports (stored in a combo box that I have been dynamically generating based on a naming convention that I use for my db query objects).

Once the user selects the report, a subform control changes it's Recordsource property to the query that corresponds to the selection from the combo box. The subform then display the query results, and various summary fields are calculated to provide the user with a dynamically generated report.

I then have a "View Report" icon that will identify the current recordsource query for the Subform, lookup the corresponding Report name from my combo box, and open the report in Preview mode.

This allows the user the convience of seeing the Report output, and allow them to quickly change them, easily navigate to new reports, without having to Preview the Report each time. I also have a calendar control to futher filter results.


There is only 1 query for the Recordsource for my Report, but I understand your point that you may have multiple queries on the one report. Which is why, I need both the DB Object Query name as well as the DB Object Report name. The report makes refrences to several other queries (such as Lookup queries for Customer names, etc.), but there is only one query for the recordsource property of the report. Your suggestion for the Reports table, I thought could store all these things.

I was also excited about the propect of being able to include a lookup field in my table, for e.g. DB object Query names, that would save time typing out each query name (and possibly misspelling), and the same for report names.

I think I can do it, if I create a function, that will simply step through the name of all queries, and then call that function in another query, to create a list of values for a combo box within my table. Haven't got this working yet.

Thanks for your thoughts.