2.2. VBA versus Macros in Access

Now that you've seen a little about how VBA works within Access you might be chomping at the bit to get started. However, there's one other scenario you should consider before jumping into Access programming without looking back: a macro. You can create a macro—a saved series of commands. Unlike in Word and Excel, where you can record your own macros, in Access you'll have to create the macro yourself, step-by-step. A macro enables you to perform a variety of operations within Access in response to the click of a command button or any other programmable event on a form or report.

If you've programmed in Word or Excel, you know that you can create a macro by starting the macro recorder and performing the desired steps. When you stop the macro recorder, all of the operations you've performed, from mouse clicks to keyboard strokes to menu selections, are recorded and saved in VBA code. You can then run the macro at a later time by selecting it from the Macros dialog box or in response to a keyboard or menu shortcut. Once you've recorded your macro, you can examine the VBA code behind the macro by simply choosing Edit from the Macros dialog box. This is one of the easiest ways to learn some VBA code within Word or Excel. For example, if you want to know the VBA code to insert three lines of text at the end of your Word document, just create a Word document start recording a macro, and type your three lines of text. You'll end up with code that looks similar to the following:

Sub InsertName()
' InsertName Macro
' Macro recorded 9/21/2003 by Patricia Cardoza
    Selection.TypeText Text:="Patricia Cardoza"
    Selection.TypeParagraph
    Selection.TypeText Text:="MIS Application Specialist"
    Selection.TypeParagraph
    Selection.TypeText Text:="Pacific Southwest Container"
    Selection.TypeParagraph
End Sub

As you can see, there are some keywords you need to know before you can program Word to do what you want in VBA. Recording a macro in Word first, then perusing the commands can help you to figure out how to write more sophisticated code directly in the VBA Editor. As shown in the previous code listing, TypeText is the method of the Selection object that allows you to enter your own text within the document. TypeParagraph inserts a carriage return in the document. These are just two of the many methods you can use with the Selection object. While very few programmers ever need to use every method of an object, you can write better VBA code by familiarizing yourself with some of the most frequently used methods of the objects you'll be dealing with.

While Word and Excel have the ability to record macros, Access doesn't have this capability. To write VBA code in Access, you'll have to just jump right in and code. However, if you aren't quite ready for VBA code yet, you can still create detailed macros using the Macro Editor in Access. The only limitation is that you can't record a macro; you must create it yourself step-by-step. This book is the Access 2003 VBA Programmer's Reference so we won't spend much time on Macros, but we'll provide a very brief tutorial on creating and using macros in Access here.

2.2.1. Macros in Access 2003

This section provides an introduction to using macros in Access 2003. Not much has changed in the last several versions of Access with respect to macro recording, but if you're just picking up Access 2003 for the first time, this section is for you.

You can use macros for a variety of tasks in Access. Even though it might sound a bit crazy, we usually prefer to write code rather than create a macro. However, that's not always the easiest or most logical method of automation. Access 2003 includes 55 built-in macro commands. Many have additional conditions that can be set. For example, if you choose the OpenDataAccessPage macro action, you'll need to select an existing Data Access Page in your database. You can also choose whether to open the Data Access Page in Browse View or Design View. Other macro actions have similar additional required arguments.

To create a new macro, navigate to the Macros tab of the Access 2003 TaskPane and click New; Access displays the new Macro window, see Figure 2-3. The default name for your new macro is Macro1, but you should change the name when you save the macro. There's nothing inherently wrong with naming your macro Macro1, but it doesn't give you very much of a clue about what the macro is for. It's better to give your macro a descriptive name, such as mcr_OpenForm (which follows the Reddick naming conventions) or even something as simple as GoToRecord (which can be the name of the action the macro performs). Whatever you name your macro (and whether or not you choose to follow Reddick's conventions or create your own conventions) make sure you can easily discern the purpose of the macro when you're looking at your Access 2003 database objects.

When I started programming in Access I didn't have a book like this to learn from. So I created many queries with the name Query1, Query2, Macro1, Macro2, and so on. While the queries and macros I created worked just fine, when I then had to update those databases years later, I couldn't remember what each individual query and macro did. I had to go through each query and macro one-by-one and rename them according to their purpose before I could update the database. Don't make the same mistakes I did when I started Access development.

