Prev: tracking changes
Next: Access
From: Laura1 via AccessMonster.com on 3 Jul 2008 09:34
Hi everyone I found this repsonse to a questions of how to get a listing of
all the qry's in a database this worked great but it only gives a name
listing as requested by the other user. I can't see to figure out how to get
the description too. ANY thoughts??? Much appreaciated!



On the main database folder, select Reports.
Click on New.
A dialog box will open. Select Design View.
Do NOT enter anything in the Choose the table or query box.

Click OK

When the report in design view opens, right-click on the little square
where the upper and left side ruler's meet (or click View + Properties
from the Menu tool bar).
Select Properties.
Click on the Data tab.
The first line is the report's record source line.
Directly on that line write (all on one line):

SELECT MSysObjects.Name FROM MSysObjects WHERE
(((Left([Name],1))<>"~") AND ((MSysObjects.Type)=5)) ORDER BY
MSysObjects.Name;

Close the properties box.

Then click on the field list tool button (it looks like a piece of
paper with writing on it).
Select and then drag the Name field onto the report detail section.
You can delete the label that is attached to the text control.
Save and run the report.

--
Message posted via http://www.accessmonster.com

From: Allen Browne on 3 Jul 2008 09:51
You can't get the Description from MSysObjects.

You need to loop through the QueryDefs collection, and test
Properties("Description").

But if a query has no Description problem, one of 2 things can happen:
a) You may get error 3270, saying the Description property does not exist,
or

b) Access may lie to you, and give you the Description of one of the tables
in the query, even though the query has no Description.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Laura1 via AccessMonster.com" <u31091(a)uwe> wrote in message
news:8698bb8f11e13(a)uwe...
> Hi everyone I found this repsonse to a questions of how to get a listing
> of
> all the qry's in a database this worked great but it only gives a name
> listing as requested by the other user. I can't see to figure out how to
> get
> the description too. ANY thoughts??? Much appreaciated!
>
>
>
> On the main database folder, select Reports.
> Click on New.
> A dialog box will open. Select Design View.
> Do NOT enter anything in the Choose the table or query box.
>
> Click OK
>
> When the report in design view opens, right-click on the little square
> where the upper and left side ruler's meet (or click View + Properties
> from the Menu tool bar).
> Select Properties.
> Click on the Data tab.
> The first line is the report's record source line.
> Directly on that line write (all on one line):
>
> SELECT MSysObjects.Name FROM MSysObjects WHERE
> (((Left([Name],1))<>"~") AND ((MSysObjects.Type)=5)) ORDER BY
> MSysObjects.Name;
>
> Close the properties box.
>
> Then click on the field list tool button (it looks like a piece of
> paper with writing on it).
> Select and then drag the Name field onto the report detail section.
> You can delete the label that is attached to the text control.
> Save and run the report.

From: Laura1 via AccessMonster.com on 3 Jul 2008 11:23
Allen:

Thanks for your help but I am not familar at all with the QueryDefs
Collection where do I find that????



Allen Browne wrote:
>You can't get the Description from MSysObjects.
>
>You need to loop through the QueryDefs collection, and test
>Properties("Description").
>
>But if a query has no Description problem, one of 2 things can happen:
>a) You may get error 3270, saying the Description property does not exist,
>or
>
>b) Access may lie to you, and give you the Description of one of the tables
>in the query, even though the query has no Description.
>
>> Hi everyone I found this repsonse to a questions of how to get a listing
>> of
>[quoted text clipped - 29 lines]
>> You can delete the label that is attached to the text control.
>> Save and run the report.

--
Message posted via http://www.accessmonster.com

From: Allen Browne on 3 Jul 2008 11:55
Here's an example of looping the QueryDefs collection:

Public Function ShowAllQueries()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef

Set db = CurrentDb()
For Each qdf In db.QueryDefs
Debug.Print qdf.Name, qdf.sql
Next
End Function

Reading the Properties("Description") of each is similar to how you do it
with TableDefs. Example here:
http://allenbrowne.com/func-06.html
The GetDescrip() is what you need there, but the example will probably be
useful.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Laura1 via AccessMonster.com" <u31091(a)uwe> wrote in message
news:8699ae738126a(a)uwe...
> Allen:
>
> Thanks for your help but I am not familar at all with the QueryDefs
> Collection where do I find that????
>
>
>
> Allen Browne wrote:
>>You can't get the Description from MSysObjects.
>>
>>You need to loop through the QueryDefs collection, and test
>>Properties("Description").
>>
>>But if a query has no Description problem, one of 2 things can happen:
>>a) You may get error 3270, saying the Description property does not exist,
>>or
>>
>>b) Access may lie to you, and give you the Description of one of the
>>tables
>>in the query, even though the query has no Description.
>>
>>> Hi everyone I found this repsonse to a questions of how to get a listing
>>> of
>>[quoted text clipped - 29 lines]
>>> You can delete the label that is attached to the text control.
>>> Save and run the report.
>
> --
> Message posted via http://www.accessmonster.com
>

 | 
Pages: 1
Prev: tracking changes
Next: Access