Close all open objects

[EternalMyrtle]

Hello,

Could someone please help me out by giving me the code to close all open objects?

Using just DoCmd.Close only closes the active object. I have been specifying everything individually but this is not practical as my database grows.

Thank you!!

 

[pr2-eugin]

What exactly seems to be the problem?

 

[EternalMyrtle]

Well, most of the forms and reports are linked to each other. I have a button on each form/report and an autokey which opens the main menu and closes all other objects but I really need some simple code which will allow me to close everything at once.

Does this exist?

 

[EternalMyrtle]

To make this clearer:

My users are using Runtime so they do not have access to the nav bar. If more than one form/report is open at once, I need to be able to use code to close them when the button/autokey is executed.

 

[pr2-eugin]

Google is your Friend

Hope this helps ! http://www.dzone.com/snippets/close-all-forms-access

 

[EternalMyrtle]

Sorry, I thought I had tried Googling this before Thank you for the link, very helpful!

 

[pr2-eugin]

No problem ! Good luck !

 

[EternalMyrtle]

It didn't close the reports, just the forms

I will Google it and report back

 

[EternalMyrtle]

Perhaps I can just alter the code to include reports:

For Each obj In Application.CurrentProject.AllForms

Can I use an OR or something?? Sorry my vb skills are basic...

 

[pr2-eugin]

Try this alternate approach : http://bytes.com/topic/access/answe...-code-close-all-open-objects-forms-tables-etc

 

[EternalMyrtle]

Did it!!

Dim obj As Object
Dim objReport As Object
Dim strName As String
For Each obj In Application.CurrentProject.AllForms
If obj.Name <> "frmMainMenuBDPR" Then
DoCmd.Close acForm, obj.Name, acSaveYes
End If
Next obj
For Each objReport In Application.CurrentProject.AllReports
If objReport.Name <> "frmMainMenuBDPR" Then
DoCmd.Close acReport, objReport.Name
End If
Next objReport
End Function

 

[EternalMyrtle]

Thank you Paul. I added additional code and it works but I will also check out the link you posted because it might be a better solution.

Really appreciate your help

 

[pr2-eugin]

Not a problem !

I am not 100% sure, but in theory the code you have should throw an error "Cannot find the Form <formName> you are trying to reference", because the code loops through the objects and try to close them even if they are not open in the first place, the CODE in the second link will avoid the error. As it only looks for Loaded objects. IMHO the second one is better.

 

[gemma-the-husky]

out of interest, I use this to close forms in design mode (during development). I never bothered modifying for other types. closing code modules would be useful

Function closedesignforms()
Dim frm As Object
Dim count As Long
count = 0
For Each frm In CurrentProject.AllForms
 
    If IsOpen(frm.Name, acForm) Then
        If frm.CurrentView = 0 Then
            DoCmd.Close acForm, frm.Name
            count = count + 1
        End If
    End If
Next
 
    If count > 0 Then
        MsgBox (count & " forms closed")
    Else
        MsgBox ("No forms to close")
    End If
 
End Function

 

[DemonDNF]

Try this alternate approach : http://bytes.com/topic/access/answe...-code-close-all-open-objects-forms-tables-etc

My attempt at this approach, I put it in Main Menu Unload:

Private Sub Form_Unload(Cancel As Integer)
On Error GoTo Err_Form_Unload
10  Dim aob As AccessObject
20  With CurrentData
[B][COLOR=darkred]30     For Each aob In .AllTables                               ' Tables[/COLOR][/B]
[B][COLOR=darkred]40         If aob.IsLoaded Then[/COLOR][/B]
[B][COLOR=darkred]50             DoCmd.Close acTable, aob.Name, acSaveYes[/COLOR][/B]
[B][COLOR=darkred]60         End If[/COLOR][/B]
[B][COLOR=darkred]70     Next aob[/COLOR][/B]
  
