Whenever you open a dialog in MS-Access, you may need/want to get a result.

The MsgBox function is often enough and it can be used as a function. Unfortunately, when it isn't enough and you want to create your own modal dialog, you cannot call a function. The call is DoCmd.OpenForm and that's a sub-routine. No choice.

The idea is simple, we need to open the dialog, let the user interact in some ways, then return the value the user just entered and move on.

Since it is a modal dialog, the DoCmd.OpenForm command blocks until the form (dialog) gets closed. Or does it? The truth is that you can simply hide the dialog, and it will also return.

The way to do so goes like this, assuming you have a Save button:

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

That simple routine has the visual effect of closing the dialog and the macro effect of returning from the DoCmd.OpenForm call. And by not closing the dialog, we still have access to its variables. This is really good since we need them if we want to have a result.

For instance, assuming this dialog was to ask for a phone number, you could do this:

Private Sub Save_Click()
    ' Tag is a string and does not like Null
    Tag = Nz(PhoneNumber, "")
    Me.Visible = False
End Sub

The Tag variable is available in all forms and can be used to pass data around. So that's what we do. Now that the current dialog has its tag set, the parent caller can make use of it.

Private Sub GetPhoneNumber()
    ...
    ' The following call blocks until the dialog gets hidden
    DoCmd.OpenForm "Phone Number", ...
    ' Get the dialog result
    phone_number = Forms("Phone Number").Tag
    ' Make sure the dialog is actually closed
    DoCmd.Close acForm, "Phone Number"
    ...
End Sub

Notice that we need to close the dialog once we are done with its Tag parameter.

This method works in MS-Access 2007 and was found to work at least since MS-Access 97 (most likely from the start.)

Source: http://mc-computing.com/databases/MSAccess/CustomDialogBoxes.html