Solved

Programmatically sort the objects in the Navigation Pane in Access 2010

Posted on 2011-09-20
11
1,451 Views
Last Modified: 2012-05-12
In Microsoft Access 2010, I would like to be able to programmatically sort the objects in the Navigation Pane, but I have not been able to find the correct syntax to use.  

To manually sort, the user needs to right click the Navigation Pane header and select select Sort By, then select the field they want to sort their objects by.  But if you want to sort descending, the user needs to right-click again, select Sort By,  and then select Descending.  I want to create a macro, or use some VBA that I can associate with a button, so I can sort descending by last modified date with one click, rather than two right-clicks and four menu selections.  I do this sort all day, always looking for the most recent queries I have been working on.
0
Comment
Question by:upsfa
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
11 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36569795
Not sure if I have ever seen this done (even in Access 2003 and older)

So you are giving your users complete and direct access to all Database objects instead of controlling this through forms...?


You can certainly create your own sort form quite easily.

Take the form from here and drop it into your database
(I am sure you will be able to easliy adapt it for different sort field options)
Database41.mdb
0
 
LVL 1

Author Comment

by:upsfa
ID: 36569896
Thanks, not what I'm looking for.   I already have a couple of forms that replicate the old database container.  Here's a good one.
SortByDesc.zip
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36570427
Again, I am not aware of a direct way that this can be done via VBA.
But perhaps thee is, so let's see what other experts post

And again, is this just for you as the developer?
Most Access Applications are designed so that direct access to the database window/ Task Pane is not needed.

0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 1

Author Comment

by:upsfa
ID: 36576168
This is not for end-users, just me a couple of my people.  Actually, I would put this in my blank.accdb template so it is available in any database I create.

Here's where I am going now.  To get to the sort options manually, you can click on the Navigation Pane header, and invoke the context menu by right-clicking.  Alternatively, if the header has the focus, you can use shift-F10 to invoke the menu.  So, I’m thinking sendkeys, although I try to avoid this.  In a macro, I have used the Navigate To function to take me to Tables, then I use shift-tab, four times to move the focus to the header, then use shift-f10, but it does not work.  When I run the macro up to the forth shift-tab line, then manually press shift-F10, it works.  If I manually place the focus on the Navigation Pane header, then run a simple macro that only does the shift-F10 sendkeys command that works, but when I put them together, they it does not work.

Ok, while I was typing that it gave me an idea – create one macro that gives the header the focus, then call another macro that does the shift-F10 part.  It works!

The three macros are attached
Option Compare Database

'------------------------------------------------------------
' SortObjects - This will be run first, it will navigate to the tables section
' of the navigation pane, then will tab up to the navigation pane header, then
' will call the macro that will excute the shift-F10 command to invoke the context menu
'------------------------------------------------------------
Function SortObjects()
On Error GoTo SortObjects_Err

    DoCmd.NavigateTo "acNavigationCategoryObjectType", "acNavigationGroupTables"
    SendKeys "+{Tab}", True
    SendKeys "+{Tab}", True
    SendKeys "+{Tab}", True
    SendKeys "+{Tab}", True
    DoCmd.RunMacro "SortByModDescending", , ""

SortObjects_Exit:
    Exit Function

SortObjects_Err:
    MsgBox Error$
    Resume SortObjects_Exit
End Function
Option Compare Database

'------------------------------------------------------------
' SortByModDescending - This opens the context menu, then selects sort by last modified date
' then opens the menu again and selects Descending, then NavigateTo is used to send the
' focus to the queries section of the navigation pane, then sendkeys is used to give the
' header focus again.  The ShowAllGroups macro is then called to show all object types.
'
'------------------------------------------------------------
Function SortByModDescending()
On Error GoTo SortByModDescending_Err

    SendKeys "+{f10}", False
    SendKeys "{down}", False
    SendKeys "{down}", False
    SendKeys "{right}", False
    SendKeys "{down}", False
    SendKeys "{down}", False
    SendKeys "{down}", False
    SendKeys "{down}", False
    SendKeys "{down}", False
    SendKeys "{enter}", False
    SendKeys "+{F10}", False
    SendKeys "{down}", False
    SendKeys "{down}", False
    SendKeys "{right}", False
    SendKeys "{down}", False
    SendKeys "{enter}", False
    DoCmd.NavigateTo "acNavigationCategoryObjectType", "acNavigationGroupQueries"
    SendKeys "+{tab}", False
    SendKeys "+{tab}", False
    SendKeys "+{tab}", False
    DoCmd.RunMacro "ShowAllGroups", , ""


SortByModDescending_Exit:
    Exit Function

SortByModDescending_Err:
    MsgBox Error$
    Resume SortByModDescending_Exit

End Function


Option Compare Database

'------------------------------------------------------------
' ShowAllGroups - This invokes the context menu and selects Show All Groups
'
'------------------------------------------------------------
Function ShowAllGroups()
On Error GoTo ShowAllGroups_Err

    SendKeys "+{f10}", False
    SendKeys "{down}", False
    SendKeys "{down}", False
    SendKeys "{down}", False
    SendKeys "{down}", False
    SendKeys "{enter}", False

ShowAllGroups_Exit:
    Exit Function

ShowAllGroups_Err:
    MsgBox Error$
    Resume ShowAllGroups_Exit

End Function

Open in new window

0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36576504
OK
Great,
If this is what works, then you can accept your own post as the solution.


Just a note here.
MS is always threatening to Deprecate SendKeys.
So be prepared for the day when this becomes a reality...

Jeff
0
 
LVL 1

Author Comment

by:upsfa
ID: 37076997
It only works when I select the macro manually.  If I add a button to the QAT and use that to run the macro the header does not get the focus (regardeless of how many times I shift-tab).  Really want this to work from the QAT
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37077242
<Really want this to work from the QAT>

Then you may have an uphill battle here.

Again, if it were me, I would just create a standalone form that does this...
0
 
LVL 1

Accepted Solution

by:
upsfa earned 0 total points
ID: 37083474
Victory is mine!

Option Compare Database

'------------------------------------------------------------
' This will sort objects in the database window descending
' by Modified date.
'
' Add this into a module in Access, then
' add an icon for this macro to the Quick Access Tool Bar
' and you will be able to perform the sort with one click so
' you can sort descending by last modified date with one click,
' rather than two right-clicks and four menu selections.
'
'  *  This will not work if your cursor is in a field on
'     a table or query, since CTRL F will then bring up the
'     Find dialog box. Just click on any object in the
'     Navigation Pane, before invoking this code.
'------------------------------------------------------------

Function SortObjects()
On Error GoTo SortObjects_Err

    SendKeys "^f", False
    SendKeys "+{tab}", False
    SendKeys "+{tab}", False
    SendKeys "+{f10}", False
    SendKeys "s", False
    SendKeys "m", False
    SendKeys "{enter}", False
    SendKeys "s", False
    SendKeys "c", False
    SendKeys "{enter}", True
    SendKeys "^f", True
    SendKeys "+{tab}", False
    SendKeys "+{tab}", False
    SendKeys "+{f10}", False
    SendKeys "a", False


SortObjects_Exit:
    Exit Function

SortObjects_Err:
    MsgBox Error$
    Resume SortObjects_Exit

End Function

Open in new window

0
 
LVL 1

Author Closing Comment

by:upsfa
ID: 37105834
"No body cares more about your problems then you"
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37085058
Congratulations!

Just keep in mind my comments about MS continual threatening to deprecating Send keys...
0
 
LVL 75
ID: 37085196
You may find issues with SendKeys in Win 7 ...

mx
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

687 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question