.jpg "Microsoft Certified Professional")
I - Introduction
Some people will spend years using Microsoft Access and never bother with one of its most powerful features; Visual Basic for Applications (VBA). Some will learn how to use macros for simple automation and stop there. It's only when you learn how to use VBA, however, that you will understand what Access is truly capable of.
What is Visual Basic for Applications?
Visual Basic for Applications is a version of the Visual Basic language that has been included with Microsoft Office. When used with Office programs, VBA enables the user to design simple or complex routines that will run in the background and respond to events such as the opening of a form or the click of a command button.
Going back even further, Visual Basic for Applications is a a version of of the BASIC (Beginner's All-Purpose Symbolic Instruction Code) programming language. The language is called a high-level language because its commands and syntax are a few levels removed from the machine code that the computer actually processes. VBA uses English-like instructions which are then translated, or compiled, into instructions that the computer can use.
Reasons to learn VBA
There are two levels at which you can use Microsoft Access. The basic level is for simple database management where you create some tables to store your data or import data from other sources and then create some queries and basic reports to analyze it. Maybe you'll have some simple data entry forms to make things a little easier. If this is all you want to do, then you'll probably get by without knowing VBA.
The next level is for building professional database applications. These are programs like the employee demo database that are centered around the ability to store important information but also include a variety of functions and an interface that makes it easy for anyone to use those functions. Such an interface includes user-friendly forms for entering data and a menu system to navigate through the available forms and reports. To create something like this you will need to be familiar with how VBA can tie your program together and provide functions that macros just aren't capable of.
In an earlier part of the series, I stated that I almost never allow users to work directly with the tables because it's safer for the data and easier for the user to go through data entry forms. When I design an application, the entire interface and all of its functions from the command buttons to the navigation and any analysis that the program performs in the background are enabled with VBA. All the user sees is a smooth running application.
Command buttons are one of the simplest tasks that VBA can be used for.
Carrying out the instructions behind command buttons is one of the simplest things I do with the language. If you use the command button wizard to create buttons on your forms, it will write the VBA in the background. There are also VBA routines in the application that work independently of forms to oversee the application as a whole or to determine if a particular form is loaded. The main employee form in the demo database has VBA code behind it that maintains the form in the proper mode so that the data is protected from accidental editing.
All of these are still relatively simple routines. Some of the most advanced programs I've written manipulate entire database files and instances of Access itself. I wrote one routine that would enable an Access application to search for a newer version of itself. On finding the newer version, the program would shut down, perform the necessary file updates and then restart itself so the user could continue working.
The way Access combines a relational database with a programming environment enables you to design data-driven applications without being dependent on pre-programmed abilities of the software. Although Access is included with Microsoft Office, in some ways it's set apart from the rest of the suite by the user's ability to create solutions that are almost indistinguishable from normal stand-alone programs. Access also provides a great training tool for both database and programming concepts.
Event-driven programming
VBA is linked to Access through form and report events like the one shown here. A procedure is set to run when the command button is clicked. This is referred to as event-driven programming.
Access programs are referred to as event-driven because the forms and reports respond to events that are triggered, or fire, in response to actions by the user or another part of the program. This is true whether you use VBA or simple macros. This differs from other programming languages including earlier versions of BASIC where the code was all contained in a single listing and was run in sequential order. In VBA, code is stored in methods and functions which are designed to run when specific events fire, such as when a form is opened, or when called by other code. This allows for a very flexible program operation that can respond to the user's needs.
Another feature of event-driven programming is that multiple events may fire in sequence in response to a single action. On the main menu of the demo database, you'll see command buttons that activate different parts of the program. If you click on the button to bring up the main Employee form, it seems to open the form with a single action. Behind the scenes, however, it's firing a number of events:
Open ?? Load ?? Resize ?? Activate ?? Current
Each of these events refers to a different aspect of opening the form and displaying its data so that the user can interact with it. You can program any or all of these events so that the application will perform the necessary tasks exactly when needed. For example:
- The Open event can be cancelled so that the form does not open if there is no data to display.
- The Resize event fires any time the size of the form is changed, including when it opens, and can be programmed to prevent the resizing of the form or dynamically arrange controls to compensate for resizing.
- The Current event fires every time the user views a new record on the form and this can be useful for adjusting properties of controls based on the data in the current record.
If you look at the list of events under the form's Properties screen, you'll
see quite a few that relate to actions from the mouse, the keyboard and the
form itself. Although it may appear confusing at first, you'll find that
this gives you very precise control over the operation of your program.
Throughout this series, you will see code examples used to illustrate various functions. The main purpose of these examples is to demonstrate the function being explained and might not represent the most efficient way to accomplish a given task. While learning to work with Visual Basic for Applications, I encourage you to explore the functions you find here, in the Access help files and other places and take plenty of time to experiment with different ways of doing things.
The English-like commands of VBA and the ability to carry out tasks by assigning small amounts of code to specific events actually makes the language easier to learn. The integration of VBA with the form and report controls in Access provides you with a graphical environment where you can focus on how the code works with the flow of your application rather than just picking your way through a long code listing.
Still, you do have to be familiar with some commands and programming techniques which I'll be detailing in the following chapters. In the next chapter, I'll explain a few things about the VBA interface in which you'll be writing the code.
