Today's guest blogger is Pavlo Pedan of ARGO Business Corp. He has 15 years of experience with Access and has a great site of tips  at http://sites.google.com/site/msaccesscode/.

When you open a form or report in Design View, the Property Sheet is either displayed or hidden based on its state the last time you left Design view. However, you might want to ensure that the Property Sheet is always displayed (or hidden) when you switch to Design view. You might think to use the following command:

DoCmd.RunCommand acCmdProperties

Unfortunately, this command only works after the form or report is already open in Design View. To ensure that the Property Sheet is always displayed or hidden when you switch to Design view, use the function below. The function can be executed while starting an application (for example in the AutoExec macro), attached to the Close event of the form or report, or whenever necessary.

For example, you might use the function to suppress the blinking of the Property Sheet when creating a form at a run-time using the Application.CreateForm method and opening it in a Form view.

Here's the code, which has been tested in Access 2007.

'----------------------------------------------------------------
' Module    : modSwitchPropertySheet
' Author    : Pavlo Pedan
' Date      : 31/07/2009
' Purpose   : DoCmd.RunCommand acCmdProperties only toggles Property Sheet in
'           : Form/Report Design View on/off. Use function below to turn it
'           : on or off
'----------------------------------------------------------------

Option Compare Database

Private Declare Function FindWindowEx Lib "User32" Alias "FindWindowExA" _
    (ByVal hwndParent As Long, ByVal hwndChildAfter As Long, _
     ByVal lpszClass As String, ByVal lpszWindow As String) As Long

Private Declare Function GetWindowRect Lib "User32" (ByVal hWnd As Long, _
    lpRect As RECT) As Long

Private Type RECT
    Left As Long
    Top As Long
    Right As Long
    Bottom As Long
End Type

Function fSwitchPropertySheet(blnON As Boolean) As Boolean
'----------------------------------------------------------------
' Procedure : fSwitchPropertySheet
' Author    : Pavlo Pedan
' Date      : 31/07/2009
' Purpose   : switch Property Sheet in Design View (blnON = False to turn off)
'----------------------------------------------------------------

    Dim frm As Form
    Dim hWnd As Long
    Dim rectControl As RECT

    ' create temp form
    Set frm = CreateForm

    ' find handle for Property Sheet window
    hWnd = FindWindowEx(Application.hWndAccessApp, 0, "MsoCommandBarDock", _
        "MsoDockRight")
    If hWnd > 0 Then

        ' get coordinates of the window
        GetWindowRect hWnd, rectControl
        ' determine action
        If blnON Then
            ' if window width is 0, switch it ON
            If rectControl.Left = rectControl.Right Then
                DoCmd.RunCommand acCmdProperties

                'returns True if action made
                fSwitchPropertySheet = True
            End If
        Else
            ' if window width is not 0, switch it OFF
            If rectControl.Left <> rectControl.Right Then
                DoCmd.RunCommand acCmdProperties

                'returns True if action made
                fSwitchPropertySheet = True
            End If
        End If

    End If

    ' close temp form without saving
    DoCmd.Close , , acSaveNo

End Function
 
Send your Power Tips to Mike and Chris at accpower@microsoft.com.