MicrosoftВ® Office Access(TM) 2007 Inside Out (Microsoft Office Access Inside Out)

Now that you’ve learned the fundamentals of using Microsoft Visual Basic, it’s time to put this knowledge into practice. In this chapter, you’ll learn how to create the Visual Basic code you need to automate many common tasks.

You can find dozens of examples of automation in the Conrad Systems Contacts, Housing Reservations, and Wedding List sample databases. As you explore the databases, whenever you see something interesting, open the form or report in Design view and take a look at the Visual Basic code behind the form or report. This chapter walks you through a few of the more interesting examples in these databases.

Why Aren’t We Using Macros?

Although you can certainly use macros to automate simple applications, macros have certain limitations. For example, as you might have noticed when examining the list of available events in Chapter 17, “Understanding Event Processing,” many events require or return parameters that can be passed to or read from a Visual Basic procedure but not a macro. Also, even though you can write a macro to handle general errors in forms and reports, you can’t really analyze errors effectively within a macro nor do much to recover from an error. And as you saw in Chapter 18, “Automating Your Application with Macros,” the debugging facilities for macros are very simplistic.

When to Use Macros

Use macros in your application in any of the following circumstances:

• Your application consists of only a few forms and reports.

• You need to build a simple application that is automated using only trusted macro actions so that the application can run even when the database is not trusted.

• Your application might be used by users unfamiliar with Visual Basic who will want to understand how your application is constructed and possibly modify or enhance it.

• You’re developing an application prototype, and you want to rapidly automate a few features to demonstrate your design. However, once you understand Visual Basic, automating a demonstration application is just as easy using event procedures.

• You don’t need complex error evaluation and handling.

• You don’t need to evaluate or set parameters passed by certain events, such as AfterDelConfirm, ApplyFilter, BeforeDelConfirm, Error, Filter, KeyDown, KeyPress, KeyUp, MouseDown, MouseMove, MouseUp, NotInList, and Updated.

• You don’t need to open and work with recordsets or other objects.

In fact, we can think of only two reasons you’ll ever need to use a macro:

• You must write an AutoKeys macro to define any keystrokes that you want to intercept globally in your application. However, you can also easily define custom handling of keystrokes using the KeyPress events in forms and reports. The one advantage of an AutoKeys macro is it allows you to trap keystrokes in one place. The disadvantage is it is more difficult to customize keystroke handling for individual forms and reports. You’ll learn how to create an AutoKeys macro in Chapter 24, “The Finishing Touches.”

• Because Microsoft Access won’t run any Visual Basic code in a database that is not trusted, in an application that runs using Visual Basic you need to either write a safe AutoExec macro or embed a safe macro in your startup form’s Load event. In that macro, test the IsTrusted property of the CurrentProject object and exit gracefully if the user has not trusted your application.

When to Use Visual Basic

Although macros can be useful, a number of tasks cannot be carried out with macros, and there are others that are better implemented using a Visual Basic procedure. Use a Visual Basic procedure instead of a macro in any of the following circumstances:

• You need complex error handling in your application.

• You want to define a new function.

• You need to handle events that pass parameters or accept return values (other than Cancel).

• You need to create new objects (tables, queries, forms, or reports) in your database from application code.

• Your application needs to interact with another Windows-based program via ActiveX automation.

• You want to be able to directly call Windows API functions.

• You want to define application code that is common across several applications in a library.

• You want to be able to open and work with data in a recordset on a record-byrecord basis.

• You need to use some of the native facilities of the relational database management system that handles your attached tables (such as Microsoft SQL Server procedures or data definition facilities).

• You want maximum performance in your application. Because modules are compiled, they execute slightly faster than macros. You’ll probably notice a difference only on slower processors.

• You are writing a complicated application that will be difficult to debug.

Although you can now trap errors in macros in an application written using Office Access 2007, you don’t have complex evaluation structures such as Select Case to help you decipher and handle the error. Quite frankly, the only reason we might recommend using macros instead of Visual Basic is when you need to write an application that can run in a database that is not trusted. But the instant that you need to use a macro action that is not trusted (our favorites are SetValue and Quit), then you might as well automate your application with Visual Basic.