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

The typical way to halt program execution until the user closes a form is to open the form with the acDialog option:

Debug.Print "About to open MyForm..."
DoCmd.OpenForm "MyForm", WindowMode:=acDialog
Debug.Print "The user closed MyForm..."

This is simple and reliable, but it also comes with a bunch of constraints:

• You can't interact with any other forms or reports until you close or hide the dialog form
• Any code that comes after the OpenForm line will not run until the form is closed
• Other annoyances that I've bumped into over the years but can't recall right now (feel free to add yours in the comments below)

To work around these constraints, I created a simple procedure named WaitTilObjClosed.

The Approach

We sit in a loop checking for the existence of the object in question until it's no longer open.  To keep the computer and application from becoming unresponsive while we sit in our infinite loop, we include calls to both the Sleep API and DoEvents.

The Algorithm

1. Enter an infinite Do...Loop
2. Cede processor time to the rest of the application
3. Cede processor time to the operating system
4. Check to see if the form or report is closed
5. If the form or report is closed, exit the loop

The Function

Here is the WaitTilObjClosed() function on its own without its required dependencies.

Sub WaitTilObjClosed(ObjType As AcObjectType, ObjName As String)
    Do
        DoEvents
        Sleep 1
        If (SysCmd(acSysCmdGetObjectState, ObjType, ObjName) = 0) Then
            DoEvents  'workaround for Access 2007 Escape key bug
            Exit Do
        End If
    Loop
End Sub

The Full Code

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

• Sleep API

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

• WaitTilReportClosed
• WaitTilFormClosed

The code can be copied and pasted into a blank standard module to get a fully-working solution that you can easily integrate into your projects:

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
' Source    : https://nolongerset.com/waittilobjclosed/
' 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)
    Do
        DoEvents
        Sleep 1
        If (SysCmd(acSysCmdGetObjectState, ObjType, ObjName) = 0) Then
            DoEvents  'workaround for Access 2007 Escape key bug
            Exit Do
        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

Sample Usage

Debug.Print "About to open MyForm..."
DoCmd.OpenForm "MyForm"
Debug.Print "MyForm has been opened..."
WaitTilFormClosed "MyForm"
Debug.Print "The user closed MyForm..."

Cover image created with Microsoft Designer