GetInfoForm(): Get User Input Without Needing a Global Form

Mike Wolfe -

One technique for passing values back and forth between forms and reports in Microsoft Access is to stash them temporarily on a hidden "Global" form.  In a previous article, I wrote about the convenience function I use to simplify this process, GetInfo():

Convenience Function for Passing Values Between Forms via a Hidden Global Form
I don’t always use a global form to stash user-provided values, but when I do, I use this GetInfo() function.
No Longer SetMike Wolfe

The problem with that approach is that it requires you to rely on global state, even if just temporarily.  I covered those problems in some detail in this follow-up article:

Pass Values Between Forms in Access Without Using a Hidden Global Form
Using a hidden “global” form is a common way to return user-entered data to your program, but there is a better way.
No Longer SetMike Wolfe

One downside of the latter approach is that it is a bit more work upfront to develop.  I find the benefits of not having to rely on global state make it more than worth it.  

What's more, it's not that much extra work, especially if you have the GetInfoForm() function at your side.  

It provides a way to open a form, wait for user interaction, and then return a reference to the form for further processing.  By returning the form object itself, you also gain the benefit of IntelliSense when retrieving form values in your calling code.

The Approach

The GetInfoForm() function takes a form name as an argument, along with optional arguments for OpenArgs and whether the form should be resizable.

It first checks if the form is already open, and if so, closes it. It then opens the form, either in dialog mode (if resizable is false) or normal mode (if resizable is true).  (This bit of extra complication is needed because forms opened in dialog mode cannot be resized.)  The function then enters a loop, waiting for the user to either enter information on the form and click [OK] (in which case the form is hidden) or click [Cancel] (in which case the form is closed).

If the form is closed (i.e., the user clicked [Cancel]), then the function returns Nothing. If the form becomes hidden (i.e., the user clicked [OK]), then the function breaks out of the loop and returns a reference to the form.

The Algorithm

Here's how GetInfoForm() works, step by step:

  1. Check if the form specified by GetDataFormName is already open. If it is, close it.
  2. Open the form. If FormIsResizable is true, open it in normal mode. Otherwise, open it in dialog mode.
  3. Enter a loop that waits for the form to close or become hidden.
  4. If the form closes, return a null reference.
  5. If the form becomes hidden, break out of the loop.
  6. Return a reference to the form, unless the form is in design mode, in which case return a null reference.

The Function

Here is the GetInfoForm() function on its own without its required dependencies:

Function GetInfoForm(GetDataFormName As String, _
                     Optional OpenArgs As String = vbNullString, _
                     Optional FormIsResizable As Boolean = False) As Form
    If FormIsOpen(GetDataFormName) Then CloseForm GetDataFormName
    If FormIsResizable Then
        'forms open in dialog mode cannot be resized
        DoCmd.OpenForm GetDataFormName, acNormal, , , , , OpenArgs
        'Wait for form to close or be hidden
        Do
            DoEvents
            Sleep 1
            If (SysCmd(acSysCmdGetObjectState, acForm, GetDataFormName) = 0) Then
                'Form is closed; user canceled
                DoEvents  'workaround for Access 2007 Escape key bug
                Set GetInfoForm = Nothing
                Exit Function
            ElseIf Not Forms(GetDataFormName).Visible Then
                'Form is hidden; stop waiting and throw control back to caller
                DoEvents
                Exit Do
            End If
        Loop
    Else
        DoCmd.OpenForm GetDataFormName, acNormal, , , , acDialog, OpenArgs
        If Not FormIsOpen(GetDataFormName) Then Exit Function    'User canceled
    End If

    'If we got here it means the form is open and hidden
    Set GetInfoForm = Forms(GetDataFormName)
    If GetInfoForm.CurrentView = acCurViewDesign Then
        Set GetInfoForm = Nothing
        Exit Function    'Don't annoy developer by returning an unusable form object when switching to design mode
    End If
End Function

The Full Code

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

  • Sleep API
  • FormIsOpen()
  • CloseForm(): not published yet; a simplified version of this function is provided in the sample code below

This code can be copied and pasted into a blank standard module for a fully-working solution that can be easily integrated into your projects:

Option Compare Database
Option Explicit

'https://nolongerset.com/how-to-pause-vba-code/
#If VBA7 Then
    Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#Else
    Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#End If

