Section 16.1. The Visual Basic Editor | Access 2007[c] The Missing Manual

16.1. The Visual Basic Editor

Although Visual Basic code's stored in your database, you need to use a different tool to view and edit it. This tool's called the Visual Basic editor.

The Visual Basic editor works in concert with Access, but it appears in a separate window. To get to the Visual Basic editor, in the Access ribbon, choose Database Tools Macro Visual Basic. Access launches the standalone window shown in Figure 16-1, complete with an old-fashioned menu and toolbar.

Note: You can close the Visual Basic editor at any time. If you don't, Access shuts it down when you exit.

Figure 16-1. When you start out in the Visual Basic editor, you'll notice that everything's blank. There aren't any code modules in the Project window, and there isn't any code on display (yet).

The Visual Basic editor window's divided into three main regions . At the top left, the Project window shows all the modules in your database. (Each module's a container for one or more code routines.) Initially, the Project window's almost empty, because you haven't created any code yet. In Figure 16-1, the Project window has a single item (named "BoutiqueFudge" to correspond with the current database). However, this project doesn't contain any modules with code.

Note: Depending on the recent tasks you've undertaken in Access, you may see an oddly-named acwztool project in the Projects window. This project's an Access add-in that powers most of the wizards you use in Access. Don't try to browse any of the code in this projectAccess doesn't let you.

Just under the Project window is the Properties window, which shows settings that you can change for the currently selected item in the Project window. At the bottom's the Immediate window, a quick testing tool that lets you run code without first putting it in your database. Everything else is empty space that's used to display your code files, once you create them. This region starts off blank.

In the following sections, you'll learn the most straightforward way to create a piece of code:

First, you'll create a brand new module, which is the container where you place your code .
Then, you'll write the simplest possible code routine inside your module .
Finally, you'll run your code to see it in action .

And when you've finished all this, you'll consider how code can plug into the forms and reports that are already in your database. (This is where things really start rocking.)

16.1.1. Adding a New Module

Usually, you'll build code routines that connect to forms, and spring into action when specific events (Section 15.5) take place. However, in this chapter you'll start a bit slower by creating a standalone code routine that runs only when you tell it to.

The first step's to add a new module for your code. In the Visual Basic editor's menu, choose Insert Module. Figure 16-2 shows what youll see.

Figure 16-2. Once your project has at least one module, the Project window shows a Modules group . Access gives new modules boring names like Module1, Module2, and so on. To choose something better, select the module in the Project window, and then, in the Properties window just underneath, change the "(Name)" property. DataCleanupCode makes a good module name .

When you add a new module, the Visual Basic editor automatically opens a code window that shows the contents of that module. (If your database has more than one module, you can open the one you want by double-clicking it in the Project window.) Initially, a brand new module has just one line of code, which looks like this:

 Option Compare Database

This line's an instruction that tells Visual Basic how to handle operations that compare pieces of text. Ordinarily, Visual Basic has its own rules about how to deal with text, but this statement tells it to use the Access settings instead.

The Access settings depend on the locale of the current database (like whether your version of Windows is using U.S. English or Japanese kanji script). Option Compare Database's ultimate result's that most English-speaking people case-insensitive comparisons. That means fudge is considered the same as fUdGe , which is the same way Access treats text when you write queries.

Before you write code that actually does anything, you should add one more instruction to the top of your code file. Just before (or after) the Option Compare Database instruction, add this:

 Option Explicit

This instruction tells Visual Basic to use stricter error checking, which catches common typos when using variables (Section 17.1).

Tip: You can tell Visual Basic to add the Option Explicit line automatically to all new code files. To do so, select Tools

Options, check the Require Variable Declarations option, and then click OK. Access experts always use this setting.

As with other Access database objects, when you close the Visual Basic editor, Access prompts you to save any newly created modules. If you don't want to wait that long, then choose File Save [DatabaseName] where DatabaseName is the name of your database file.