80     For Each aob In .AllQueries                              ' Queries
90         If aob.IsLoaded Then
100            DoCmd.Close acQuery, aob.Name, acSaveYes
110        End If
120    Next aob
130 End With
 
 
140 With CurrentProject
150    For Each aob In .AllForms                                ' Forms
160        If aob.IsLoaded Then
170             If aob.Name <> "frmMain" Then
180                 DoCmd.Close acForm, aob.Name, acSaveYes
190             End If
200        End If
210    Next aob
 
220    For Each aob In .AllReports                              ' Reports
230        If aob.IsLoaded Then
240           DoCmd.Close acReport, aob.Name, acSaveYes
250        End If
260    Next aob
 
270    For Each aob In .AllDataAccessPages                      ' Pages
280        If aob.IsLoaded Then
290            DoCmd.Close acDataAccessPage, aob.Name, acSaveYes
300        End If
310    Next aob
 
320    For Each aob In .AllMacros                               ' Macros
330        If aob.IsLoaded Then
340            DoCmd.Close acMacro, aob.Name, acSaveYes
350        End If
360    Next aob
 
370    For Each aob In .AllModules                              ' Modules
380        If aob.IsLoaded Then
390            DoCmd.Close acModule, aob.Name, acSaveYes
400        End If
410    Next aob
420 End With
Exit_Form_Unload:
    DoCmd.SetWarnings True
    DoCmd.Hourglass False
    Exit Sub
Err_Form_Unload:
    ErrorLine = Erl
    ErrorNumber = Err.Number
    ErrorDescription = Err.Description
    ErrorForm = "frmMain"
    ErrorSourceType = "Sub"
    ErrorSourceName = "Form_Unload"
    Call gsubErrorHandler(ErrorLine, ErrorNumber, ErrorDescription, ErrorForm, _
                          ErrorSourceType, ErrorSourceName)
    Resume Exit_Form_Unload
End Sub

I noticed while watching in debug that this also scrolls through the hidden system tables as well.

Is it ok to close the system tables?

It didn't generate errors, it apparently closed them and kept on looping. But I practically have no data as I'm still designing. I don't want to set myself up for some serious pain further down the road.

Robert

 

[Galaxiom]

Rather than looping the AllForms and AllReports Collections in CurrentProject why not loop the Forms and Reports Collections?

They hold just the loaded objects.

Users should not be directly opening tables and queries.

 

[gemma-the-husky]

Rather than looping the AllForms and AllReports Collections in CurrentProject why not loop the Forms and Reports Collections?

They hold just the loaded objects.

Users should not be directly opening tables and queries.

yes, you are right.

it's often a matter of finding or building a mousetrap that works, and not realising there was a better way to achieve the same result.

 

[DemonDNF]

Rather than looping the AllForms and AllReports Collections in CurrentProject why not loop the Forms and Reports Collections?
...

Ok, removed everything except forms and reports but I'm hitting a snag with that:

Run-time 438
Object doesn't support this property or method.

140 With CurrentProject
150    [B][COLOR=darkred]For Each aob In .Forms[/COLOR][/B]                                ' Forms
160        If aob.IsLoaded Then
170             If aob.Name <> "frmMain" Then
180                 DoCmd.Close acForm, aob.Name, acSaveYes
190             End If
200        End If
210    Next aob
 
220    For Each aob In .Reports                              ' Reports
230        If aob.IsLoaded Then
240           DoCmd.Close acReport, aob.Name, acSaveYes
250        End If
260    Next aob
...

Using Access 2002. I must not have the proper syntax, or this might be for 2003 and above.

Robert


EDIT: I added a check for frmMain because processing ends and remaining forms are left open.

 

[pr2-eugin]

No not in the CurrentProject, just simply..

For Each aob In Forms                               [COLOR=Green]' Forms [/COLOR]
    If aob.Name <> "frmMain" Then DoCmd.Close acForm, aob.Name, acSaveYes
Next

For Each aob In Reports                             [COLOR=Green]' Reports[/COLOR]
    DoCmd.Close acReport, aob.Name, acSaveYes
Next

 

[Galaxiom]

No not in the CurrentProject, just simply..

The bigger story is that Forms and Reports Collections are members of Application which in turn is the default collection of Access. Hence they can be omitted.

The full reference to the Forms collection is:

Access.Application.Forms