Version 2.3 First Published 19 Aug 2022 Approx 0.8 MB (zipped)
When Access 2007 was launched, the application interface was significantly changed including the new ribbon.
Another of the main visual differences was a new navigation pane to replace the old database window
Access 2003 Database Window
List View
|
Large Icons View
|
Details View
|
The database window provided many options but only one type of object could be viewed at a time
By contrast the navigation pane allowed all object types to be viewed simultaneously and added a very useful search bar
Access 2007 Navigation Pane
List View
|
Large Icons View
|
Details View
|
At first, many Access users found the new navigation pane a challenge to work with as it was so different to the old database window
Over time, many of us came to prefer the navigation pane for the wider range of options it provided for viewing objects - including displaying all database object types at once.
The search bar is particularly useful for finding selected objects in a large database containing many objects
However, even 15 years on, some experienced Access developers continue to dislike the navigation pane with some vocal critics such as Pat Hartman at
Access World Forums regularly referring to it as the 'nav pain (sic)' mainly because it takes several clicks to achieve certain display options.
This helper app was created to simplify the various display options so that each is available with as few clicks as possible - usually just one!
A small form gives access to almost all the available options. For example:
List View by Object Type; Sort By Name Ascending
|
Icon View by Tables & Related Views; Sort by Type Descending
|
Details View by Modified Date; Sort by Modified Date Ascending
|
Details View by Object Type (Tables Only); Show System Objects; Sort by Modified Date Ascending; Hide Search Bar 
|
List View by Object Type; Collapse All Groups
|
List View by Custom Groups; Show Hidden & System Objects;
Sort by Type Descending;
|
Other options are possible including locking / minimizing / maximizing or completely hiding the navigation pane
Most of the code used is fairly simple. The challenge was finding code that worked for each item.
There is VERY little documentation online related to coding the navigation pane options programmatically.
Some of the code needed was very obscure, In some cases, there were no direct commands other than the use of command bars
All options are saved in the table tlbNavPaneSettings and implemented the next time the form is opened
To use this in your own applications, import the following objects:
• Form frmNavPaneHelper - optionally make it the startup form
• Table tblNavPaneSettings
• Module modFunctions
NOTE:
1. For best results, change settings entirely using the Helper form or using the navigation pane itself
Mixing methods may lead to the form settings being inaccurate.
Resynchronise if necessary by clicking form buttons to match the navigation pane options in use.
2. To edit any code based on command bars, the VBE reference Microsoft Office xx.0 Library MUST be added to your project
3. There are currently two bugs related to using the navigation pane, both of which have been reported to Microsoft within the last month or so.
Fixes for these two bugs will hopefully be provided within the next few weeks:
a) The Sort Descending option is 'lost' when an Access app is restarted.
The code in the helper form app fixes that issue when the form is loaded
b) The command designed to show/hide the navigation pane search bar worked in Access 2007 but not in Access 365.
Application.SetOption "Show Navigation Pane Search Bar", True 'False
However, following a suggestion by Shane Groff from the Access development team, I was able to find another method using command bars which does toggle the search bar visible/hidden
Application.CommandBars("Navigation Pane Pop-up").Controls(7).Execute
For convenience, much of the form code is listed below
CODE: to update Category view - using DoCmd.NavigateTo code
Private Sub fraCategory_AfterUpdate()
Select Case fraCategory
'NOTE: can add specified group as argument
Case 1 'object type
strValue = "Object Type"
DoCmd.NavigateTo "acNavigationCategoryObjectType"
Case 2 'tables & views
strValue = "Tables and Related Views"
DoCmd.NavigateTo "acNavigationCategoryTablesAndViews"
Case 3 'modified date
strValue = "Modified Date"
DoCmd.NavigateTo "acNavigationCategoryModifiedDate"
Case 4 'created date
strValue = "Created Date"
DoCmd.NavigateTo "acNavigationCategoryCreatedDate"
Case 5 'custom
strValue = "Custom"
DoCmd.NavigateTo "Custom"
End Select
'save setting to table
strName = "Category"
intValue = fraCategory
CurrentDb.Execute "UPDATE tblNavPaneSettings
SET tblNavPaneSettings.ItemDetail = '" & strValue & "', tblNavPaneSettings.ItemValue = " & intValue & "" & _
WHERE (((tblNavPaneSettings.ItemName)='" & strName & "'));", dbFailOnError
End Sub
CODE: to expand/collapse Groups - using command bars code
Private Sub fraGroup_AfterUpdate()
Select Case fraGroup
Case 1 'expand all
strValue = "Expand All"
Application.CommandBars("Navigation Pane Group Header Pop-up").Controls(8).Execute
Case 2 'collapse all
strValue = "Collapse All"
Application.CommandBars("Navigation Pane Group Header Pop-up").Controls(9).Execute
End Select
fraGroup = 0 ''reset so neither button is active
'save setting to table
strName = "Group"
intValue = fraGroup
CurrentDb.Execute "UPDATE tblNavPaneSettings
SET tblNavPaneSettings.ItemDetail = '" & strValue & "', tblNavPaneSettings.ItemValue = " & intValue & "" & _
WHERE (((tblNavPaneSettings.ItemName)='" & strName & "'));", dbFailOnError
End Sub
CODE: to update the SortOrder - again using command bars code
Private Sub fraSortOrder_AfterUpdate()
Select Case fraSortOrder
Case 1 'ascending
strValue = "Ascending
Application.CommandBars("Navigation Pane SortBy Pop-up").Controls(1).Execute
Case 2 'descending
strValue = "Descending"
Application.CommandBars("Navigation Pane SortBy Pop-up").Controls(2).Execute
End Select
'save setting to table
strName = "SortOrder"
intValue = fraSortOrder
CurrentDb.Execute "UPDATE tblNavPaneSettings
SET tblNavPaneSettings.ItemDetail = '" & strValue & "', tblNavPaneSettings.ItemValue = " & intValue & "" & _
WHERE (((tblNavPaneSettings.ItemName)='" & strName & "'));", dbFailOnError
End Sub
CODE: to update the Sort By view - yet again using command bars code
Private Sub fraSortBy_AfterUpdate()
Select Case fraSortBy
Case 1 'name
strValue = "Name"
Application.CommandBars("Navigation Pane SortBy Pop-up").Controls(3).Execute
Case 2 'type
strValue = "Type"
Application.CommandBars("Navigation Pane SortBy Pop-up").Controls(4).Execute
Case 3 'modified date
strValue = "Modified Date"
Application.CommandBars("Navigation Pane SortBy Pop-up").Controls(5).Execute
Case 4 'created date
strValue = "Created Date"
Application.CommandBars("Navigation Pane SortBy Pop-up").Controls(6).Execute
Case 5 'remove automatic sorts
strValue = "Remove Automatic Sorts"
Application.CommandBars("Navigation Pane SortBy Pop-up").Controls(7).Execute
End Select
'save setting to table
strName = "SortBy"
intValue = fraSortBy
CurrentDb.Execute "UPDATE tblNavPaneSettings
SET tblNavPaneSettings.ItemDetail = '" & strValue & "', tblNavPaneSettings.ItemValue = " & intValue & "" & _
WHERE (((tblNavPaneSettings.ItemName)='" & strName & "'));", dbFailOnError
End Sub
CODE: to update the Object Type view - using DoCmd.RunCommand acCmdView(ObjectType)
Private Sub fraViewObj_AfterUpdate()
'set focus to the Object Type category in the navigation pane
DoCmd.NavigateTo "acNavigationCategoryObjectType"
'reset the Category option to Object Type
Me.fraCategory = 1
Select Case fraViewObj
Case 1 'all objects
strValue = "All objects"
'no code needed here - default
Case 2 'tables
strValue = "Tables"
DoCmd.RunCommand acCmdViewTables
Case 3 'queries
strValue = "Queries"
DoCmd.RunCommand acCmdViewQueries
Case 4 'forms
strValue = "Forms"
DoCmd.RunCommand acCmdViewForms
Case 5 'reports
strValue = "Reports"
DoCmd.RunCommand acCmdViewReports
Case 6 'macros
strValue = "Macros"
DoCmd.RunCommand acCmdViewMacros
Case 7 'modules
strValue = "Modules"
DoCmd.RunCommand acCmdViewModules
End Select
'save setting to table
strName = "ViewObjects"
intValue = fraViewObj
CurrentDb.Execute "UPDATE tblNavPaneSettings
SET tblNavPaneSettings.ItemDetail = '" & strValue & "', tblNavPaneSettings.ItemValue = " & intValue & "" & _
WHERE (((tblNavPaneSettings.ItemName)='" & strName & "'));", dbFailOnError
End Sub
CODE: to update the View By settings - using DoCmd.RunCommand acCmdView(ViewByType)
Private Sub fraViewBy_AfterUpdate()
'set focus to the Object Type category in the navigation pane
DoCmd.NavigateTo "acNavigationCategoryObjectType"
Select Case fraViewBy
Case 1 'details
strValue = "Details"
DoCmd.RunCommand acCmdViewDetails
Case 2 'icon
strValue = "Icon"
DoCmd.RunCommand acCmdViewLargeIcons
Case 3 'lists
strValue = "List"
DoCmd.RunCommand acCmdViewList
End Select
'save setting to table
strName = "ViewBy"
intValue = fraViewBy
CurrentDb.Execute "UPDATE tblNavPaneSettings
SET tblNavPaneSettings.ItemDetail = '" & strValue & "', tblNavPaneSettings.ItemValue = " & intValue & "" & _
WHERE (((tblNavPaneSettings.ItemName)='" & strName & "'));", dbFailOnError
End Sub
CODE: to update the navigation pane width - using DoCmd.NavigateTo code
Private Sub fraWidth_AfterUpdate()
Select Case fraWidth
Case 1 'hide
strValue = "Hide"
DoCmd.NavigateTo "acNavigationCategoryObjectType"
DoCmd.RunCommand acCmdWindowHide
Case 2 'minimize
strValue = "Minimize"
DoCmd.NavigateTo "acNavigationCategoryObjectType"
DoCmd.Minimize
Case 3 'maximize
strValue = "Maximize"
DoCmd.RunCommand acCmdViewList
End Select
'save setting to table
strName = "Width"
intValue = fraWidth
CurrentDb.Execute "UPDATE tblNavPaneSettings
SET tblNavPaneSettings.ItemDetail = '" & strValue & "', tblNavPaneSettings.ItemValue = " & intValue & "" & _
WHERE (((tblNavPaneSettings.ItemName)='" & strName & "'));", dbFailOnError
End Sub
CODE: to update the ShowHiddenObjects setting - using Application.SetOption code
Private Sub OptHidden_AfterUpdate()
Select Case OptHidden
Case 1 'show
strValue = "Show"
Application.SetOption "Show Hidden Objects", True
Case 2 'hide
strValue = "Hide"
Application.SetOption "Show Hidden Objects", False
End Select
'save setting to table
strName = "HiddenObjects"
intValue = OptHidden
CurrentDb.Execute "UPDATE tblNavPaneSettings
SET tblNavPaneSettings.ItemDetail = '" & strValue & "', tblNavPaneSettings.ItemValue = " & intValue & "" & _
WHERE (((tblNavPaneSettings.ItemName)='" & strName & "'));", dbFailOnError
End Sub
CODE: to update the ShowSystemObjects setting - using Application.SetOption code
Private Sub OptSystem_AfterUpdate()
Select Case OptSystem
Case 1 'show
strValue = "Show"
Application.SetOption "Show System Objects", True
Case 2 'hide
strValue = "Hide"
Application.SetOption "Show System Objects", False
End Select
'save setting to table
strName = "SystemObjects"
intValue = OptSystem
CurrentDb.Execute "UPDATE tblNavPaneSettings
SET tblNavPaneSettings.ItemDetail = '" & strValue & "', tblNavPaneSettings.ItemValue = " & intValue & "" & _
WHERE (((tblNavPaneSettings.ItemName)='" & strName & "'));", dbFailOnError
End Sub
CODE: to update the Lock Navigation Pane setting - using DoCmd.LockNavigationPane code
Private Sub OptLock_AfterUpdate()
Select Case OptLock
Case 1 'lock
strValue = "Lock"
DoCmd.LockNavigationPane True
Case 2 'unlock
strValue = "Unlock"
DoCmd.LockNavigationPane False
End Select
'save setting to table
strName = "LockNavigationPane"
intValue = OptLock
CurrentDb.Execute "UPDATE tblNavPaneSettings
SET tblNavPaneSettings.ItemDetail = '" & strValue & "', tblNavPaneSettings.ItemValue = " & intValue & "" & _
WHERE (((tblNavPaneSettings.ItemName)='" & strName & "'));", dbFailOnError
End Sub
CODE: to update the Show Search Bar setting - using command bars code
Private Sub OptSearch_AfterUpdate()
'this toggles the search bar visible/hidden
Application.CommandBars("Navigation Pane Pop-up").Controls(7).Execute
'save setting to table
If Nz(DLookup("ItemValue", "tblNavPaneSettings", "ItemName = 'SearchBar'"), 0) = 0 Then
intValue = -1
strValue = "Show"
Else
intValue = 0
strValue = "Hide"
End If
CurrentDb.Execute "UPDATE tblNavPaneSettings
SET tblNavPaneSettings.ItemDetail = '" & strValue & "', tblNavPaneSettings.ItemValue = " & intValue & "" & _
WHERE (((tblNavPaneSettings.ItemName)='" & strName & "'));", dbFailOnError
End Sub
Version History:
Version 2.2 : 15 Aug 2022 - initial release at Access World Forums
Version 2.3 : 19 Aug 2022 - added Sort By, Sort Order, Group & Search Bar items using command bars code
Version 2.4 : 21 Aug 2022 - this is now also available as an Access Add-in . See Navigation Pane Helper Add-In
Video:
I have created a short video demonstrating how this works
The video is available on my YouTube channel at https://youtu.be/bdTB7HxcTyE or you can click below:
Download:
Click to download: Navigation Pane Helper v2.3 (zipped)
Acknowledgements & Useful links
1. Thanks to Shane Groff from the MS Access team for alerting me to the use of command bars for the Sort Order code.
This led me to check other command bar code for use in both this and other apps.
2. Code to list navigation pane command bars by Daniel Pineault:
List Application Command Bars
3. Three excellent articles on command bars by ex-MVP Dale Fye :
Part 1 - Understanding and using CommandBars (ShortCut menus)
Part 2 - Creating Your Own CommandBars
Part 3 - Using Built-in Shortcut Menus
Dale also has a very useful Access Shortcut (right-click) Tool
4. Access Dev Tools also have a commercial utility CommandBar Editor
Please use the feedback form below to email me with any comments on this utility.
Colin Riddington Mendip Data Systems Last Updated 19 Aug 2022
|
Return to Example Databases Page
|
Return to Top
|