Friday, 23 September 2011

Creating a Dialog Form

Dialog Forms are an integral part of most customized Access Database Applications.  As developers, we set them up to enable our applications to communicate and interact with users.  The simplest sort of dialog box is one which displays a message.  These may be created using VBA's MsgBox Function or the MsgBox Macro Action.   Others involve a greater degree of customization, especially when the application requires the user to provide information such as parameters for a query.  One way to do is to create a standard Access form and modify it's properties to make it into a Dialog Form.

Figure 1: This Dialog Form obtains date parameters used to restrict the
records displayed in a report.  You may remember this was NorAzri's chosen
method for filtering report results mentioned in my last post.
To create a Dialog Form such as that displayed in figure one, we begin by creating an unbound form.  This is done by clicking the FORM DESIGN icon from the FORMS group of the CREATE ribbon.  The term unbound means that the form is not bound to a particular Table as with conventional forms.  The information it is designed to collect is temporary, and does not need to be stored.

When a new form opens in design view, the screen will show a blank grid in the main window, and a variety of  form controls in the CONTROLS group of the DESIGN ribbon.  The Dialog Form in figure 1 uses four Combo Box Controls and a Command Button.  The values displayed in the Combo Boxes are defined in the Row Source Property.  I recently blogged about Customizing an Access Combo Box.  If you are interested, I recommend you check it out by following the link above (just remember that the combo boxes in this example do not need a Control Source because we do not need to store it's data in a table).

However in this post we are mainly interested in the process of changing the appearance and action of an ordinary form to that of a Dialog Form.  So how is this done?  The answer to this lies in a number of key form properties.  The two most important properties to understand are the POP UP and MODAL properties.

The POP UP property ensures that when the dialog form is opened, it appears as a pop up box rather than a full size form. Otherwise it would just appear as an ordinary form rather than dialog form.  As such this property should always be set to YES.  Setting the MODAL property to YES ensures the user cannot ignore the dialog form by clicking on a ribbon icon or form control, for example.  The idea here is that your application is waiting for a user response. Access will not do anything until that response is given, and/or the dialog form is closed.  These properties are located on the OTHER TAB of the PROPERTY SHEET.

Figure 2: Set the POP UP and MODAL properties to YES.  This is done
 from the OTHER TAB of the PROPERTY SHEET.  The property
sheet is accessed by clicking the PROPERTY SHEET icon
on the TOOLS group of the DESIGN ribbon.
NB When you change these properties make sure the SELECTION TYPE of the PROPERTY SHEET is set to FORM.  If it is not, just click the drop down list at the top of the sheet and change it. Alternatively click the square box at the top left hand corner of the DESIGN GRID. 

If you look again at the dialog form in figure1 you will notice there are no navigation buttons or record selectors which forms normally have by default.  To remove these just go to the FORMAT TAB of the PROPERTY SHEET and change the NAVIGATION BUTTONS and RECORD SELECTORS properties to NO.

You may also notice that our dialog form has a border style specific to dialog forms.  One of its features is that the user cannot resize it, thereby diminishing its impact!  This is attained by changing the BORDER STYLE property to DIALOG.

One of the more subtle properties that you might want to change is that of the SCROLL BARS property.  I recommend you set this to NEITHER.  Apart from the fact you should not need to scroll a dialog form, Access also leaves a thick white line at the bottom of the form if this is not changed.  This looks particularly bad if you choose a different Back Color for your form.

One last property alteration that I recommend, is the addition of a form Caption.  This appears in the top border of the dialog form.  In figure 1 I have set this to "Please Enter a Date Range".  The caption does not have to be a question, generally it is just a form title.  Either way you will need to choose your own caption and enter it in the CAPTION property, the first property on the FORMAT TAB.

As far as the actual function of the dialog form is concerned, this particular example runs a VBA sub when the command button is clicked.  The code creates an SQL WHERE statement based on information obtained from data entered by the user on the dialog form.  This is part of the code that I used:

varYearFrom = Me!cboYearFrom
varYearTo = Me!cboYearTo
varMonthFrom = Me!cboMonthFrom
varMonthTo = Me!cboMonthTo

strSQL = "(Year between " & varYearFrom & " AND " & varYearTo & ") AND (Month between " & varMonthFrom & " AND " & varMonthTo & ")"

As you can see, each combo box value is stored in a variable in the first four lines of code.  These variables are then integrated into the string containing the SQL Statement. Once we have the SQL Statement stored in the string strSQL, this can be passed as the WHERE parameter of the DoCmd.OpenReport Method like this:

DoCmd.OpenReport varForm, acViewReport, , strSQL

Then, when our report opens, only records falling within the criteria set in the SQL WHERE statement are displayed. In this case these are all the records between January and December 2011 (see figure 1 above).


  1. You have a terrific way of explaining things, thanks for sharing your knowledge!!!

    1. Thanks Emily. Glad you find the blog helpful. And thanks for the feedback as well.