Automating Microsoft Access with VBA

In the first part of this book, you learned the basics of the VBA language. Now it's time to start applying your skills to Access. This chapter covers the use of VBA with one of the most commonly used Access objects: the form. You're no doubt familiar with Access forms from developing applications. As a first step, you learn how to open and close forms using VBA.

Opening a Form

You already saw the code to open a form in Chapter 8's case study. But it's worth reviewing again, to make sure you understand what's going on. The DoCmd object is the key to unlocking many of VBA's capabilities to automate Access. There is one DoCmd object open at all times; you can neither create it nor destroy it. What you can do is use its methods. These methods let VBA code interact directly with Access-specific objects.

To open a form, you invoke the DoCmd.OpenForm method, which has a number of optional arguments:

DoCmd.OpenForm formname, [view], [filtername], [wherecondition], [datamode], [windowmode], [openargs]

As you might assume, you must supply the form name when invoking the OpenForm method. The optional arguments let you control the behavior of the newly opened form in more detail:

• The view argument specifies the initial view (form, datasheet, and so on) of the form.

• The filtername argument lets you supply the name of a query to be used as a filter for the form.

• The wherecondition argument specifies an expression to be used as a filter for the form (you omit the WHERE keyword when specifying a wherecondition).

• The datamode argument specifies the data entry mode for the form (for example, whether the data is read-only or editable).

• The windowmode argument specifies whether the form should be opened normally, hidden, as a dialog box, or as an icon.

• The openargs argument enables you to supply arbitrary data to the form's code. You learn more about openargs later in the chapter.

At its simplest, the OpenForm method assumes that you want to use default values for all these arguments. For example, you can use this procedure to open the Clients form:

Sub OpenClientForm() ' Open a form with default values DoCmd.OpenForm "Clients" End Sub

In this case, the form will open in its last saved view, displaying all its data, in the saved data mode and window mode. But the power of the VBA code comes in when you decide that you don't want to use the default values. For example, this procedure opens the Clients form in Datasheet view, no matter what view it was saved in:

Sub OpenClientFormDataSheet() ' Open a form in datasheet view DoCmd.OpenForm "Clients", View:=acFormDS End Sub

Closing a Form

After a form is open, you can use the DoCmd.Close method to close it again. This method is used for all sorts of Access objects, not just forms, and it has three optional arguments:

DoCmd.Close [objecttype], [objectname], [save]

• The objecttype argument specifies whether you want to close a form, a report, or some other object.

• The objectname argument specifies the name of the object to close.

• The save argument controls whether to save changes to the object when closing it.

If the Clients form is already open, this procedure closes it:

Sub CloseClientForm() ' Close the client form DoCmd.Close acForm, "Clients" End Sub

When closing a form, the default is for Access to prompt the user whether to save any changes to the form itself (such as the application of a new filter, or a change in the order of records). You can override this behavior by specifying the constants acSaveNo (to automatically discard the changes) or acSaveYes (to automatically save the changes). To avoid this sometimes-confusing prompt, modify the procedure this way:

Sub CloseClientForm() ' Close the client form, ' automatically saving changes DoCmd.Close acForm, "Clients", _ Save:=acSaveYes End Sub

TIP

You might have noticed that every argument to the Close method is optional. What happens if you don't specify an object type or an object name? The answer is that VBA closes the object that has the focus when you invoke the method.