Trouble looping through form objects

Question

I am trying to create a sub that loops through AllForms, and saves/closes all of them except for one specific form called Globals.  I found and adapted some code, but I'm having trouble with one aspect.  The code I found looped through AllForms using an element called obj that was defined as Object.  See below...

    Dim obj As AccessObject

    Dim dbs As Object

    Set dbs = Application.CurrentProject

    For Each obj In dbs.AllForms

            If obj.IsLoaded And obj.Name <> "Globals" Then

                ''  If form is unsaved (i.e., "is 'Dirty'"), then save record (by forcing Dirty = False)

                If obj.Dirty = True Then obj.Dirty = False

                '' Close form

                DoCmd.Close ObjectType:=acForm, ObjectName:=obj.Name

            End If

    Next obj

    Set dbs = Nothing

Problem is that the Obj.Dirty statement won't work with obj declared as an AccessObject.  It throws a run-time error 438 ("Object doesn't support this property or method").

I thought maybe I could declare it as a Form (which seemed like the most likely variable type to work with AllForms), but that ends up throwing a run-time error 13 ("Type mismatch") on the "For each..." line.

So question is, how can I design this For Each statement to allow me to get to the form properties of the obj members?

Note: I'm suspecting that I'll get some answers that say either (1) "You don't need to save the data" (because it's saved magically somehow whenever you close a form) or (2) "There's a way to save the data automatically as part of the "DoCmd.Close" statement.

For point (1), if that's the case, do please let me know.  I assume that access doesn't just dump dirty data when it closes things, but I always code things to save, just to be safe.  I'm not sure where I'd look to see the rules on this about what gets saved or not saved.

For point (2), I didn't use the Close argument of the DoCmd.Close statement because I couldn't tell from the documentation what that did... It said it "saves changes to the object," but I couldn't tell if that saved changes just to the design or orientation of the form, or actually wrote all of the dirty data to the database.  That's a different question, but also appreciate any insight on that if you have it.

Regardless of these points, I'm interested in the answer to the original question, as I have other things I want to do in future versions that will adjust other form properties within this loop.

Thanks!

Emily

Answer 1

Just modify my function to exclude the specific form you wish to keep open.  see: http://www.devhut.net/2015/02/17/ms-access-vba-close-all-open-forms/

Answer 2

It sounds like you may be confusing Save (which saves design changes to the structure of a form) with saving the Data on a bound form. Setting Dirty to False forces a save of the data, assuming that the data passes all its referential integrity and validation and BeforeUpdate tests.

Also, the AllForms collection is literally ALL forms, open or not. If all you want to do is close all open forms, the Forms collection would return them, without the need to do an extra check:

Dim db As DAO.Database

Dim frm As Form

Set db = CurrentDb

For Each frm In db.Forms

   If frm.Name <> "Globals" Then

      If frm.Dirty Then

         frm.Dirty = False ' you need some error trapping here in case the record can't be saved

     End If

  End If

Next frm

Answer 3

One thing I noticed, just in case anyone reads this thread in the future.  The Forms collection does not appear to have any open modal forms that I have open in it.

I worked around that, but just thought someone might want to know that if they read this.

Thanks!

Emily

Answer 4

Thanks Daniel.  Looks like what I stumbled into is the same as what you posted.  That makes me feel better.

Thanks to everyone!

Answer 5

Thanks John,

When I run that, Ihave an error on compiling.  It trips on the following line:

     For Each frm In db.Forms

The message is "Compile error: Method or data member not found."  I notice that if I delete ".Forms" from "db.Forms" and then type a dot, it brings up a lot of properties and methods that do not include Forms.

I played around a little bit, and although I'm a little out of my depths, I got the following to work.  Anything wrong with this code?  Please tell me if I'm about to blow up my database :).  I bolded the interesting parts.

    Dim frm As Form

    Dim db As Object   

    Set db = Application.Forms

    For Each frm In db

            If frm.Name <> "Globals" Then

                If frm.Dirty = True Then frm.Dirty = False

                DoCmd.Close ObjectType:=acForm, ObjectName:=frm.Name

            End If

    Next frm

    Set db = Nothing

Thanks again!

Emily