Chapter 2: Introduction to Event-Driven Programming | Beginning Access 2002 VBA (Programmer to Programmer)

Overview

This chapter introduces a key concept in VBA programming. Indeed, the concept of event-driven programming is one that is central to all Windows programming, whether it is in low-level languages such as Visual C++ or higher-level languages such as Visual Basic for Applications (VBA) or Visual Basic Scripting Edition (VBScript). In essence, when we say that Access is an event-driven application, what we mean is that nothing happens in Access unless it is in response to some event that Access has detected .

In itself, that should not be a very difficult concept to grasp. After all, most of us are event-driven in the way that we behave. The phone rings, so we answer it. It gets dark, so we switch the lights on. We feel hungry, so we buy some food. That's event-driven behavior at its simplest.

These examples all have something in common, which characterizes the way that event-driven programming works:

An event occurs The events that occurred in the three examples above are the phone ringing, it getting dark, and us feeling hungry. Events are sometimes triggered by external forces (such as a visitor ringing the doorbell), and sometimes they are triggered by an internal change of state (we start to feel hungry).
The event is detected by the system We are only concerned about events that can be detected. For example, if a phone three blocks away rings we are not bothered, either because we cannot hear it or because we know that it is someone else's responsibility to handle the phone call. It is like that in Windows programming. Windows applications can only respond to events if they can detect them, and they will only handle events that they know are their responsibility to handle.
The system responds to the event The response can take the forms of various explicit actions, such as turning on the light or buying a burger. Alternatively, the system can respond to the event in a less dramatic fashion, by incrementing a counter which keeps track of how many times the event has occurred. In fact, the system can acknowledge the event and choose to do nothing. The important thing to note is that once the system has carried out its predefined response to the event, the event is said to have been handled and the system can go back to its dormant state, waiting for the next event to occur.

Now that's fine as far as we are concerned, but how does that help us with Windows programming in general and Access VBA programming in particular? What are the events that arise in Windows and in Access? And what actions do Windows and Access come up with in response to those actions?

Examples of Events

Here is an item that should be familiar to all of us. It is the Windows Desktop.

Whenever we start one of the Windows family of operating systems we will see something similar to this. So what do we do next? Well, that depends on what we want to achieve. Let us suggest that we want to delete the documents in our Recycle Bin. So we right-click the icon representing the Recycle Bin and choose Empty Recycle Bin - a seemingly simple enough event. Only it is not quite as simple as all that. When we look more closely we will see that there are a number of events and responses underlying this seemingly simple action:

Move the mouse pointer This event is detected by Windows, which responds by repainting the mouse pointer at the new location on the screen. In fact, this event occurs repeatedly and Windows responds repeatedly until the mouse comes to rest over the Recycle Bin.
Right-click the mouse button over the Recycle Bin This event is also detected by Windows and the response is two-fold. First, the Recycle Bin icon is shaded to indicate that it has been selected. Second, a popup menu specific to the Recycle Bin is displayed at the location of the mouse pointer.
Move the mouse to the Empty Recycle Bin menu item Notice how Windows again detects the mouse moving and responds by repainting the mouse pointer. This time, however, because the mouse is over a menu, the menu is repainted to highlight the menu items over which the mouse passes .
Click the Empty Recycle Bin menu item The popup menu item detects that the Empty Recycle Bin menu item has been selected, and in response displays a dialog box asking whether we really want to delete the items in the Recycle Bin. At the same time, it hides the popup menu.
Move the mouse to the dialog box Again, Windows detects the mouse movement and repaints the cursor.
Click the Yes button The message box determines that we have clicked the mouse button over the Yes button and responds by causing the Recycle Bin to delete all items within it. In addition, the Recycle Bin icon is changed to an image of an empty trashcan.

Notice that in every case, the same three steps occur:

An event occurs
The event is detected by the system
The system responds to the event

How Windows Handles Events

So far, we have looked at what events are and we have seen an example of a typical series of events and responses. But, before we go any further, let's have a look in a little more detail at what was happening when we emptied the Recycle Bin. Just who or what was detecting the events? And what is it that determines which action is carried out in response to the event?

