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 The calling form even waits until the dialog box is closed.

The problem is how to get data back from it.

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.) 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. 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.

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 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

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

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
URL: http:// cpcug.org / user / clemenzi / technical / Databases / MSAccess / CustomDialogBoxes.html