Example Apps for Businesses, Schools & Developers

Screenshots

Click any image to view a larger version

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

DatabaseWindow2003List

Large Icons View

DatabaseWindow2003LargeIcons

Details View

DatabaseWindow2003Details

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

NavPane2007List

Large Icons View

NavPane2007Icons

Details View

NavPane2007Details

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

NavPaneHelper1

Icon View by Tables & Related Views; Sort by Type Descending

NavPaneHelper2

Details View by Modified Date; Sort by Modified Date Ascending


NavPaneHelper3

Details View by Object Type (Tables Only); Show System Objects;
Sort by Modified Date Ascending; Hide Search Bar

NavPaneHelper4

List View by Object Type; Collapse All Groups

NavPaneHelper5

List View by Custom Groups; Show Hidden & System Objects; Sort by Type Descending;

NavPaneHelper6

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