Sorting Nav Pane Objects by Description

Published 03 October 07 06:35 PM

I've recently had some questions from users who used to sort their DBC by the description field and wanted to be able to do the same thing in the 2007 navigation pane.  If you use this scenario, and you've tried in 2007, you'll know that the navigation pane doesn't have a sort by the description field.  One way to work around this is to create a query that does it for you. 

Here's a sample that lists the user Query objects in the database along with their descriptions.  Note because the Description property is not stored as a field in MSysObjects, you need some accompanying VBA logic to retrieve the description property from the property bag. 

First, add this function in VBA:

Public Function stDesc(stQryName As String) As String
    On Error Resume Next
    stDesc = CurrentDb.QueryDefs(stQryName).Properties("Description")
End Function

Then run this query:

SELECT MSysObjects.Name, stDesc([Name]) AS Description
FROM MSysObjects
WHERE (((MSysObjects.Name) Not Like "~*") AND ((MSysObjects.Type)=5))
ORDER BY stDesc([Name]);

by Zac Woodall
Filed under: , , , ,

Comments

# Techy News Blog » Sorting Nav Pane Objects by Description said on October 3, 2007 2:00 PM:

PingBack from http://www.artofbam.com/wordpress/?p=4916

# KiwiBruce said on October 3, 2007 5:22 PM:

Hi Zac,

So good, you can build a query to see your objects in a differant order...but then you have to say OK the one I want frmXYZ and go back to the nav pane and find it.

What this really highlights for me is that the Nav Pane is a very poor substitute for the old Database window. When are you going to bring it back? When i am flipping between say Tables and Forms it is so many more mouse clicks and drags it is crazy slow. It slows me down big time. Does anyone else find this?

Bruce

# Alan Cossey said on October 4, 2007 10:34 AM:

KiwiBruce,

Yes, I agree. The Navigation Pane may be useful for people who navigate about their database using it, but for developers it is poor. It is much more difficult to move between objects as each entry takes more space and there is only one column.

Alan Cossey

# Ryan B said on October 4, 2007 1:07 PM:

Alan,

I have to disagree with you.  I actually like the new Nav Pane.  In my nav pane i have created some custom groups.  I have all my objects sorted by their use.  every thing is organized. I know right where to go when I  am look for something.  I love it!

# Zac Woodall said on October 4, 2007 6:48 PM:

KiwiBruce and Alan, have you tried searching in the NavPane by using ctrl+F?  I've found this to be a huge time saver, no more hunting through big lists of objects like I had to do in the DBC.  Even if they're alphabetized, it still takes a while to find 1 form out of 25.  

# KiwiBruce said on October 4, 2007 7:34 PM:

Thanks Zac I will try the Find feature and see if that eases my pain.

One thing in particular that really drives me absolutely nuts is once a category is collapsed, the Nav Pane does not remember the most recently used object under each category. The DBC did this why on earth would the Nave pane not? Crazy! I work with a couple of apps that have  500+  and 800+ objects and this is not easy in the Nave Pane but I could re-size the DBC and work with hundreds of objects no problem. Anyway I have found a few DBC replacements so I think they will be the best bet for developers that need to deal with large object sets.

New Comments to this post are disabled

About Zac Woodall

Zac is a Program Manager at Microsoft on the team designing Access’s next generation platform infrastructure. He advocates easy to use designs, organizes community efforts, and is the author of The Rational Guide to Microsoft® Office Access 2007 Templates. Zac has been working at Microsoft Corporation since 1999. Before that time, he attended the University of Idaho, from which he holds a B.S. in Computer Science.
Page view tracker