Provided by Allen Browne, March 2009.
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.
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
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:
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.)
|Home||Index of tips||Top|