Databases - MS Access 97 Custom Dialog Boxes

Basic information on opening forms using code is fairly simple.

This page focuses specifically on creating and using custom dialog boxes using MS Access 97.

Overview

Opening a Form as a dialog box is simple

  DoCmd.OpenForm "FormName", , , , , acDialog, "Passed Parameter"

The calling form even waits until the dialog box is closed.

The problem is how to get data back from it.

Which button was pressed - OK, Cancel, Retry, ...
If OK was pressed, how is the data read.

Reading data from an open form is fairly simple. A FieldName can be separated from the form reference by either a dot or explanation point (!). However, Form.Tag must follow a dot. (FieldName represents a control, Form is actually a property.)

tempStr = Forms![Form Name]!FieldName.Value   ' Either . or !
tempStr = Forms("Form Name").FieldName.Value  ' Either . or !
tempStr = Forms(DialogName).Form.Tag          ' The dot is required

Unfortunately, when a dialog box is closed, there is no form to get the data from.

The solution is simple - don't Close the dialog, just Hide it. Of course, the help explicitly says "other windows in Microsoft Access are disabled until you close the form". (Suprise - the help is wrong.)

Returning Values via Form.Tag

Attach code similar to the following to the dialog box. Either pass a parameter back through the Form.Tag parameter (shown below), or assign a different value to each button and let the calling form simply read values directly from the dialog box.

Private Sub OK_UIButton_Click()
 ' Hide dialog box so the parameters are still available
    Me.Visible = False
    Tag = ProductID.Value
End Sub

Private Sub Cancel_UIButton_Click()
 ' Hide dialog box so the parameters are still available
    Me.Visible = False
    Tag = -1
End Sub

However, if you just hide the form, the next time it is called, the calling code usually won't wait until the user enters new data. (I've seen it both ways.) As a result, it is necessary for the calling form to actually close the dialog box after the data has been read.

Private Sub Command3_Click()
Dim tempStr As String
Dim ResultFlag As String
Dim DialogName As String
 
 DialogName = "Locate Product"  ' The name of the dialog box
 tempStr = ProductID.Value      ' Parameter used by the dialog box
 
'DoCmd.OpenForm "FormName", , , , , , "Passed Parameter"
 DoCmd.OpenForm  DialogName, , , , , acDialog, tempStr

 ResultFlag = Forms(DialogName).Form.Tag        ' Uses String
'ResultFlag = Forms![Locate Product].Form.Tag   ' Alternate syntax
'Unbound_UIField.Value = ResultFlag  ' for test only
 
 If ResultFlag <> Str(-1) Then      ' Test that OK was pressed
  tempStr = Forms(DialogName)!ProductID.Value
  ProductID_UIEdit.Value = tempStr
 End If
 
    ' Without an explicit "DoCmd.Close",
    '   * The dialog box's Form_Open is run only
    '     the first time "DoCmd.OpenForm" is called
    '   * When the form is merely hidden,
    '     "DoCmd.OpenForm" does not always wait for the
    '     user to hide the dialog box - very unpredictable
 DoCmd.Close acForm, DialogName
 
End Sub

If you elect to return a button ID via Form.Tag, then I suggest using either string values or the standard MsgBox constants (vbOK vbCancel vbAbort vbRetry vbIgnore vbYes vbNo - with values from 1 to 7).

Using a Module

As you can see, the code to open a dialog box will be similar for each Dialog Box in your application. Therefore, it is reasonable to make it more generic and to place it in a separate Module. When producing modules, remember to change the functions from Private (the default value) to Public. The following is from my DialogBoxes Module.

' This routine accepts the name of the dialog box (form)
' and a single ID value. It returns returns the original value
' if either Esc or Cancel is pressed, otherwise, it returns
' the new value
Public Function OpenDialogBox_Int_MC _
    (DialogName As String, CurrentValue As Integer) As Integer
Dim ResultFlag As String
 
 DoCmd.OpenForm DialogName, , , , , acDialog, CurrentValue

 ResultFlag = Forms(DialogName).Form.Tag
 
 If ResultFlag <> Str(-1) Then      ' Test that OK was pressed
  OpenDialogBox_Int_MC = ResultFlag
 Else
  OpenDialogBox_Int_MC = CurrentValue
 End If
 
 DoCmd.Close acForm, DialogName
 
End Function

This only accepts and returns a record ID (long integer), a separate routine (OpenDialogBox_Str_MC) accepts and returns string identifiers (used for short lists, <50 elements - I have no idea what I ment by this). Call this routine with something similar to

tempInt = OpenDialogBox_Int_MC("DialogBoxName", RecordID)

I use _MC (ModuleCode) to indicate code that I've written which is located in a module. This just makes it a little easier for other people to read my code.

Various Techniques

There are several techniques to get data back from a dialog box

Hide the dialog box, let the calling form directly read the data, then let the calling form close it.
"Hardcoding" a write to a parameter on the calling form. Unfortunately, the parameter / field MUST be visible.
"Hardcoding" a call to a function on the calling form (known as a call back).
Saving the data in a table.

In a good generic, re-useable dialog box, the name of the form which called it and the names of elements on the calling form should never be hardcoded. Instead, the name of the form can be passed as a parameter. (I am not aware of any function or parameter that will return a reference to the calling form.)

References and Related Information

Access: Making a query more flexible: How to get a query to request a parameter - Under Criteria, place the prompt in square bbrackets - [ask this question]
Microsoft Info: A list of related information

Author: Robert Clemenzi - clemenzi@cpcug.org