Rocky Smolin at Beach Access Software
rockysmolin at bchacc.com
Wed Nov 21 00:57:41 CST 2007
A.D.:
Thank you as usual for this solution. I have to get A2007 I think to see
for myself what you are referring to by the collapsed state.
What would the effect of the code be in A2003, though since there is no
navigation pane?
Regards,
Rocky
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of A.D.TEJPAL
Sent: Tuesday, November 20, 2007 9:26 PM
To: Access Developers discussion and problem solving
Cc: A.D.TEJPAL
Subject: Re: [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
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.5.503 / Virus Database: 269.16.2/1142 - Release Date: 11/20/2007
5:44 PM