Tips for Serious Users

Provided by Allen Browne,  March 2009.

Keep something open

To prevent users modifying the database schema, developers normally hide the Navigation Pane/Database Window, and use a form as the interface to everything in the application. We will refer to this form as the "switchboard", whether you created it yourself or via the wizard.

When the user closes everything else, we want the switchboard to open automatically. The code below does that. Just set one property for each form and report.

Note that the code does not run when if the user closes the switchboard itself. If you try that, closing the database is a nightmare (worse in some versions than others.) Every time you close the switchboard, you open it again, so you can't get out. So, provided the switchboard is not the last thing closed, the code below will open it.

To use this in your database:

1. In your database, open the code window (Ctrl+G.)
2. Insert a new standard module (Module on the Insert menu.)
3. Paste the code below into this window.
4. Replace the word frmSwitchboard with the name of your switchboard form (about a dozen lines from the bottom.)
5. To verify that Access understands the code, choose Compile on the Debug menu.
6. Save the module with a name such as ajbKeep1Open.
7. For each form in your database (except the switchboard), set its On Close property to:
       =Keep1Open([Form])
8. For each report in your database, set its On Close property to:
       =Keep1Open([Report])

Notes:

1. Don't substitute the name of your form or report above. Literally type [Form] or [Report] including the square brackets.
2. You don't need to set the Close property for subforms or subreports.
3. If the On Close property is set to [Event Procedure], click the Build button (...) beside the property.
   Access opens the code window. In the close event, add the line:
       Call Keep1Open(Me)
4. Grab the error logger code if you wish to use that line in the error handler, rather than the MsgBox.
5. The code is not designed to distinguish multiple instances of the same form. Test the hWnd as well if you need to handle that.
6. Do NOT set the Close property for your switchboard.
7. If it bothers you that the switchboard does not reopen itself every time you close it, you could create a macro named AutoKeys, and define a hotkey. The example below opens a form named frmSwitchboard when you press F12 anywhere in the database.

The code

Option Compare Database
Option Explicit
Public Function Keep1Open(objMe As Object)
On Error GoTo Err_Keep1Open
    'Purpose:   Open the Switchboard if nothing else is visible.
    'Argument:  The object being closed.
    'Usage:     In the OnClose property of forms and reports:
    '               =Keep1Open([Form])
    '               =Keep1Open([Report])
    'Note:      Replace "Switchboard" with the name of your switchboard form.
    Dim frm As Form         'an open form.
    Dim rpt As Report       'an open report.
    Dim bFound As Boolean   'Flag not to open the switchboard.
    
    'Any other visible forms?
    If Not bFound Then
        For Each frm In Forms
            If (frm.hWnd <> objMe.hWnd) And (frm.Visible) Then
                bFound = True
                Exit For
            End If
        Next
    End If
    
    'Any other visible reports?
    If Not bFound Then
        For Each rpt In Reports
            If (rpt.hWnd <> objMe.hWnd) And (rpt.Visible) Then
                bFound = True
                Exit For
            End If
        Next
    End If
    
    'If none found, open the switchboard.
    If Not bFound Then
        DoCmd.OpenForm "Switchboard"
    End If
    
Exit_Keep1Open:
    Set frm = Nothing
    Set rpt = Nothing
    Exit Function
    
Err_Keep1Open:
    If Err.Number <> 2046& Then     'OpenForm is not available when closing database.
        'Call LogError(Err.Number, Err.Description, ".Keep1Open()")
        MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Keep1Open()"
    End If
End Function

How it works

You can use the code without learning how it works, as the only change you need to make is to substitute the name of your switchboard form.

Normally, you should use the narrowest data type you can: Form rather than Object, Textbox rather than Control, etc. This function accepts an Object, so we can use it with both forms and reports.

The Forms collection lists the open forms, so we loop through this list. As soon as we find an open form that is visible and is not ObjMe (the form being closed), we set the bFound flag to true, and skip the rest of the forms (as we know we don't need to open the switchboard.)

The form that called Keep1Open() will be in the Forms collection, but we want to ignore it and see if any other visible forms are open. You may be tempted to  use:
    If frm.Name <> obj.Name Then
But examining the Name is not good enough. It fails if:

• A form and a report have the same name, or
• Multiple instances of the same form are open (since they have the same name.)

We could have used:
    If Not frm Is objMe Then
but old versions of Access (97) do not always handle Is correctly.

The safest solution is to test the hWnd property. This is a unique number assigned by Windows so it can manage the form. Since no two windows can have the same hWnd at the same time, we completely avoid the issue of duplicate names.

If we did not find any other visible form open, we do exactly the same thing with the Reports collection, so see if any other visible report is open.

Finally, if no other visible form or report was found, we open the switchboard.

The error handler suppresses error 2046, which can  occur if it tries to open the switchboard when you are trying to close the database. (The ampersand is a type declaration character, indicating the literal 2046 is a Long.)