Hello:

This is my first post so please be gentle. :) I spend 99.9% of my development in a VB .NET/C# .NET/ASP .NET/etc.. environment so this is relatively "new" to me. I have a bit of VBA experience from college but that's about it. I need to get my learning curve on. :)

I have a VBA 2007 application that has several different forms including a Switchboard type page. I'd like to have this page be visible (Opened?) if the user closes the other forms. I created a Functions modules that iterates through Form's collection and checks if a form is open. But, I'd rather not put a function call to this in every single form's Close event. Is there a global event or a better way to do this? Thanks in advance.

Here's the code for anyone that cares:

' Checks if the specified form is open
Public Function IsFormOpen(ByVal sFormName As String) As Boolean
    ' Validation
    If (IsNull(sFormName) = True Or Len(sFormName) = 0) Then Exit Function

    ' Check if specified form is open
    If SysCmd(acSysCmdGetObjectState, acForm, sFormName) = acObjStateOpen Then
       If Forms(sFormName).CurrentView = 1 Then
          IsFormOpen = True
       Else
          IsFormOpen = False
       End If
    End If
End Function

' Check if any form is open
Public Function IsAFormOpen() As Boolean
    ' Local Variables
    Dim iCounter As Integer ' Counter
    Dim bReturn As Boolean ' Is a form open flag (RETURN)

    ' Initialize
    iCounter = 0
    bReturn = False

    ' Validation
    If (Forms Is Null) Then Exit Function

    ' Check if a form is open
    For iCounter = 0 To Forms.Count - 1 Step 1
        If (Functions.IsFormOpen(Forms(iCounter).Name) = True) Then
            bReturn = True
            Exit For
        End If
    Next iCounter

    ' Return
    IsAFormOpen = bReturn
End Function

I am a brute force coder, so this may not be what you want. The only way around this I can think of is to create a small unbound hidden form that checks for form status every few seconds or so.

The better idea is to put the function call in each form's On Close event. Is there a reason you don't want to do that?

Normally what I do is to make sure that only one form is open at a time. I remove the Close buttons from the forms and give the user a "Return to Main Menu" button etc.

The thinking is that there should be only one form open at a time and the user can navigate through the levels of the app with button events. I have even used a left menu button navigation that looks like a web navigation system. The open form is always maximized, and each form displays the same navigation sub form. The navigation subform closes the current form and opens the next, and it appears there as a subform on the new main form, and then checks to see which main form is open, and then changes the font to bold red on the open form, like a web nav. This way it is impossible for the user to get lost.

Anyway, TMI? DId that help?

mmcdonal

Look it up at: http://wrox.books24x7.com

No. I understand. That's a nice idea but not what the client asked for or is willing to pay for. :)

Added a routine on the form's close even to detect if any forms are open is doable, I just hate relying on each form to know/have these special rules. A new developer might not know about it and after creating a new form, the application doesn't work as intended.