Close all forms in one command?

[fraser_lindsay]

Hi,

I'm trying to keep my database clutter free for the user and thought that it would be good to try and have forms close when the user clicks 'home' and jumps back to the main menu/switchboard.

I have tried this with a macro but I can only seem to be able to set it to close one form and without knowing which forms would be open this makes it difficult.

Is there a VBA command I can use for such an action?

i.e. open switchboard but then close all open forms? If anyone could help me out I'd be very grateful.

 

[Uncle Gizmo]

I did a quick Google and found the following link which I am sure will prove useful:
http://snippets.dzone.com/posts/show/3911

 

[Mike375]

I did a quick Google and found the following link which I am sure will prove useful:
http://snippets.dzone.com/posts/show/3911

Real neat Tony!!

And it works, inluding an OpenForm at the end

 

[Uncle Gizmo]

Thanks Mike, Google is my friend.

 

[MarkK]

Or slightly more efficient, use the Forms collection, which only contains open forms...

[FONT="Tahoma"]Sub CloseAllForms(Optional Except As String)
  Dim i As Integer
  For i = Forms.Count - 1 To 0 Step -1
    If Forms(i).Name <> Except Then DoCmd.Close acForm, Forms(i).Name, acSaveYes
  Next
End Sub[/FONT]

 

[fraser_lindsay]

Guts, thanks very much for your responses. Very much appreciated.

However, I'm still doing something wrong.

The form I want to keep open is my main Switchboard, called 'Switchboard'.
So on another form, I assigned the code Lagbolt suggested to a button's on click event first:

Private Sub Home_Click()

Sub CloseAllForms(Optional Except As String)
  Dim i As Integer
  For i = Forms.count - 1 To 0 Step -1
    If Forms(i).Switchboard <> Except Then DoCmd.Close acForm, Forms(i).Switchboard, acSaveYes
  Next


End Sub

I also tried the link you gave me Tony, but I think this has to go in a module, is that correct? I have edited it (with guess work) and created a new module for it called 'closeallforms'

Function CloseAllForms()


Dim obj As Object
Dim strName As String

For Each obj In Application.CurrentProject.AllForms

If obj.Name <> "Switchboard" Then 

DoCmd.Close acForm, obj.name, acSaveYes

End if 
Next obj

End Function

Then I tried again on my button with this on the on click event:

Private Sub Home_Click()

Run CloseAllForms

End Sub

But I got an error "expected variable or procedure, not module" So I'm clearly not calling it correctly.


I may be a bit out of my depth here, sorry.

 

[boblarson]

Then I tried again on my button with this on the on click event:

Private Sub Home_Click()

Run CloseAllForms

End Sub

But I got an error "expected variable or procedure, not module" So I'm clearly not calling it correctly.

It would just be

Private Sub Home_Click()

 CloseAllForms

End Sub

 

[Mike375]

Use

Call CloseAllForms


You can also put it directly behind a button or stand alone label. Drop the function name and change End Function to End Sub. In what I pasted below you can see I have and OpenForm action at the end. The code, whether calling a function from a module or behind a button can be placed on any of the forms that will be closed.

Private Sub Label111_Click()


Dim obj As Object
Dim strName As String
For Each obj In Application.CurrentProject.AllForms
If obj.Name <> "Your Form" Then
DoCmd.Close acForm, obj.Name, acSaveYes
End If
Next obj
DoCmd.OpenForm "LN", acNormal, "", "", acEdit, acNormal
End Sub

 

[boblarson]

Oh, and I would get rid of the acSaveYes on your form close code as you really need to be careful about using that. Contrary to what you might think it does (it does NOT save records) it saves form changes. So, you could be saving yourself some filter or something else as well which then you won't like later.

 

[fraser_lindsay]

Fantastic, I was getting there.

Thanks a million Mike, that has worked perfectly. Keeps things tidier, which is great.

Bob, thanks for the pointers too, I changed 'acSaveYes' to 'acSaveNo'