Whenever Windows detects that an event has occurred - the mouse may have been moved or a mouse button clicked - Windows generates a message and directs it to the specific window to which the event relates . So, when the user clicks the mouse over the Yes button on the Confirm File Delete dialog box, Windows generates a message and directs it to the Yes button. Windows can direct messages to windows or controls either by sending them directly or by placing the message in a message queue and marking it with the handle (identifier) of the window or control the message is destined for.

The type of message generated will be different for every type of event that Windows detects. For example, when Windows detects that the mouse has been moved, it generates one type of message. And when it detects that the user has clicked a mouse button, it generates a different message. All of these messages, irrespective of their type, are automatically generated by Windows and directed to the window to which the event relates. It is then up to the window that receives the message to decide how to handle the event indicated by the message it received.

When the window receives the message it can carry out some particular action in response to the message - such as displaying a dialog box. Alternatively, the window can decide that it does not need to take any specific action. In this case it can elect for the default action for that event to be carried out - the default action is what Windows does if we haven't told it to do anything different. In fact, most windows will only process a few messages specifically and will pass the rest of the messages back for the system to handle with the default action.

So how does this help us? Well, a few more things should have become apparent:

The Windows operating system automatically detects when events occur
The operating system automatically notifies windows or controls as events arise which relate to them
If the window decides it does not want to do anything special, it can simply pass the message back to allow the default response to occur

The implication of all this is that as programmers, we can limit ourselves to writing code that handles specific events in a specific manner. We don't need to bother with the hassle of detecting events - Windows does that for us. And we don't need to write code to handle events where we want our windows or controls to react in the default manner.

We can see this if we take a look back at the form we created in the previous chapter.

Try It Out-Examining Event Properties

Open the IceCream.mdb database and, if necessary, press F11 to display the database window:
Now open the frmCompany form in design view by right-clicking it and selecting Design View :
Select the cmdFirst button ( First) at the bottom of the form and display the Properties window. You can do this by choosing Properties from the View menu, by right-clicking the command button and choosing Properties, or by selecting the command button and depressing the Properties toolbar button:
Select the Event tab on the Properties sheet. This will display all of the events that can occur for the selected command button. There are quite a few, aren't there!

Twelve different events that we can handle for a single command button! As it happens there is only one event that we are concerned with. We want to carry out a specific action when the user clicks the button. We don't care what happens when the mouse moves over the button; we don't care what happens when the user presses a key when the button is selected; all we care about is what happens when the button is clicked. That is why we have specified a macro ( macFirstButton ) to be run when the Click event occurs. If we had wanted an action to occur whenever the user moved the mouse pointer across the button, we would have specified a macro to be run when the MouseMove event occurs and so on.

Two important characteristics of event handling in Access that we examined a little earlier - characteristics that are major contributing factors in making Access such an excellent tool for rapid design and prototyping - are worth repeating here. The first is that the programmer does not need to determine when a particular event happens. We don't have to work out when the user clicks one of our buttons or when the user moves the mouse pointer over it. Windows automatically detects the event and the operating system notifies Access, which in turn causes the specific event to be raised, so executing the code or macro that the programmer has specified in the property sheet for that particular event.

The second noteworthy characteristic of this method of event handling is that the programmer need only supply macros or code for those events that the programmer wants to be handled in a 'non-default' manner. For example, clicking on a button causes the appearance of the button to change to reflect the fact that it has been depressed, whether or not we supply our own event handling macro or code. We don't need to worry about that because it is a type of default behavior common to all buttons. So for the cmdFirst button, all we need to worry about is the code or macro that is required to move the form to the first record.

Some Definitions

We have introduced a few new terms in this chapter and it is probably worthwhile clarifying just what they mean. They are terms that we will be using throughout the rest of the book, so it is as well to make sure that we all know what we are talking about!

An event handler is any VBA code (or macro) that the programmer constructs to be executed when a specific event occurs for a specific object. In the example above, we specified that the macFirstButton macro should be the event handler for the Click event of the cmdFirst button.

An event property is the mechanism that Access provides to allow us to attach an event handler to a specific event for a specific object. These event properties are exposed on the Event tab of an object's property sheet (kind of logical, huh!). In the example above, we can see that our command button has 12 event properties. That means that there are 12 different events that we can choose to handle through VBA code or macros. It is worth noting that the same event handler can, in fact, be assigned to many different event properties on many different objects.

