Data Control, LLC

I find that when developing a proposal, if I give the client a graphic display of their database as it would look to the user, that they are better equiped to help me design the database the way they want it the first time and not after many revisions.

I had a tough time accomplishing this one and my final solution isn’t elegant at all!

I wanted to close all currently open forms except the main menu.  And if the main menu wasn’t open, I wanted to open it at the end of the routine.

 I used the Forms collection to inspect all of the currently opened forms.  I compared the name of the form to two hard coded names that I didn’t want to close (one of which was the form calling this routine).

 But the challenge I encountered was that once a form was closed, the overall collection seems to have changed so that the next time the loop is iterated, a previous form in the collection was being skipped!

 My crappy solution was to simply run the entire routine 5 times which should capture all forms eventually.

I know there must be a better solution, but I simply don’t have the time for elegance on this project and my solution runs so darn quickly, the user will never know.

 Private Sub cmdContinue_Click()

     DoCmd.RunCommand acCmdSaveRecord

      Dim frm As Form

    Dim i As Integer

       For Each frm In Forms

        Debug.Print frm.Name

    Next

       Debug.Print

    Debug.Print “—————————————————————————————————————————————————”

    Debug.Print

   

    For i = 1 To 5

        Debug.Print “LOOP: ” & i

        Debug.Print

        For Each frm In Forms

            Debug.Print “   ” & frm.Name

            If frm.Name = “frm_MainMenu” Or frm.Name = “frmadm95Choose Case” Then  ‘don’t close the main menu

                Debug.Print “    – form not closed: ” & frm.Name

            Else

                Debug.Print “    – trying to close form: ” & frm.Name

                DoCmd.Close acForm, frm.Name

            End If

          Next

       Next

       If nz(Me!cboCase) = 0 Then

        MsgBox “You must choose a case.”

        Me!cboCase.SetFocus

    Else

           DoCmd.Close acForm, Me.Name

        DoCmd.OpenForm “frm_MainMenu”

    End If

End Sub