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.
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:
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.
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:
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.
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.