.jpg "Microsoft Certified Professional")
VII - Using DoCmd
One of the unique features of Visual Basic for Applications (VBA) is the DoCmd object which you can use to run many actions in the Access portion of your project such as opening and closing forms and reports, manipulating data and even running other code or applications.
DoCmd has a collection of methods that act like subroutines you can call to perform the various actions. Each of these methods has a definition that accepts one or more parameters to define the action you want to perform. When you call one of the methods, Intellisense will prompt you to enter the parameters, some of which are limited to a list of specific values.
You can perform many of these methods in VBA in other ways than using DoCmd and, in some cases as you write more advanced programs, DoCmd might not be the best way to go. It is, however, a valuable shortcut, especially as you become more familiar with VBA and the things you can do with the language. Exploring the DoCmd methods is a great way to become more comfortable working with VBA.
The above screenshot shows a couple of form events that are using DoCmd to carry out actions. The first one is a form Resize event which calls the Restore method whenever the form is minimized or maximized. I've sometimes used this to prevent a form from being resized when it might cause the interface to become cluttered or cause confusion for the user.
The second example is a button Click event. This particular form is a program menu and the button, cmdMain, is designed to open the main entry form of the application. The Close and OpenForm methods of the DoCmd object are called to close the menu and open the required form. The Close method uses three arguments to:
- Specify that it's closing a form
- Specify the form name
- Require that the program prompt the user to save any design changes that have been made to the form before closing it.
Often, the DoCmd methods include optional and default parameters. In the case of the Close method, the defaults would have enabled this command to be written in this case as:
DoCmd.Close
This would have assumed that the active window, or the form where the button was being clicked, was to be closed and that the program should prompt to save any changes. It's also possible to accept the defaults for some parameters and specify others like so:
DoCmd.Close , , acSaveYes
In this case, the command closes the current window and automatically saves any design changes without prompting. When accepting the default values in this way, the commas are only required if you want to specify the value for a parameter later in the declaration statement. For example:
DoCmd.Close , "Welcome"
The comma acts as a placeholder for the first argument and accepts the default for the object type being closed. The form name is then specified here but no further placeholders are needed at the end of the statement even though there are additional parameters that could be used.
In the series of screenshots below, you can see another example of DoCmd being used to open a form in Access. Note that for a number of the parameters of the OpenForm method, Intellisense supplies a list of possible values to choose from.
Also notice that after the FormName parameter in the first screenshot, the rest of the parameters are optional and have default values. The command could be left at:
DoCmd.OpenForm "mnuReference"
This would open the form with the default and most common options. Having the extra options, however, gives you more control if you need it.
Other Methods
There's are a lot of methods available through DoCmd but here are a few that you might use more often.
| Method | Description |
| Close | Close a object such as a form or report. |
| DeleteObject | Delete an Access object (table, query, form, etc.). |
| Minimize | Minimize a form or report to a tab at the bottom of the screen. |
| FindRecord | Search form records for a specific value. |
| OpenForm OpenReport |
Opens a form or report in Access. |
| GoToControl | Moves the focus to a specific control on the form. |
| Quit | Close current database and exit Microsoft Access. |
| Beep | Play a beep sound through the speaker. |
For a complete list, look up the DoCmd object in the Access help file. You can also get help on a specific method by positioning the cursor on the command in VBA code and pressing F1.
