Hiding Nav Pane from a Control on a form - Hides the Form Instead?

[Dal Jeanis]

I'm migrating a 2003 database to 2010, and trying to lock down ribbons and nav pane on MS Access 2010, but also making it easy for my programmers to open them up again without a restart. The active code to hide the Nav Pane, found various places on the internet, looks like this:

       DoCmd.NavigateTo "acNavigationCategoryObjectType"
       DoCmd.RunCommand acCmdWindowHide

When I put that code in a called global module, it worked for a while...

Then I put it behind a checkbox on a Programmer Options form, and the code instead somehow hides the form the checkbox is on.

And, after experimenting a little while, I haven't been able to find again the method that worked to hide the nav pane.

I've read this page about NavigateTo http://msdn.microsoft.com/en-us/libr.../ff191916.aspx and it seems like it's doing something pretty general to affect the Nav Pane itself, which makes the Nav Pane current, so the RunCommand affects the Nav Pane.

Can someone please explain under what circumstances that code will work? Or, alternatively, under what circumstances that code will fail? Or, alternatively, exactly what NavigateTo is accomplishing, and for what reasons it might or might not work in the contexts of control Event code, Form Event Code, or global function code?

[Dal Jeanis]

I found it, but I don't like it.

It looks like, if all tabs of the nav pane are collapsed, then the code doesn't work, whether you are using the "DoCmd.NavigateTo acNavigationCategoryObjectType " version or the "DoCmd.SelectObject acTable, , True " version.

As long as one or more of the categories is uncollapsed, the nav pane will allow itself to be hidden with those version of the code.

Which means that, to be certain I'm closing it, I have to mess with the nav pane view, to make sure a category is uncollapsed.

[Dal Jeanis]

'The following SelectObject works in Access 2007 or prior, but in 2007 only if the Navigation Pane category
'is set to ObjectType and the Tables group is EXPANDED; otherwise the Pane does not get the focus.
'If no object is visible at all, then the Pane automatically gets the focus anyway, so expanded status does not matter.
DoCmd.SelectObject acTable, vbNullString, True

I found that note on this page about testing whether the Nav Pane is Open or not -
http://www.tech-archive.net/Archive/.../msg00290.html

[Dal Jeanis]

All right. I put this code behind a checkbox on my frm_ProgrammerOptions form. When the box is checked, it will show the Nav Pane. When the box is unchecked, it will hide the nav pane.

This code prefers to use the two lines of code here -

  DoCmd.NavigateTo "acNavigationCategoryObjectType"
  DoCmd.RunCommand acCmdWindowHide

However, those lines fail if, in the nav pane's Group By Object Type View, all of the categories are collapsed. It can also fail, apparently, if the form that's causing the code to execute is modal.

So, I use the first of the above lines to attempt to invoke the nav pane.

If that works, as WE HOPE, then Screen.ActiveForm will throw an error 2475, because there isn't an active form. I set a flag that we do NOT have a form, which allows me to skip the grunt work of hiding and demodaling the visible forms.

If I did grab a form, then I loop through the forms collection, hiding and demodaling all forms and saving their prior state.

I execute the second of the above lines, to hide the nav form.

Lastly, if I did grab a form, then I loop through the forms collection, restoring the prior visible and modal properties.

Private Sub chkShowNavPane_Click()
  On Error GoTo Err_chkShowNavPane_Click
     
   Dim bolGotForm As Boolean
   Dim retJunk
   Dim frm As Form
   Dim intI As Integer
   Dim intForms As Integer
   Dim bolFrmVisible(99) As Boolean
   Dim bolFrmModal(99) As Boolean

    If chkShowNavPane Then
       DoCmd.SelectObject acTable, "MSysObjects", True
       DoCmd.Minimize
       GoTo Exit_chkShowNavPane_Click
    End If
   
' We are trying to Hide the Nav Pane
     bolGotForm = True
     DoCmd.NavigateTo "acNavigationCategoryObjectType"
     
     ' if the nav form has the focus, this reference will throw error 2475
     ' and set bolGotForm to False
     ' that way, we avoid having to set all the forms invisible and nonmodal
     retJunk = Screen.ActiveForm.Name
 
     ' If we didn't get the nav form, then hide all the visible forms
     ' and set them to not modal
     If (bolGotForm) Then
         intForms = Forms.Count
         If intForms > 0 Then
            For intI = 0 To intForms - 1
               Set frm = Forms(intI)
               bolFrmVisible(intI) = frm.Visible
               frm.Visible = False
               bolFrmModal(intI) = frm.Modal
               frm.Modal = False
            Next intI
         End If
 
     End If
 
     ' hid the nav pane
     DoCmd.RunCommand acCmdWindowHide

    'restore all the visible forms
     If (bolGotForm) Then
         intForms = Forms.Count
         If intForms > 0 Then
            For intI = 0 To intForms - 1
               Set frm = Forms(intI)
               frm.Visible = bolFrmVisible(intI)
               frm.Modal = bolFrmModal(intI)
            Next intI
         End If
     End If
      
' backstop - if all else fails, make programmer options form visible
'     If Forms![frm_ProgrammerOptions].Visible = False Then
'        MsgBox "Sorry - At least one category in the nav pane must be expanded to hide the nav pane"
'        Forms![frm_ProgrammerOptions].Visible = True
'     End If
   
   
Exit_chkShowNavPane_Click:
    Exit Sub
    
Err_chkShowNavPane_Click:
    
    If Err.Number = 2475 Then
        bolGotForm = False
        Resume Next
    End If
    
    MsgBox Err.Description
    Resume Exit_chkShowNavPane_Click
    
End Sub

This code has been tested in Access 2010 under most reasonable circumstances. It NOT been tested with the new "Navigation Forms", so I'd appreciate anyone who uses the things to pop this code behind a checkbox on a new form, open the nav pane in Group By Object Type mode and make sure that all Groups are collapsed, then click the checkbox on and off, and report back if the code works as expected under those circumstances.

WARNING: The above code has an arbitrary limit of 99 loaded forms. If you expect more forms than that to be loaded at one time, then change the lines that read.

   Dim bolFrmVisible(99) As Boolean
   Dim bolFrmModal(99) As Boolean

For reference and comparison, here's the code behind my ribbon checkbox.

Private Sub chkShowRibbons_Click()
   
   If chkShowRibbons Then
       DoCmd.ShowToolbar "Ribbon", acToolbarYes
   Else
       DoCmd.ShowToolbar "Ribbon", acToolbarNo
   End If
End Sub