The same types of object will always have the same number and type of event properties. So, every command button that you place on a form in Access will have the same 12 event properties that are listed in our example above. Event properties often start with the word On and their names reflect the event they allow the programmer to handle. So, the On Click event property is where we specify the VBA code or macro that we will use to handle the Click event. The On Mouse Move property is used to handle the MouseMove event, and so on.

So, to recap terminology, in our earlier example we can see that the cmdFirst button has a Click event. We can write a macro or piece of VBA code to act as an event handler for that event. And we specify that the macro or VBA code is the event handler for the Click event, by entering its name in the On Click event property for the button.

So Many Events

Reading through the previous paragraphs, you might well have thought "Whaaaat? 12 events for a measly little button!" VBA gurus call this a rich programmatic interface. Most other folk call it downright scary It can be daunting when you start programming and are faced with a vast number of events to choose between. Sometimes it is obvious which to use. We want our form to move to the first record when the user clicks the button, so we use the Click event. But it is not always that straightforward. For example, report sections have an On Retreat event property. What's that all about? And what is the On No Data event property of a report used for?

Well, you don't have to worry. In the first place, there are excellent reference materials available in the shape of Access Help. And secondly, you'll find that there are a few core events that you will use time and time again, and you will soon become familiar with what these are. For example, let's look at command buttons again. Although there are 12 possible events, you will soon discover that for (at least) 99% of the time, you will only ever need to handle the Click event. Why? Because that's what people are used to. Sure, you could write an elegant and highly inventive handler that makes use of one or more of the other events. But bear in mind that users, like horses, scare easily. They are not used to things happening when they move their mouse over buttons. They are used to things happening when they click buttons. Buttons are to be clicked - no more, no less. Please don't misinterpret this as either an attack on the intelligence of users or an attempt to limit the creativity of developers. It is just that people are used to interacting with the Windows interface in a certain manner. They expect buttons just to sit there and wait to be clicked; they don't expect strange things to happen when you move the mouse over them. A user's expectation of how your application will behave is a very powerful force. Use it to your advantage, make your application conform to the standards of Windows interface behavior and you will find that the task of getting users to feel comfortable with your application will be ten times easier than if you attempt to surprise them with cool new methods of interaction.

That is (more than) enough on interface design considerations. If you want to know more about this subject, an excellent reference is About Face by Alan Cooper (IDG, ISBN: 1-568843-22-4). Alternatively, you could consult the Windows Interface Guidelines for Software Design, which is available online on the Microsoft Developer Network (MSDN) web site.

Default Events

One of the helpful things about the way that Access exposes events to programmers is that each object has a default event. The default event is the event that Microsoft believes programmers are most likely to want to use when handling events for that type of object. For example, the default event for command buttons is the Click event. That is obviously because the event we are most likely to want to handle when using command buttons is the Click event. By way of contrast, for a textbox the default event is the BeforeUpdate event. This is because Microsoft believes that, as programmers, we are more likely to want to write a piece of VBA code or macro to handle the BeforeUpdate event than any other event that occurs with textboxes.

In fact, we have come across default events before. In the previous chapter, when we wanted to attach a macro to the cmdNext button, we did so by right-clicking the button in design view and selecting Build Event from the popup menu.

This brought up the Choose Builder dialog that allowed us to select the technique we wanted to use to write the logic that would handle the button's behavior. We selected Macro Builder , built a macro and when we closed the macro window, Access entered the name of the macro into the On Click event property:

Right-clicking an object and selecting Build Event will always create an event handler for the object's default event. If, instead, we want to create an event handler for a non-default event, we have to do it through the object's property window. For example, if we wanted to create a macro to handle the MouseMove event of the cmdFirstButton command button, we could type the name of a saved macro directly into the property sheet for the button's On Mouse Move event:

Alternatively, we could click in the event box and then click the builder button (the one with the ellipsis or three dots) to the right of the On Mouse Move event in the buttons Property window. This would allow us to invoke the Macro Builder to build a macro to handle the MouseMove event:

One Action, Many Events