Note: Once you've saved your module, you can see it in the Access window's navigation pane. If you're using the Tables and Related Views mode, your module appears in the Unrelated Objects category. If you're using the Object Type mode, then it appears in a separate Module category. If you double-click a module in the navigation pane, then Access opens it in the Visual Basic editor.

16.1.2. Writing the Simplest Possible Code Routine

Inside every module (except the empty ones) are one or more Visual Basic subroutines . A subroutine's a named unit of code that performs a distinct task. In the VB language, subroutines start with the word Sub followed by the name of the subroutine. Subroutines end with the statement End Sub . Here's an example subroutine that's rather unimaginatively named MyCodeRoutine:

 Sub MyCodeRoutine()     ' Your code goes here. End Sub

This small snippet of VB code illustrates two important principles. First, it shows you how to start and end any subroutine (by using the Sub and End Sub statements). This code also shows you how to create a comment . Comments are special statements that Access completely ignores. Comments are notes to yourself (like explaining in plain English what the following or preceding line of code actually does). To create a comment, you just place an apostrophe (') at the beginning of the line.

Tip: The Visual Basic editor displays all comments using green text so you can clearly see the difference between comments and code.

Right now, MyCodeRoutine doesn't actually do anything. To give it more smarts, you add code statements between the Sub and End Sub. The following exceedingly simple code routine shows a Message box:

 Sub MyCodeRoutine()     ' The following statement shows a message box.     MsgBox "Witness the power of my code." End Sub

This code works because the Visual Basic language includes a command named MsgBox. (See the box "The Visual Basic Language Reference" in Section 16.2 for advice on how to master all the commands you have at your disposal.) You use this command to pop up a basic Message box with a message of your choosing. The message itself is a piece of text (or string in programmer parlance), and like all text values in VB, it needs to be wrapped in quotation marks so Access knows where it starts and where it ends. (Access forces you to obey the same rules when you use text in an expression.)

Once you've typed this in (go aheadtry it!), you're ready to run your code routine. To do so, place the cursor anywhere inside the subroutine so the Visual Basic editor knows what code you're interested in. Now, on the Visual Basic toolbar, click the Run button (which looks like the play button on a VCR control), or, from the menu, choose Run Run Sub/UserForm. Figure 16-3 shows the result.

Figure 16-3. Your first code routine isn't terribly useful, but it does prove that you know enough to write a line of code and run it.

Access runs the code in a subroutine one line at a time, from start to finish. When you show a MsgBox, your code pauses until you click the OK button in the Message box, at which point it carries on and finishes the subroutine.

Note: Remember, Access considers VB code potentially dangerous, so it doesn't run it in an untrusted database. In other words, if you see the security message (Section 15.2.2), then you need to click Options to show the Microsoft Office Security Options dialog box, choose the "Enable this content" option, and then click OK. Or you can create a trusted location for your database files. Section 15.2.4 has the full story.

GEM IN THE ROUGH
The Visual Basic Language Reference

Visual Basic is stuffed full of magical commands like Msg-Box. You'll explore many of them in this chapter and the next , but for the full scoop on all the commands, you need to fire up Access Help. Here's how:

From the Visual Basic editor's menu, choose Help Microsoft Visual Basic Help.

You see a list of links that promises to teach you more about the VB language.
Click Visual Basic for Applications Language Reference.

You see several more topics that cover all the messy details of VB.
Click Visual Basic Language Reference to delve deeper.

Now you see topics that describe the VB language itself. These fundamentals apply to Visual Basic in any Office program.
Click Functions to see a list of Visual Basic commands, including MsgBox. (Click any function to see a detailed reference page about it.)

Access Help's a great way to learn more about Visual Basiconce you know a few fundamentals. If you dive into it too soon, you'll probably find that the explanations are about as clear as split-pea soup. But by the time you've finished working through the code examples in this book, you'll be ready to use it to learn more.