Pause Code Until a Form is Closed or Hidden (without acDialog)

Recently, I wrote about a simple function (and two related convenience functions) I use to pause code without relying on the acDialog flag of the DoCmd.OpenForm / .OpenReport methods.

• WaitTilObjClosed()
• WaitTilFormClosed()
• WaitTilReportClosed()

How to Pause Code Execution Until a Form or Report is Closed (Without Using acDialog)

Do you need to pause your code until the user closes a form or report but are running into problems using acDialog? This simple function is your answer.

No Longer SetMike Wolfe

What if the Form Gets Hidden?

In response to that article, former Access MVP Dale Fye posted the following comment on LinkedIn:

Of course this does not jump out of the loop if the form is simply hidden like using acDialog does.  I like this feature for my application dialog boxes so that I can call a function or procedure which:
1. opens a dialog form
2. waits until the form is either closed (User selects Cancel) or hidden (user selects 'Proceed')
3, then performs actions based on the user selection and then passes some information back to the calling routine (generally a True or False) value used to indicate whether to proceed with the subsequent code or exit the calling procedure.

However, it would be easy to add this feature to your code by simply checking whether the form is loaded and/or visible.

Why Would You Hide a Dialog Form?

The technique Dale refers to is indeed a handy one.

The nice thing about hiding a form is that it is still accessible in code, along with all the values the user entered.  It allows you to use the following algorithm:

1. Open a form in dialog mode (the calling code is paused)
2. If the user presses a [Cancel] button, close the form
3. If the user presses an [OK] button, hide the form
4. In the original calling code, check if the FormIsOpen()
5. If the form is closed, end the routine because the user canceled
6. If the form is open, read all the user-entered values from the hidden form
7. Close the hidden form when you are done with it

As Dale alluded to, this can be a very powerful technique.  It allows you to forgo stashing data on a hidden global form, which is how I handled these sorts of situations for a long time.  It also provides you with IntelliSense when referring to the controls on the hidden form.

But First! Our Obligatory Reverence for Backward Compatibility

The last thing I want to do with a function like WaitTilFormClosed() is change its behavior in some way that could cause issues for all of my existing code.

Even if I think it makes sense 99% of the time to treat a closed form the same as a hidden form for the purpose of my WaitTilFormClosed() function, I can't be 100% certain that all my existing code would work under such an assumption.  Thus, the safe thing to do is create a new function with a name that clarifies its unique behavior.

• WaitTilFormClosedOrHidden()

We will also introduce an optional parameter that defaults to the existing behavior, which is a common technique for maintaining backward compatibility in your code library.

Adding Procedure Parameters Without Breaking Backwards Compatibility in VBA

Sometimes you need to add a parameter to a Function or Sub in VBA. But you don’t need to break all of your calling code to do it.

No Longer SetMike Wolfe

The Updated Code: WaitTilObjClosed()

Here's a revised version of the WaitTilObjClosed() function, which is the foundation of the WaitTilFormClosed() function.

Sub WaitTilObjClosed(ObjType As AcObjectType, ObjName As String, _
                     Optional TreatHiddenAsClosed As Boolean = False)
    Do
        DoEvents
        Sleep 1
        If (SysCmd(acSysCmdGetObjectState, ObjType, ObjName) = 0) Then
            DoEvents  'workaround for Access 2007 Escape key bug
            Exit Do
        ElseIf TreatHiddenAsClosed Then
            Select Case ObjType
            Case acForm:   If Not Forms(ObjName).Visible Then Exit Do
            Case acReport: If Not Reports(ObjName).Visible Then Exit Do
            Case Else
                Throw "Object type {0} not supported", ObjType
            End Select
        End If
    Loop
End Sub

The New Code: WaitTilFormClosedOrHidden()

Here's the new convenience function to go along with the new optional parameter we added above:

Sub WaitTilFormClosedOrHidden(FormName As String)
    WaitTilObjClosed acForm, FormName, TreatHiddenAsClosed:=True
End Sub

The Full Code

