Should I use a macro or Visual Basic?

Macros are an easy way to take care of simple details such as opening and closing forms and running reports. You can quickly and easily tie together the database objects you've created because there's little syntax to remember; the arguments for each action are displayed in the Macro window.

In addition to the ease of use macros provide, you must use macros to:

• Make global key assignments.
• Carry out an action or a series of actions when a database first opens. However, you can use the Startup dialog box to cause certain things to occur when a database opens, such as opening a form.

You should use Visual Basic instead of macros if you want to:

• Make your database easier to maintain. Because macros are separate objects from the forms and reports that use them, a database containing many macros that respond to events on forms and reports can be difficult to maintain. In contrast, Visual Basic event procedures are built into the form's or report's definition. If you move a form or report from one database to another, the event procedures built into the form or report move with it.
• Use built-in functions, or create your own. Access includes many built-in functions, such as the IPmt function, which calculates an interest payment. You can use these functions to perform calculations without having to create complicated expressions. By using Visual Basic, you can also create your own functions either to perform calculations that exceed the capability of an expression or to replace complex expressions. In addition, you can use the functions you create in expressions to apply a common operation to more than one object.
• Handle error messages. When something unexpected happens while a user is working with your database, and Access displays an error message, the message can be quite mysterious to the user, especially if the user isn't familiar with Access. By using Visual Basic, you can detect the error when it occurs and either display your own message or take some action.
• Create or manipulate objects. In most cases, you'll find that it's easiest to create and modify an object in that object's Design view. In some situations, however, you might want to manipulate the definition of an object in code. By using Visual Basic, you can manipulate all the objects in a database, as well as the database itself.
• Perform system-level actions. You can carry out the RunApp action in a macro to run another Microsoft Windows-based or Microsoft MS-DOS–based application from your application, but you can't use a macro to do much else outside Access. By using Visual Basic, you can check to see whether a file exists on the system, use Automation or dynamic data exchange (DDE) to communicate with other Windows-based applications such as Microsoft Excel, and call functions in Windows dynamic-link libraries (DLLs).
• Manipulate records one at a time. You can use Visual Basic to step through a set of records one record at a time and perform an operation on each record. In contrast, macros work with entire sets of records at one time.
• Pass arguments to your Visual Basic procedures. You can set arguments for macro actions in the lower part of the Macro window when you create the macro, but you can't change them when the macro is running. By using Visual Basic, however, you can pass arguments to your code at the time it is run or you can use variables for arguments— something you can't do in macros. This gives you a great deal of flexibility in how your Visual Basic procedures run.