[AccessD] A2K7 Navigation Pane

Rocky,

    A convenient arrangement would be to have two command buttons named CmdHide and CmdShow on a form so as to hide or show the NavPane window in Access 2007. If the user is in a position to ensure that forms group in NavPane is never put in collapsed state, the code applicable in Access 2K & 2K3, as given below, would work in A2K7 as well.

Code in form's module - A2K7
(Forms group in NavPane must not be in collapsed state)
========================================
Private Sub CmdHide_Click()    
    ' Make NavPane the active window and hide it
    DoCmd.SelectObject acForm, , True
    DoCmd.RunCommand acCmdWindowHide
End Sub
'-------------------------------------------------------

Private Sub CmdShow_Click()
    DoCmd.SelectObject acForm, , True
End Sub
========================================

    The simple solution given above will not work if the forms group in NavPane window is in collapsed state. This is because hiding the NavPane can get implemented only if the focus stays on NavPane window during execution of acCmdWindowHide command. However when an attempt is made in Access 2007 to activate the NavPane via DoCmd.SelectObject method while the pertinent object group is in collapsed state, the focus reverts back to the active form. This results in acCmdWindowHide command taking effect on the form itself instead of the intended target (NavPane). Thus you land up with hiding the form itself, instead of the NavPane.

    Remedy lies in hiding all active forms and reports prior to hiding the NavPane, and thereafter, un-hiding the forms & reports. Of course no other object like table or query should be in open state.

    It is observed that it is not necessary to select any specific object in NavPane. This argument in DoCmd.SelectObject statement can be left blank Mere selection of a type group (acForm, acTable etc) is adequate. If the group mentioned in DoCmd.SelectObject method does not yet have any object, it will not force that group's title bar to show up in the NavPane if not already visible (otherwise it will). This however does not detract from effectiveness of proposed approach and even such an empty group serves satisfactorily as an argument to DoCmd.SelectObject method.

    Sample code in form's module, as given below, will ensure effective hiding / un-hiding of NavPane window under all situations, even if all object groups in NavPane are in collapsed state. Although this code is meant for Access 2007, it can be used in Access 2003 as well (statements meant for hiding / un-hiding of active forms / reports are however not needed in Access 2003).

    It permits unrestricted number of forms / reports to stay in open state. It is also ensured that while cycling through forms/reports for bulk hiding/ unhiding, any forms kept deliberately hidden to start with, continue in this state while other temporarily hidden objects are made visible again.

Best wishes,
A.D.Tejpal
------------

Sample universal code in form's module - Access 2007
(for hiding / un-hiding NavPane window)
'====================================
' Declarations section
' Comma separated list of form names
' already in hidden state (so that these
' do not get un-hidden along with others)
' Note - This list gets generated automatically
Private HiddenFormsList As String
'-------------------------------------------------------

Private Sub CmdHide_Click()
    ' Hide all open forms & reports so that with
    ' statement (A) focus stays on NavPane even
    ' when no object is on display (e.g. all NavPane
    ' groups are in collapsed state)
    P_HideAllOpenFormsReports
    
    ' Make NavPane the active window and hide it
    DoCmd.SelectObject acForm, , True               ' (A)
    DoCmd.RunCommand acCmdWindowHide
    
    ' Unhide all open forms and reports (other than
    ' forms deliberately kept hidden).
    ' Activate this form
    P_UnHideAllOpenFormsReports
End Sub
'-------------------------------------------------------

Private Sub CmdShow_Click()
    DoCmd.SelectObject acForm, , True
End Sub
'-------------------------------------------------------

Private Sub P_HideAllOpenFormsReports()
    On Error Resume Next
    Dim frm As Form, rpt As Report
    Dim Cnt As Long
    
    HiddenFormsList = ""        ' Default
    ' Hide all open forms
    Err.Clear
    Cnt = Forms.Count
    If Err.Number = 0 Then
        For Each frm In Forms
            ' Update list of deliberately hidden forms
            If frm.Visible = False Then
                HiddenFormsList = _
                    HiddenFormsList & "," & frm.Name
            End If
            frm.Visible = False
        Next
    End If
    
    ' Hide all open reports
    Err.Clear
    Cnt = Reports.Count
    If Err.Number = 0 Then
        For Each rpt In Reports
            rpt.Visible = False
        Next
    End If
    
    Set frm = Nothing
    Set rpt = Nothing
    On Error GoTo 0
End Sub
'-------------------------------------------------------

Private Sub P_UnHideAllOpenFormsReports()
    On Error Resume Next
    Dim frm As Form, rpt As Report
    Dim Cnt As Long
    
    ' UnHide all open forms
    Err.Clear
    Cnt = Forms.Count
    If Err.Number = 0 Then
        For Each frm In Forms
            If InStr(HiddenFormsList, frm.Name) > 0 Then
            Else
                frm.Visible = True
            End If
        Next
    End If
    
    ' UnHide all open reports
    Err.Clear
    Cnt = Reports.Count
    If Err.Number = 0 Then
        For Each rpt In Reports
            rpt.Visible = True
        Next
    End If
    
    ' Activate this form
    DoCmd.SelectObject acForm, Me.Name, False
    
    Set frm = Nothing
    Set rpt = Nothing
    On Error GoTo 0
End Sub
'====================================

  ----- Original Message ----- 
  From: Rocky Smolin at Beach Access Software 
  To: 'Access Developers discussion and problem solving' 
  Sent: Tuesday, November 20, 2007 12:50
  Subject: [AccessD] A2K7 Navigation Pane


  Dear List:
   
  A client writes to ask if the Navigation Pane (which in his screen shot is
  on the left side of the screen) can be made to disappear through code.  I
  don't have A2007 so I don't know.   I guess this 'Navigation Pane' is
  something new in A2007?
   
  Is there a line of code that will turn the pane off?  An if the user is
  running 2003 is suppose it would generate an error but I could precede the
  code to turn off the pane with On Error Resume Next?
   
  MTIA,
   
  Rocky