The code below includes required helper functions that I've written about in the past:

• Sleep API

For portability, I commented out a call to my custom error-raising function, Throw():

• Throwing Errors in VBA

I've also included a couple of additional convenience functions to make your calling code more readable:

• WaitTilReportClosed
• WaitTilFormClosed
• WaitTilFormClosedOrHidden

Option Compare Database
Option Explicit

Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

'---------------------------------------------------------------------------------------
' Procedure : WaitTilObjClosed
' Author    : Mike Wolfe
' Date      : 1/7/2009 - 9/1/2023
' Source    : https://nolongerset.com/waittilformclosedorhidden/
' Purpose   : Halts program execution until user closes object.  User is generally
'               unaffected by the loop.
' Requires  : Sleep API sub
'  3/23/09  : Removed initial 1000 ms sleep; changed repeating 200 ms sleep to 1 ms;
'               while Sleeping other programs can use processor but Access cannot;
'               during DoEvents, other parts of Access can use processor;
'               without the Sleep call, processor usage stays at 100% for MSACCESS.EXE
'               with a long Sleep call, Access becomes noticeably sluggish
' 12/ 4/15  : Added a workaround for the Access 2007+ Escape key bug: https://social.msdn.microsoft.com/Forums/office/en-US/bb573ad0-7cac-499e-afc9-f9d2ab19cb76/bug-in-access-2007-ltescgt-raises-runtime-error-3059?forum=accessdev#3cbe9f1d-c890-44bc-b0ac-b70405023d34
'---------------------------------------------------------------------------------------
'
Sub WaitTilObjClosed(ObjType As AcObjectType, ObjName As String, _
                     Optional TreatHiddenAsClosed As Boolean = False)
    Do
        DoEvents
        Sleep 1
        If (SysCmd(acSysCmdGetObjectState, ObjType, ObjName) = 0) Then
            DoEvents  'workaround for Access 2007 Escape key bug
            Exit Do
        ElseIf TreatHiddenAsClosed Then
            Select Case ObjType
            Case acForm:   If Not Forms(ObjName).Visible Then Exit Do
            Case acReport: If Not Reports(ObjName).Visible Then Exit Do
            Case Else
                'https://nolongerset.com/throwing-errors-in-vba/
                'Throw "Object type {0} not configured", ObjType
                MsgBox "Object type " & ObjType & " not configured"
            End Select
        End If
    Loop
End Sub

'---------------------------------------------------------------------------------------
' Procedure : WaitTilReportClosed
' Author    : Mike Wolfe
' Source    : https://nolongerset.com/waittilobjclosed/
' Purpose   : Halts program execution until user closes report.  User can still print
'             report, use other programs, and generally be unaffected by the loop.
' Requires  : WaitTilObjClosed routine
'---------------------------------------------------------------------------------------
'
Sub WaitTilReportClosed(RptName As String)
    WaitTilObjClosed acReport, RptName
End Sub

'---------------------------------------------------------------------------------------
' Procedure : WaitTilFormClosed
' Author    : Mike Wolfe
' Source    : https://nolongerset.com/waittilobjclosed/
' Purpose   : Halts program execution until user closes form.  Allows resizing and moving
'             a form, operations that aren't available when opening form in acDialog mode.
' Requires  : WaitTilObjClosed routine
'---------------------------------------------------------------------------------------
'
Sub WaitTilFormClosed(FormName As String)
    WaitTilObjClosed acForm, FormName
End Sub

' ----------------------------------------------------------------
' Procedure : WaitTilFormClosedOrHidden
' Author    : Mike Wolfe
' Source    : https://nolongerset.com/waittilformclosedorhidden/
' Purpose   : Halts program execution until form is closed or hidden.  Allows resizing and moving
'             a form, operations that aren't available when opening form in acDialog mode.
' ----------------------------------------------------------------
Sub WaitTilFormClosedOrHidden(FormName As String)
    WaitTilObjClosed acForm, FormName, TreatHiddenAsClosed:=True
End Sub