Microsoft Access 2010 Bible

Chapter 25: Advanced Data Access with VBA

895

FIGURE 25.12

Creating a dialog box that opens a form

Although not implemented in frmFilterProducts, the cmdOK_Click event procedure could

also contain a DoCmd.Close statement to close the dialog box after it has opened frmProduct

Example4. Or, you may elect to keep the dialog box open to make it easy for users to select

another product category to view.

Summary

This chapter examines several fairly advanced techniques for working with data on Access forms.

In each case, a few lines of VBA code is all you need to make a form more efficient and effective for

users.

The With keyword is used to save execution time by not referencing the controls on the form explicitly

(that is, directly) — for example, Forms!frmProductsExample4.SetFocus. This syntax requires

Access to search alphabetically through the list of forms in the database container. If there are 500

forms (and some large systems have this many or more) and the form name started with z, this would

take a considerable amount of time. Because there is more than one reference to the form, this process

would have to take place multiple times. The With command sets up an internal pointer to the form so

that all subsequent references to controls, properties or methods (like Requery or SetFocus) of the

form are much faster.

When you use the With keyword and reference the form name, you simply use a dot (.) or an excla-

mation point (!) to reference a control, property, or method just like the Forms!FormName was first.

You can see this in Figure 25.12.

For each With, you must have an End With.

Using the With keyword