Something else to bear in mind when deciding which event to handle is the fact that what seems like a single action may actually result in a number of different events being triggered. For example, let's consider what happens when a user has changed the text in a textbox ( txtFirst ) and then hits the Tab key to move to a new textbox ( txtSecond ). The single action of hitting the Tab key will trigger the following events in order:

Which event you decide to handle depends very much on just what you want to do. For example, the BeforeUpdate event occurs after the text in the text box has been changed on screen but before the value is actually saved. As such it is an excellent candidate for holding validation rules that can check the validity of what has been entered in the control before the value actually gets saved.

For a fuller listing of the order in which events happen as a result of common actions, type "Find out when events occur" in the Answer Wizard of the Access online help.

Handling Events in Access with VBA

So far then, we have looked at what events are and how we can use macros to perform certain tasks as those events occur. The example we have used is the frmCompany form that we created in the previous chapter. That form has five buttons on it, whose purpose is to allow the user to navigate through all of the records in the underlying table. Each of the buttons has a macro attached to its On Click property that determines which record the form will move to when the button is clicked:

You should also recall that this solution is not perfect. If we click the First button ( cmdFirst ) to move to the first record in the form and then click the Previous button ( cmdPrevious ), a dialog box tells us that an error has occurred, because Access tried to move to the record before the first record (and obviously there isn't one):

What we decided we needed was some intelligent navigation buttons - buttons which knew what record the form was on and therefore buttons which could enable and disable themselves depending on whether the actions they represented were valid at that point in time. That degree of intelligence simply isn't possible with macros. Macros are great for automating very simple tasks, but they just don't have the flexibility to allow us to use them for anything too sophisticated.

The alternative to using macros is to write your event handlers in VBA. In fact, you will find that in almost all professional Access applications VBA is used for event handlers. Some of the reasons for this were mentioned in the previous chapter but, to recap, the most important are as follows :

VBA allows you to add more complex logic to your event handlers
VBA allows you to execute a more varied selection of actions than are available through macros
VBA allows you to extend the functionality of Access by making use of other components or applications, such as Excel or Word, through automation
VBA code executes faster than the equivalent macro actions
VBA code is portable between any applications that support VBA, whereas macros are proprietary to Access
VBA allows you to trap errors and handle them gracefully
VBA code is easier to read and print out than macros

Put simply, VBA gives you more control. So, let's waste no more time and rewrite our event handlers in VBA code.

Try It Out-Writing VBA Event Handlers

If you haven't done so already, open the IceCream.mdb database and, if necessary, hit F11 to display the database window:
Now open the frmCompany form in design view by right-clicking it and selecting Design View :
Select the cmdFirst button at the base of the form and display the Properties window. You can do this by choosing Properties from the View menu, by right-clicking the command button and choosing Properties, or by selecting the command button and depressing the Properties toolbar button.
Select the Event tab on the Properties sheet. Notice that the event handler for the Click event is the macFirstButton macro:
Select the On Click event property and delete the text macFirstButton . Note that by doing this we are not deleting the actual macro. We are simply instructing Access that the macro is no longer to be used as the handler for the Click event of this button.
Click the Builder button (the one with the ellipsis or three dots) to the right of the On Click event property. This will display a dialog box asking us how we want to build our event handler. We want to use VBA code rather than macros, so select Code Builder and hit the OK button:
Clicking OK opens up the VBA integrated development environment (IDE). A new window should appear looking something like this:

The first line Option Compare Database determines the method for comparing text strings. The default is fine for us so just ignore it for now.

The last two lines of code shown in the window in the above form is the VBA event handler for the Click event of the cmdFirst button. These are automatically entered by VBA when you decide to use the Click event of cmdFirst . The event procedure (that's another term for an event handler written in VBA) starts with the line:
```
   Private Sub cmdFirst_Click()   
```
and ends with the line:
```
   End Sub   
```
Everything that goes between these two lines will be executed whenever the cmdFirst button is clicked. At the moment there is nothing there, so nothing beyond the default action (the button appears to be pressed down) will happen when the button is clicked.
Type the following line of code in the event procedure, making sure that you type it exactly as it appears below, including the two commas:

Don't worry if some strange popups (looking a bit like tooltips) appear while you are typing the line of code. They are designed to help you to write code more efficiently but you can simply ignore them if you prefer. These two features - Auto List Members and Auto Quick Info - are discussed a little later in this chapter. Microsoft calls this Intellisense technology and you will soon find it is an indispensable tool which aids in Rapid Application Development.
Now switch back to Access by hitting Alt + F11 . The words [Event Procedure] should now appear against the On Click property of the cmdFirst button. This indicates that the Click event for this button is now being handled by VBA code:
Close the Properties window, switch to Form View and move to the second record by hitting the Next button. Then click the First button. If you've done everything correctly, you will be able to use the button to navigate back to the first record on the form. Congratulations! You have written your first VBA procedure!

How It Works

OK, so let's have a look in more detail at the code you typed in:

 DoCmd.GoToRecord , , acFirst

It's really quite simple. We'll look at each part in turn.

The DoCmd at the start of the line indicates that we want Access to carry out an action . In other words, we want Access to do something that we could have performed using either the keyboard (or mouse) or a macro. The action that we want to carry out is the GoToRecord action. This is what would happen if we selected Go To from the Edit menu when the form's open in Form View . You may remember from the last chapter that this is also the name of the action we selected in the macro that was originally behind this button.

Once we've specified the action, we need to tell Access which record we want to go to. This is the purpose of the constant acFirst - the record we want is the first record. We'll be looking at constants in detail in the next chapter. For now you should simply be aware that the constant is a useful named placeholder for a value that is set somewhere else in the program or internally within VBA.

The two commas before acFirst indicate that there are additional optional arguments we could have supplied to make our code more specific. In this case, we could have specified the type of object we want to move within (in our case a form) and the name of the object, frmCompany . So, we could have written the code like this:

   DoCmd.GoToRecord acForm, "frmCompany", acFirst

If we omit these two arguments, Access will assume that we mean the current object. This is fine in our case, so we can leave the optional arguments out. However, we still have to insert a comma to indicate where arguments have been omitted. So that's how we end up with the line of code:

 DoCmd.GoToRecord , , acFirst

If arguments are still a mystery to you, don't worry, as we will be covering them in more depth in the next chapter.

What are Actions and Methods?

At this stage you might be getting a little confused over just what the difference is between actions and methods. After all, there is a GoToRecord action (which we used in the original macro) and a GoToRecord method (which we used in VBA) and they both do the same thing. What's the difference?

Actions are the building blocks of macros . Many of them correspond to tasks carried out by the user by selecting items from a menu. Others allow you to perform different tasks that a user can't, such as displaying a message box or making the computer beep. The main thing to remember, however is that actions occur in macros.

Methods , however, occur in VBA . A method is used to instruct an object to behave in a certain way and we'll look at the idea of objects and methods in more detail in Chapter 5. Because you cannot use actions outside of macros, you use methods to achieve the same ends in VBA. There are two objects whose methods you use to perform almost all of the macro actions in VBA. These are the Application object and the DoCmd object.

So, if we want to write a VBA statement that performs the same function as the Quit action in a macro, we would use the Quit method of the Application object:

   Application.Quit

In fact, the Application object is the default object, and refers to Access itself, so we can omit it and simply type this as our line of code:

   Quit

Defaults are a useful way to abbreviate your code and to speed up the writing process. Only use defaults where you are sure of their meaning, however. Assuming the wrong default will lead to errors that may prove very difficult to track down - if in doubt use the fully qualified version.

Almost all of the macro actions, however, correspond to methods of the DoCmd object. So if we want to write a line of VBA that does the same as the Beep macro action, we would write this:

   DoCmd.Beep

To find out the VBA equivalent of a macro action, simply look up the action in the Access help. If there is an equivalent VBA method, it will be described on the help page for the action.

Why is the Code in Different Colors?

One of the nice things about VBA is colored code. This is not just a pretty device. The different colors are used to distinguish the different components of code and can make your code easier to read. You can set these colors yourself on the Editor Format tab of the Options... dialog on the Tools menu in the VBA IDE:

The colors really can make it easier for you to read and understand the code. For example, you can alter the color of the line of code that is due to be executed next. This makes it easier to see what is happening when you step through your code one line at a time. Or you can choose to have all your comments in gray so that they don't appear too intrusive .

Stepping through the code by running it line by line is useful for debugging the code. This is covered later in the book, in Chapter 12.

You can also use different colors to distinguish between the different types of word you use in your code. Keywords - reserved words, such as DoCmd , which always have a special meaning to Access - can be in one color, and identifiers - such as the names of forms you have created or messages you want to display - can be in another color. This can make it easier to understand what your code (or someone else's code!) is doing.

What were all those Popup Thingies?

Good question! Before you have finished typing even half a word you may find VBA trying to butt in and finish the job for you. It all looks a bit disconcerting at first but, once you get used to the way it works, it can make your code both easier to write and less prone to errors. The proper terms for these popups are Auto List Members and Auto Quick Info . We'll look at them now in more detail. As we go through this over the next few pages, it would be worth your while to keep the VBA IDE open, and just have a play around to become accustomed to how these pop-ups operate .

The Auto List Members and Auto Quick Info features can be turned on and off via Tools/Options.../Editor on the menu of the VBA IDE. Make sure that these options are checked if you want to observe the behavior described below.

The Auto List Members feature of the VBA IDE suggests a list of all the valid words that can come next in your VBA code. (More specifically, it lists the relevant methods, properties, events, members or constants. We'll be looking at what these terms mean in the next few chapters.) You can see this at work if you type the phrase DoCmd. in VBA. As soon as you have done so, VBA suggests a list of possible methods that could come next:

All of the words in the popup list box are valid methods that can follow the DoCmd object. We saw earlier how the DoCmd object allows us to carry out the same actions we can perform in macros. Well, once we have decided that we want to use the DoCmd object, the popup lists all of those actions for you.

You can either select an item from the list by double-clicking it or by hitting the Tab key, or the spacebar. Or if you want you can carry on typing your code. If you do carry on typing your code, VBA will highlight the word that matches most closely what you are typing.

The other popup you may have seen is displayed by the Auto Quick Info feature. This one helps you to remember the syntax of difficult-to-remember commands. You'll see it whenever you type the name of a recognized function or sub procedure in VBA. So, in our example above, once you had typed DoCmd.GotoRecord , VBA displayed the Auto Quick Info popup to help you with the rest of the command:

The popup window acts as a memory aid, to remind you what arguments you need to type in after DoCmd.GoToRecord . The next argument you need to type in is always highlighted in bold, and any optional arguments are displayed inside square brackets.

In the example above, the next argument we should type in is the ObjectType argument. There is even an Auto List Members popup behind the Auto Quick Info popup to list the possible values you can type in for the ObjectType argument! Since the ObjectType argument is shown in square brackets, we can ignore it, as it is an optional argument. However, we still need to type a comma to acknowledge the fact that we have omitted the optional argument.

Typing the comma causes VBA to highlight the next argument in the Auto Quick Info popup:

Again the argument is optional, so we can ignore it and simply type a comma in its place. The highlight then moves to the third argument:

Even though the Record argument is optional, we want to use it, as this is the one that tells VBA which record we want to move to. And what's cool is that VBA displays an Auto List Members popup that lists the possible values we can supply for this argument. We can click acFirst and VBA inserts that argument in our line of code. The last argument, Offset , is also optional, so we can ignore it and just hit the Enter key to complete our line of code.

Auto List Members and Auto Quick Info aren't just gimmicks. They really help you to get your VBA code right the first time without needing to spend time looking through the Help, or other manuals. As such they are a great aid to productivity.

What if I Still Get it Wrong?

None of us are perfect! Even with Auto List Members and Auto Quick Info looking over our shoulder and telling us what to write we still make mistakes. Another way in which VBA makes the job of writing code a bit easier is that it will inform you if you have made a mistake in a line of code. For example, you might have mistyped the line of code as:

   Do Cmd GoToRecord , , acFirst

Then, when you tried to move off the line, VBA would have highlighted the line of code (and the word Cmd ) and displayed this dialog box:

This indicates that the word Do must always be followed by the words While or Until (we'll cover this in Chapter 4), or else it must appear on its own. Of course, we don't want the word Do - we want to use DoCmd , which is something altogether different. This type of error is called a syntax error .

However, if the error is less obvious, VBA may only be able to recognize the fact that it's an error when you try to run the code (when you click the button). This is called a run-time error and will result in a rather unfriendly dialog box being presented to the user when they click the button. For example, if you had missed out a comma and typed:

   DoCmd.GoToRecord , acFirst

VBA wouldn't have generated a syntax error when you moved off the line but, when the cmdFirst button was clicked at run time it would have interpreted acFirst as the second argument (rather than the third) and would have displayed the following dialog box:

Here, the constant acFirst has the value 2 within VBA, a value that the missing optional parameter should not have. Later in the book we shall be looking at how to prevent these run-time errors and how to handle them more gracefully.

The third type of error that you can create when writing code occurs when the code that you type in is syntactically correct, but does not produce the desired effect. In this situation, the program appears to function normally, but in fact doesn't perform as you intended. For example, you may have accidentally typed:

   DoCmd.GoToRecord , , acLast

This is a logic error - you had intended to type acFirst not acLast . It might take some time for you or the users of your application to notice that the cmdFirst button was moving the current record pointer to the last rather than the first record. You may also hear this type of error referred to as a semantic error because it changes the meaning of the code.

Compiling Code

You've seen how VBA checks for the more obvious errors as you type. However, there is another method that can be used to prevent errors. This is the process known as compiling , and is used to trap the less obvious errors that might crop up in your code. When your code is compiled, routine checks are performed - such as checking that variables have been declared (if Option Explicit has been set) and that procedures you call are named in your application. Compiling involves assembling the code in preparation for execution, but doesn't actually execute the code. Compiling can't catch all errors, but it will pick up general consistency problems in your code. If compiling doesn't produce any errors, then control is returned to you. You don't need to worry about this now, as we look at the issue of compiling code in more detail in Chapter 19, and variables and Option Explicit are covered in more detail in the next chapter.

Other Events

So where are we now? We have looked at what events are, and how fundamental they are to the operation of Windows and Access. We have also looked at how we can use macros and VBA to write event handlers. But so far we have only mentioned one or two events. We'll take a quick look at some of the more common events that can be handled within Access, what triggers them and how they can be useful to us. We've included a comprehensive list of the events that Access handles on the CD that accompanies this book. It should give you an idea of just how much you can achieve in Access through the careful use of event handlers.

Event Property	Belongs to...	Occurs...	Used for...
On Change	Controls on a form	after the contents of a control change (say, by typing a character).	triggering the update of related controls on the form.
On Click	Forms, Controls and sections on a form	when the user clicks the mouse button over a form or control; when the user takes some action which has the same effect as clicking (like pressing the spacebar to check a checkbox).	just about anything - this is one of the most used of all events, and is about the only event used with command buttons.
On Close	Forms, Reports	after a form or report has been closed and removed from the screen.	triggering the opening of the next form, and for "cleaning up" or checking entries on the current form.
On Current	Forms	when the form is opened or requeried; after the focus moves to a different record, but before the new record is displayed.	implementing intelligent navigation buttons (see example below) or "startup" code.
On Dbl Click	Forms; Controls and sections on a form	when the user depresses and releases the left mouse button twice over the same object.	selecting an item in a list and carrying out the actions of the OK button in one go.
On Delete	Forms	when the user attempts to delete a record.	preventing the user from deleting records.
On Dirty	Forms	after the user has updated any data in the current record but before the record has been saved.	determining whether you need to ask the user if any changes should be saved.
On Error	Forms; Reports	when a run-time database engine error occurs (but not a VBA error). We look at this in more detail in Chapter 13.	intercepting errors and displaying your own custom error messages.
On Mouse Move	Forms; Controls and sections on a form	when the mouse pointer moves over objects.	displaying X and Y coordinates of the mouse pointer.
On Mouse Up	Forms; Controls and sections on a form	when the user releases a mouse button.	detecting whether the user has a mouse button depressed when clicking an object.

The final column of the table is only intended to give an indication of the type of action that you can perform in the event handler. It isn't meant to be an exhaustive or comprehensive list of the uses of each event handler. If you feel a burning desire to use the On Delete event to start a video clip playing then you can if you really want to!

You will notice that the table lists event properties rather than events. Remember, an event property is a property that appears in the property sheet and allows you to handle a specific event. Therefore, the event handler for the Click event of a command button is exposed via the button's On Click event property.