Using a Common Dialog control

Sometimes you will want to let the user save a file to disk, for example when exporting values from the database to an Excel spreadsheet. You could ask the user to type the complete file name into a textbox but then you've then got to rely on them typing the path and name in properly. A better solution is to use the Microsoft Common Dialog control. This lets the user navigate through drives and folders and select the file that they want to use.

The Common Dialog control does not appear in the standard MS Access toolbox. Select the 'More Controls' button at the end of the toolbar, pick 'Microsoft Common Dialog Control' from the list that appears, and drop it onto the form.

The Common Dialog control looks like a button at design time. Don't worry about this, the control is invisible at run time and the user won't see it. The control only exists as a container for the methods and properties of the dialogs.

VBA code for a Common Dialog Control

Add this code to the Click event of the button:

This snippet of code will show a standard Windows file-selection dialog to the user. The filename is saved in the FileName property of the control. This filename will be empty if the user presses Cancel or clicks the close widget of the dialog so it's essential that we test for this possibility.

Properties

Although the Common Dialog Control is a Microsoft product, it is not part of MS Access and some of the properties might be unfamiliar. For example, you set DialogTitle instead of Caption and you call ShowSave to make the dialog appear instead of setting Visible = True.

These properties appear in the Access Properties window as for any other control but if you click on the three dots by the Custom property on the Other tab then this custom dialog will appear to let you set the most useful properties:

The Flags property of the Common Dialog control alters the behaviour of the dialog. For example:

will display a "Do you want to replace this file?" message box to warn the user if the file named already exists. The full list of flags is given in Access Help.

Other dialogs

The custom properties dialog has other tabs "Color", "Font", "Print". You can use the ShowColor method of the Common Dialog control to let the user select a colour or ShowPrinter to let the user choose and set up a printer. The Common Dialog control makes it easy for you to give your users a lot more choice without having to do too much programming yourself. It will also make your Access database look a lot more professional because these are standard Windows dialogs and the user will find them familiar and reassuring.

The final thing to remember with all versions of the Common Dialog control is that it will not save a file or change a colour by itself. All that it does is get you the file name or the colour chosen by the user. You as the developer still have to write the VBA code to respond to that choice.