Figure 2.3. Figure 2-3

Now that you've opened up a blank macro, click the first line of the Action column to display the Actions drop-down menu shown in Figure 2-4.

Figure 2.4. Figure 2-4

To implement an action, click the Action name to add it to your macro. Depending on the action you choose, you'll see additional criteria appear in the Action Arguments section of the window. Not all actions have arguments. In particular the Beep, CancelEvent, FindNext, Maximize, Minimize, Restore, ShowAllRecords, and StopMacro actions don't have arguments. Figure 2-5 shows a macro with several different actions. The Action Arguments is shown for the OpenForm action.

For readability, some Access programmers like to group their actions in a macro, leaving a blank line between groups of actions. There's nothing wrong with this practice; however, there's no advantage to it either.

Figure 2.5. Figure 2-5

Now that you've completed your macro, save you changes and exit. However, what good is a macro if you don't have a way to call it? One of the common uses for a macro and one of the easiest ways to use one is in response to the click event of a command button on a form. To associate a macro with the click event of a command button, use the following steps:

You can also call macros from within your code. You might wonder why you would ever call a macro from within code. After all, you're already writing code, why not just write code to accomplish the steps in the macro? Well, we can't give you a definitive answer to that question, except to say that if you already have a perfectly good macro that does what you need, why not use it? Writing code to duplicate a working macro is like taking two steps backward for every one step forward. On the other hand, sometimes you just want everything in one place. If so, go ahead and duplicate your macro actions within code.

2.2.2. Advantages to Using VBA over Macros

While macros are perfectly acceptable and even recommended in certain situations, there are some key advantages to using VBA instead of a macro. The following is a list of some of the advantages you'll enjoy using VBA instead of a macro.

• Error Handling: If a macro encounters an error, it just stops. For example, a macro created to open a form will fail if the form can't be found. While Access will provide a fairly detailed error message informing you the macro failed, you can't add any error handling. If you're writing a macro to add a menu item to the menu bar, how can you be sure the code only runs once? If the code were to run twice, you could end up with two instances of the same menu listed on your menu bar. Using VBA code for this task is more appropriate as you can test to see if the menu has already been added. If not, the code runs; if so, the code does not run or displays a graceful error message. In some circumstances, running your macro could even crash the entire Access application.

• Speed: A one-action macro will probably execute faster than the equivalent VBA code. However, running a complex macro with 10 or 12 actions usually takes significantly longer than the equivalent code. VBA code within Access is fast. If you're designing an end-user application, you definitely need to be concerned with speed. If your users see the hourglass for even more than 5 or 6 seconds, their perception will be that your application is slow.

• Functionality: With 55 macro actions, how could you ever miss functionality? We're being facetious, of course—if all Access programming were limited to 55 actions, there wouldn't be very many applications written in Access. How would you ever display a custom error message, play a sound other than the default "beep" or open an HTML file in response to a button click? VBA provides so much more functionality than Access macros. Some of the key functionality is the ability to interact with other applications. Using VBA, you can openWord or Excel files, send e-mail from Outlook, open an Internet Explorer browser and navigate to a particular Web site, or open almost any file stored on your computer or a network drive. External application access isn't limited to Microsoft products either. You can add a reference to any number of applications through the References dialog box in Access VBA. Once you've added a reference, you can control other applications such as Adobe Acrobat, VMWare, or Yahoo Messenger. You can also take advantage of many Web services such as MapPoint and CarPoint.

• Control: With VBA, you can exercise almost complete control over your code. Instead of working with macros where you are must let the chosen macro actions perform the work, you can control each step of the process in VBA. Macros can't ask for a variety of variables to input into an equation. They can't dynamically create an ADO connection based on user input. They also can't run a different set of actions for each user of your application. VBA can accomplish all of these tasks with ease.

• Interaction with other applications: When using VBA within Access you're not limited to merely programming Microsoft Access. You can add references to other object libraries such as Word, Excel, Outlook, and even non-Microsoft programs including accounting packages, drafting programs, and even graphics programs.