Programming Microsoft Access
II - The VBA Environment
To non-programmers, the VBA environment can be a little intimidating at first with its lack of directions and that big open area where the code resides. It's like being confronted with a blank sheet of paper and being told to just make something up. At first, it may be hard to see the connection between this and the work you've been doing in Access until you break it down into its different parts.
The VBA environment is referred to as an Integrated Development Environment (IDE) because it contains all of the tools in this one place that you need to write, compile and debug the code. Other programming systems might require you to edit the code with one program and then compile it into an EXE file or other executable form with another command line program. VBA does it all in one place.
There are a couple of ways to open
the VBA environment. The simplest way is to press ALT-F11 which will bring up
VBA from anywhere in Access. If a form event already has code assigned to it, you
can also click on the Build button (
) next to the event on the form Properties
listing. This will go straight to the section of code that deals with the
event.
Project Explorer
The VBA Project Explorer window
In the standard environment screenshot shown above, you'll see two smaller windows on the left side of the screen. One of these sections is labeled "Project - Personnel". This is the name of the VBA project that is associated with the database. Inside the window, you'll notice a directory listing which shows the names of some of the database objects. In this case, it's two of the major forms and the two modules. The reason these are listed is because these objects have code associated with them that can be edited in the VBA environment.
Notice that this directory does not contain tables or queries. This is because those objects cannot have VBA code associated with them. Only forms and reports have events that can be programmed. Standalone modules, shown underneath the forms in the screenshot, are independent collections of methods and functions that you can create. The code in these modules can be called by forms and reports or by other modules. They're like reference libraries that can hold code that doesn't specifically apply to any of the other objects or that you want to make available to the entire application.
Also notice that not all of the forms in the database are listed in this directory. That's because while all forms are able to contain VBA code, not all of them do. Each form and report has a property that you can set when you view the item in Design View. This property, Has Module, determines whether the form has code behind it. For most of the forms and all of the reports in the demonstration database, this property is set to No as those objects don't require code at this time and eliminating the modules behind them reduces the size of the database. If the Has Module property is already set to Yes, you can set it to No but this will delete any code that exists in the module.
The code modules behind forms are similar to the standalone modules but they are inseparable from their forms and supply instructions for the form events rather than general functions.
As an exercise, try this:
- Switch back to Access using the ALT-TAB keys and press F11 to bring up the database window.
- Select one of the reports such as rptMailingLabels and open it in design view.
- Press F4 to bring up the properties dialog and select the All tab.
- Way down almost at the bottom of the properties, you'll see the Has Module property. Double-click where it says No to change it to Yes or just type Yes in the box.
- Close the report, clicking Yes when you're asked to save it.
- Now go back to the VBA environment and you'll see that the report has now been added to the directory because it now has a module that can hold programming for its events. If you double-click on the object in the directory, it will bring up the blank module in the code window.
Properties Window
The VBA Properties window
This window shows the properties associated with the current object that you're editing. If you're viewing the module behind a form that is open in design view, you'll see a long list of properties in this window, much like the properties dialog in Access. If you start editing the code for a form that is not currently open in Access, Access will automatically open that form in design view and the properties list will become available in VBA. This list includes the events for the forms and the window is a handy way to reference and change the properties for a form or report while you make changes to the code. As you learn VBA, you'll find that many of these properties can be set through code as the program is running (referred to as run-time) as well as at design time. The properties window shows how the properties are set by default before the code affects them.
Code Window
There are three parts to the code window with the main one being, obviously, the code editor itself. At the top of the editor are two dropdown boxes. The General dropdown on the left lists all of the controls on the form or report that can be programmed such as command buttons and text fields. After selecting one of these objects, the dropdown on the right will list the events that can be programmed for that object.
As an example, if you're using the
demonstration database, in the code module for frmEmployeeMain, select cmdAddNew from the controls dropdown. You'll see
that the right-hand dropdown automatically changes to the Click event since this
is the default event for a command button. If you open the events dropdown,
you'll also notice it's the only event shown in bold since it's the only event
with code attached to it. When you selected the cmdMain button from
the controls dropdown, the cursor in the code editor window immediately moved to
the related section of code. This can be a quick way of navigating
through a large module.
Additional Windows
Although they're not shown by default and you might not use them right away, there are a few extra windows that you can show when writing code. These windows are often used while testing the code or tracking down errors. You can select them for display from the View menu in VBA.
- Immediate Window - This window is very useful for testing formulas and seeing values as they exist in the code. It can also be used by the program to display custom messages for the programmer related to activity by the program.
- Locals Window - The Locals window shows all the variables that have been declared in the part of the program that is being run and their current values.
- Watches Window - Sometimes when you're testing a program, you want to know when a specific value changes. This is when you tell VBA to watch the value and report any changes. This window shows the watches that have been requested and their current status.
I'll include more details on the VBA environment in upcoming chapters. Meanwhile, you should take some time to explore the IDE and get comfortable with it. You'll be spending a lot of time there.
