Enhancing a Database with VBA

Office 2003 applications like Access, Excel, and Word share a common programming language: VBA. With VBA, you can develop applications that combine tools from these Office 2003 products, as well as other programs that support VBA. Because of the language's power and flexibility, programmers often prefer to use VBA over Access macros to customize their Access applications.

Introducing the Structure of VBA

VBA is an object-oriented programming language because, when you develop a VBA application, you manipulate objects. An object can be anything within your database, such as a table, query, or a database. Even Access itself is considered an object. Objects can have properties that describe the object's characteristics. Text boxes, for example, have the Font property, which describes the font Access uses to display the text. A text box also has properties that indicate whether the text is bold or italic.

Objects also have methods, actions that can be done to the object. Deleting and inserting are examples of methods available with a record object. Closely related to methods are events. An event is a specific action that occurs on or with an object. Clicking a form button initiates the Click event for the button object. VBA also refers to an event associated with an object as an event property. The form button, for example, has the Click event property. You can use VBA to either respond to an event or to initiate an event.

Writing VBA Code

Unlike Access macros, which are created in the Macro Design window, the VBA programmer types the statements, or code , that make up the VBA program. Those statements follow a set of rules, called syntax , that govern how commands are formulated. For example, to change the property of a particular object, the command follows the general form:

Object.Property = Expression

where Object is the name of a VBA object, Property is the name of a property that object has, and Expression is a value that will be assigned to the property. The following statement sets the Caption property of the Departments form:

Forms!Departments.Caption="Department Form"

You can use Access's online Help to learn about specific object and property names . If you want to apply a method to an object, the syntax is:

Object.Method arg1, arg2,

where Object is the name of a VBA object, Method is the name of method that can be applied to that object, and arg1, arg2 , ... are optional arguments that provide additional information for the method operation. For example, to move to page 2 of a multipage form, you could use the GoToPage method as follows:

Forms!Departments.GoToPage 2

Working with Procedures

You don't run VBA commands individually. Instead they are organized into groups of commands called procedures . A procedure either performs an action or calculates a value. Procedures that perform actions are called Sub procedures . You can run a Sub procedure directly, or Access can run it for you in response to an event, such as clicking a button or opening a form. A Sub procedure initiated by an event is also called an event procedure . Access provides event procedure templates to help you easily create procedures for common events. Event procedures are displayed in each object's event properties list.

A procedure that calculates a value is called a function procedure . By creating function procedures you can create your own function library, supplementing the Access collection of built-in functions. You can access these functions from within the Expression Builder, making it easy for them to be used over and over again.

Working with Modules

Procedures are collected and organized within modules . Modules generally belong to two types: class modules and standard modules. A class module is associated with a specific object. For example, each form or report can have its own class module, called a form module or report module . In more advanced VBA programs, the class module can be associated with an object created by the user . Standard modules are not associated with specific objects, and they can be run from anywhere within a database. This is usually not the case with class modules. Standard modules are listed in the Database window on the Modules Object list.

Building VBA Projects

A collection of modules is further organized into a project . Usually a project has the same name as a database. You can create projects that are not tied into any specific databases, saving them as Access add-ins that provide extra functionality to Access.

Using the Visual Basic Editor

You create VBA commands, procedures, and modules in Office's Visual Basic Editor . This is the same editor used by Excel, Word, and other Office applications. Thus, you can apply what you learn about creating programs in Access to these other applications.

The Project Explorer

One of the fundamental tools in the Visual Basic Editor is the Project Explorer. The Project Explorer presents a hierarchical view of all of the projects and modules currently open in Access, including standard and class modules.

The Modules Window

You write all of your VBA code in the Modules window. The Modules window acts as a basic text editor, but it includes several tools to help you write error-free codes. Access also provides hints as you write your code to help you avoid syntax errors.

The Object Browser

There are hundreds of objects available to you. Each object has a myriad of properties, methods, and events. Trying to keep track of all of them is daunting, but the Visual Basic Editor supplies the Object Browser , which helps you examine the complete collection of objects, properties, and methods available for a given object.

Show Me Microsoft Office Access 2003
Show Me Microsoft Office Access 2003
ISBN: 0789730049
EAN: 2147483647
Year: 2002
Pages: 318

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net