closing all forms except main switchboard

[Guest]

My user needs me to provide a command button on every form that will close
all forms and return him to the main switchboard form. How do I do this? I
was looking at the forms collection, but cant figure out how to do this as
the count property of the forms collection is amended after each 'close'.
Also I dont see the close method available.

 

[Allen Browne]

Loop backwards through the Forms collection:

For i = Forms.Count - 1 to 0 Step -1
DoCmd.Close acForm Forms(i).Name
End For

Here's another approach. Paste the function below into a standard module.
Set the On Close property of each form to:
=Keep1Open([Form])
and the OnClose property of each report to:
=Keep1Open([Report])
The function lets the user navigate from anywhere to anywhere, but opens the
switchboard when no other form or report remains open and visible.

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.
'Return: None.
'Usage: In the OnClose property of forms and reports:
' =Keep1Open([Form])
' =Keep1Open([Report])
Dim sName As String 'Name of the object being closed.
Dim lngObjType As Long 'acForm or acReport
Dim bFound As Boolean 'Flag not to open the switchboard.
Dim frm As Form 'an open form.
Dim rpt As Report 'an open report.

'Initialize
sName = objMe.Name
If TypeOf objMe Is Form Then
lngObjType = acForm
ElseIf TypeOf objMe Is Report Then
lngObjType = acReport
End If

'Any other visible forms?
For Each frm In Forms
If frm.Visible Then
If frm.Name <> sName Or lngObjType <> acForm Then
bFound = True
Exit For
End If
End If
Next

'Any other visible reports?
If Not bFound Then
For Each rpt In Reports
If rpt.Visible Then
If rpt.Name <> sName Or lngObjType <> acReport Then
bFound = True
Exit For
End If
End If
Next
End If

'If none found, open the switchboard.
If Not bFound Then
DoCmd.OpenForm "frmSwitchboard"
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.
MsgBox "Error " & Err.Number & " - " & Err.Description
End If
Resume Exit_Keep1Open
End Function