MS-Access: Populate modal form from custom type

I am trying to display an error form, populated with text from a custom type object, as a MS-Access version of the fantastic answer on this Excel question.

The problem I run into is that I want to wait for a user response/confirmation on this error form, so it must be (as far as I am aware) a modal form and therefore I cannot just open the form and immediately populate it.

I have tried to find a way of doing in Access what was done in Excel; load the form, populate the form then display the form, but this doesn't seem possible since Access' event order is Open->Load->...

I have also tried looking for a way to open as a normal form, populate and then 'modalise' the form but could not find a way to do this.

Does anyone know of a way to achieve this function? Or is there an alternative to modal forms to pause execution awaiting user input?

Solution

A modal form is VERY different from a dialog form.

Modal forms do NOT cause the calling code to halt, but a dialog form does. (do not confuse the two types of forms).

To “get back” a user response from a dialog form, simply set the form visible = false in place of a close form command. This will KICK the dialog form out of dialog mode, and your calling code now continues and the calling code is “free” to examine any values the user typed in or say choose from combo boxes, or check boxes, or whatever.

So your code block will look like this:

Private Sub Command0_Click()

   Dim f       As String
   Dim strT    As String

   f = "formB"
   DoCmd.OpenForm f, , , , , acDialog

   If CurrentProject.AllForms(f).IsLoaded Then
      ' grab any values entered by user, or whatever buttons hit
      strT = Forms(f).Text1
      ' etc. etc. etc. - grab values from that form
      ' don't forget to close the form
      DoCmd.Close acForm, f
   Else
      ' user canceled the form
      ' code goes here for user hitting your cancel button or "x"
   End If

End Sub

And in your dialog form, the “ok” button, or “close” button simply goes:

Me.visible = False

If the user hits your cancel button, that code would be:

Docmd.Close acForm

So if the user hits “cancel” or even the “X” in the upper right corner, you consider and assume the user “bailed out”. When they do this, the form will be closed.

So the code after the dialog part simply checks if the form is STILL open (because your “ok” button does a visible = false).

And since the form is STILL open, then you are free to grab the values of ANY control – say user text typed into a text box, values from a combo box, check box, or whatever.

When user is done the calling code is free to examine or grab any value(s) from that form.

Edit - 2nd solution to allow "setting" of values.

This code will work:

Private Sub Command0_Click()

   Dim f       As String
   Dim strT    As String

   f = "formB"

   DoCmd.OpenForm f
   Forms(f).Text1 = "Hello"    ' set values on form

   ' now WAIT for user   
   Do While CurrentProject.AllForms(f).IsLoaded
      If Forms(f).ok = True Then Exit Do
      DoEvents
   Loop

   If CurrentProject.AllForms(f).IsLoaded Then
       If Forms(f).ok = True Then
          MsgBox "value return = " & Forms(f).Text1
       Else
          MsgBox "user cancel"
       End If
       DoCmd.Close acForm, f
   Else
      ' cancel code goes here
      MsgBox "user cancel"
   End If

End Sub

The code for the OK button on the form B is now:

Me.Visible = False
Me.ok = True

And you need a public var "ok" in form B. eg this:

Option Compare Database
Option Explicit

Public ok      As Boolean

So the cancel button in form B can simply close the form. (you can close the form - but don't set ok = True)