Introduction to Access VBA

Like many professional RDBMS (Relational Database Management Systems), Microsoft Access comes with its own programming language called VBA. VBA, or Visual Basic for Applications, is a subset of Microsoft's popular enterprise programming language Visual Basic. VBA follows the Visual Basic language syntax and comes with many of its common features such as an integrated development environment (IDE) and many common controls for building professional event-driven and data-driven applications.

Though VBA supports the look and feel of Visual Basic, it is not Visual Basic. A main difference being that Visual Basic allows for creation of executable programs, whereas VBA does not. Moreover, VBA for Access is specifically designed for Microsoft Access. Meaning, it has knowledge of and support for the Microsoft Access object model. The concept of an object model is different for each Microsoft Office application. For example, both Microsoft Excel and Microsoft Word support VBA, but each has its own object model.

The Event-Driven Paradigm

The event-driven paradigm is a powerful programming model that allows programmers to build applications that respond to actions initiated by the user or system. Access VBA includes a number of events that are categorized by the objects they represent. VBA programmers write code in event procedures to respond to user actions (such as clicking a command button) or system actions (such as a form loading).

To demonstrate the event-driven model, consider a form, which has a corresponding Form object that contains many events such as Click, Load, and MouseUp. As seen next, both Click and MouseUp events are triggered by the user performing an action with the mouse on the form.

Private Sub Form_Click()

'write code in here to respond to the user clicking the form End Sub

Private Sub Form_MouseUp(Button As Integer, _ Shift As Integer, X As Single, Y As Single)

'write code in here to respond to the user releasing a mouse button End Sub

I discuss the details of these event procedures soon enough. For now, understand that objects have related events, which can be triggered by users. You, the VBA programmer, write code in these event procedures to respond to user actions. Moreover, events can be triggered by the system or the program itself. For example, the Load event seen next is triggered when a form's Form object is first loaded into memory.

Private Sub Form_Load()

'write code in here to respond to the form loading into memory End Sub

If you're new to event-driven programming, this may seem a bit awkward at first. I promise you, however, it is really not that difficult. In fact, VBA does a great job of providing much of the detail for you. By the end of this chapter, you will be writing your first Access VBA event-driven programs with ease.

Object-Based Programming

The key to programming in VBA is using objects. Objects have properties that describe the object and methods, which perform actions. For example, say I have an object called Person. The Person object contains properties called HairColor, Weight, Height, and Age that describe the object. The Person object also contains methods that describe an action the object can perform such as Run, Walk, Sleep, and Eat. As you can see, understanding the concept of objects is really quite simple!

Many Access VBA objects also contain data structures called collections. In a nutshell, collections are groupings of objects, which you are introduced to in this chapter.

Access VBA supports many objects such as the Form object, which is simply a window or dialog box. The Form object contains many properties such as Caption, Moveable, and Visible. Each of these properties describes the Form object and allows VBA programmers to set characteristics of a user's interface. Like the object Person, the Form object contains methods such as Move and Refresh.

Many objects share common characteristics such as properties and methods. To demonstrate, the Label object (which implements a label control) shares many of the Form properties such as Caption and Visible.

Properties and methods of objects are accessed using the dot operator (.) as demonstrated in the next two VBA statements.

Labell.ForeColor = vbBlue

Labell.Caption = "Hello World"

Don't worry about the details in the previous statements for now, but do realize that properties such as ForeColor and Caption belong to the Labell object and they are accessed using the dot operator. I discuss this in more detail in sections to come.

0 0

Post a comment