Microsoft Office Access 2007 All-in-One Desk Reference For Dummies

Alan Simpson, Margaret Levine Young, Alison Barrows, April Wells, Jim McCarter

• Publisher: For Dummies
• Year: 2011
• ISBN:
  9781118050521
• Edition: 1
• Topic: Computer Science
• Subtopic: Databases
• Index: Computer Science

• Updated to cover all the latest features and capabilities of Access 2007, this resource provides new and inexperienced Access users with eight task-oriented minibooks that cover begininning to advanced-level material
• Each minibook covers a specific aspect of Access, such as database design, tables, queries, forms, reports, and macros
• Shows how to accomplish specific tasks such as database housekeeping, security data, and using Access with the Web
• Access is the world's leading desktop database solution and is used by millions of people to store, organize, view, analyze, and share data, as well as to build powerful, custom database solutions that integrate with the Web and enterprise data sources

Book VIII

Programming in VBA

Chapter 1: What the Heck Is VBA?

In This Chapter

Understanding Visual Basic for Applications (VBA)

Using the Visual Basic Editor

Discovering code as you go

V isual Basic for Applications — often abbreviated VBA — is a programming language you can use to extend the functionality of Microsoft Access and other products in the Microsoft Office suite of programs. A programming language is a means of writing instructions for the computer to execute (perform). Programmers often refer to the written instructions as code because the instructions aren’t in plain English. Rather, they’re in a code that the computer can interpret and execute.

You can create sophisticated Access databases without using VBA at all. In most cases, the other objects offered by Access — tables, queries, forms, reports, and macros — offer more than enough flexibility and power to create just about any database imaginable. But once in a while, you come across a situation where you want to do something that none of those other objects can do. That’s where VBA comes in. If you can find no other way to accomplish some goal in Access, writing code is usually the solution.

Finding VBA Code

So what the heck is VBA code, anyway? To the untrained eye, VBA code looks like gibberish — perhaps some secret code written by aliens from another planet. But to Access, the code represents very specific instructions on how to perform some task.

Within any given database, Access stores code in two places:

Class modules (Code-Behind Forms): Every form and report you create automatically contains a class module (also called a code-behind form), as illustrated in Figure 1-1. The class module for a given form or report is empty unless you place controls that require VBA code on that form or report.

Figure 1-1: Every form and report has a class module behind it.

Standard modules: Code can also be stored in standard modules. Code in standard modules is accessible to all objects in your database, not just a single form or report.

Opening a class module

If you want to view or change the code for a form or report’s class module, first open, in Design view, the form or report to which the module is attached. Then click the View Code button, shown near the mouse pointer in Figure 1-2.

Figure 1-2: The View Code button.

You can also get to a class module from the Event tab of the Property sheet in the Design View window. The Property sheet allows you to zoom right in on the VBA code that’s associated with a given control. For example, some controls contain code created by wizards. When you click such a control and then click the Events tab in the Property sheet, the property value chose [Event Procedure]. When you click [Event Procedure], you see a button with three dots, like the one near the mouse pointer in Figure 1-3. That’s the Build button. Click it to see the code that executes in response to the event.

Figure 1-3: Look for the code that executes in response to the event.

To write custom code for a control, select the control in Design view, open the Property sheet, click the Event tab, click the event to which you want to attach some custom code, click the Build button, and then choose Code Builder.

After you open a module, you’re taken to an entirely separate progra...