'---------------------------------------------------------------------------------------
' Procedure : GetInfoForm()
' DateTime  : 10/24/2016
' Author    : Mike Wolfe
' Source    : https://nolongerset.com/getinfoform/
' Purpose   : Opens a form to get information from a user and returns the hidden form object
' Notes     - Avoids having to store information on the Global form
'           - Upon success, the GetData form should be hidden, not closed
'           - If user cancels, the GetData form should simply be closed
'           - If the form needs to be user-resizable, then do the following
'               o Pass FormIsResizable parameter as True
'               o On GetData form, set .PopUp and .Modal properties to True
' Usage     : Dim Frm As Form_GetDeedInfo, RBook As String, RPage As String
'             Set Frm = GetInfoForm("GetDeedInfo")
'             If Frm Is Nothing Then Exit Sub
'             RBook = Frm.RecordBook
'             RPage = Frm.RecordPage
'             CloseForm Frm
'---------------------------------------------------------------------------------------
'
Function GetInfoForm(GetDataFormName As String, _
                     Optional OpenArgs As String = vbNullString, _
                     Optional FormIsResizable As Boolean = False) As Form
    If FormIsOpen(GetDataFormName) Then CloseForm GetDataFormName
    If FormIsResizable Then
        'NOTE: forms open in dialog mode cannot be resized
        DoCmd.OpenForm GetDataFormName, acNormal, , , , , OpenArgs
        
        'Wait for form to close or be hidden
        Do
            DoEvents
            Sleep 1
            If (SysCmd(acSysCmdGetObjectState, acForm, GetDataFormName) = 0) Then
                'Form is closed; user canceled
                DoEvents  'workaround for Access 2007 Escape key bug
                Set GetInfoForm = Nothing
                Exit Function
            ElseIf Not Forms(GetDataFormName).Visible Then
                'Form is hidden; stop waiting and throw control back to caller
                DoEvents
                Exit Do
            End If
        Loop
    Else
        DoCmd.OpenForm GetDataFormName, acNormal, , , , acDialog, OpenArgs
        If Not FormIsOpen(GetDataFormName) Then Exit Function    'User canceled
    End If

    'If we got here it means the form is open and hidden
    Set GetInfoForm = Forms(GetDataFormName)
    If GetInfoForm.CurrentView = acCurViewDesign Then
        Set GetInfoForm = Nothing
        Exit Function    'Don't annoy developer by returning an unusable form object when switching to design mode
    End If
End Function

'https://nolongerset.com/why-so-lazy-access/
Function FormIsOpen(FormName As String) As Boolean
    FormIsOpen = (SysCmd(acSysCmdGetObjectState, acForm, FormName) <> 0)
End Function

'NOTE: This is a simplified version of my actual CloseForm() function
'       which I have not written about as of publishing; future readers
'       may find the full version at https://nolongerset.com/closeform/
Function CloseForm(FormName As String)
    DoCmd.Close acForm, FormName
End Function

Sample Usage

Let's say you have a form named "GetDeedInfo" that prompts a user to provide the book and page for the record book where a particular deed was filed.  

Here's what the code-behind might look like for the GetDeedInfo form:

Option Compare Database
Option Explicit

Private Sub btnCancel_Click()
    DoCmd.Close acForm, Me.Name
End Sub

Private Sub btnOK_Click()
    Me.Visible = False
End Sub

Here's how you could use GetInfoForm() to open the form, wait for the user to fill it out, and then retrieve the user's input:

Sub TestGetDeedInfo()
    Dim Frm As Form_GetDeedInfo
    Set Frm = GetInfoForm("GetDeedInfo")
    If Frm Is Nothing Then Exit Sub
    
    Dim RecBook As String
    RecBook = Frm.tbRecordBook.Value
    
    Dim RecPage As String
    RecPage = Frm.tbRecordPage.Value
    
    CloseForm Frm.Name
    
    Debug.Print RecBook, RecPage
End Sub

Pay particular attention to the first line in the sample code:

Dim Frm As Form_GetDeedInfo
  • Even though GetInfoForm() returns an Access.Form object, we can assign the return value to a specific form object.
  • We could use Dim Frm As Form instead, and the code would still work, but we would lose 1) IntelliSense and 2) compile-time checking.
  • The "GetDeedInfo" form's HasModule property must be set to True (i.e., "GetDeedInfo" cannot be a so-called "lightweight" form); this happens automatically when you add code behind the form.

Acknowledgements
  • Portions of this article's body generated with the help of ChatGPT

Cover image created with Microsoft Designer