Usually, you’ll build code routines that connect to forms, and spring into action when specific events (Section 15.5) take place. However, in this chapter you’ll start a bit slower by creating a standalone code routine that runs only when you tell it to.

The first step’s to add a new module for your code. In the Visual Basic editor’s menu, choose Insert → Module. Figure 16-2 shows what you’ll see.

Figure 16-2. Once your project has at least one module, the Project window shows a Modules group. Access gives new modules boring names like Module1, Module2, and so on. To choose something better, select the module in the Project window, and then, in the Properties window just underneath, change the “(Name)” property. DataCleanupCode makes a good module name.

When you add a new module, the Visual Basic editor automatically opens a code window that shows the contents of that module. (If your database has more than one module, you can open the one you want by double-clicking it in the Project window.) Initially, a brand new module has just one line of code, which looks like this:

	Option Compare Database

This line’s an instruction that tells Visual Basic how to handle operations that compare pieces of text. Ordinarily, Visual Basic has its own rules about how to deal with text, but this statement tells it to use the Access settings instead.

The Access settings depend on the locale of the current database (like whether your version of Windows is using U.S. English or Japanese kanji script). Option Compare Database’s ultimate result’s that most English-speaking people case-insensitive comparisons. That means fudge is considered the same as fUdGe, which is the same way Access treats text when you write queries.

Before you write code that actually does anything, you should add one more instruction to the top of your code file. Just before (or after) the Option Compare Database instruction, add this:

	Option Explicit

This instruction tells Visual Basic to use stricter error checking, which catches common typos when using variables (Section 17.1).

As with other Access database objects, when you close the Visual Basic editor, Access prompts you to save any newly created modules. If you don’t want to wait that long, then choose File → Save [DatabaseName] where DatabaseName is the name of your database file.

Inside every module (except the empty ones) are one or more Visual Basic subroutines. A subroutine’s a named unit of code that performs a distinct task. In the VB language, subroutines start with the word Sub followed by the name of the subroutine. Subroutines end with the statement End Sub. Here’s an example subroutine that’s rather unimaginatively named MyCodeRoutine:

	Sub MyCodeRoutine()
	    ' Your code goes here.
	End Sub

This small snippet of VB code illustrates two important principles. First, it shows you how to start and end any subroutine (by using the Sub and End Sub statements). This code also shows you how to create a comment. Comments are special statements that Access completely ignores. Comments are notes to yourself (like explaining in plain English what the following or preceding line of code actually does). To create a comment, you just place an apostrophe (') at the beginning of the line.

Right now, MyCodeRoutine doesn’t actually do anything. To give it more smarts, you add code statements between the Sub and End Sub. The following exceedingly simple code routine shows a Message box:

	Sub MyCodeRoutine()
	    ' The following statement shows a message box.
	    MsgBox "Witness the power of my code."
	End Sub

This code works because the Visual Basic language includes a command named MsgBox. (See the box “The Visual Basic Language Reference” in Section 16.2 for advice on how to master all the commands you have at your disposal.) You use this command to pop up a basic Message box with a message of your choosing. The message itself is a piece of text (or string in programmer parlance), and like all text values in VB, it needs to be wrapped in quotation marks so Access knows where it starts and where it ends. (Access forces you to obey the same rules when you use text in an expression.)

Once you’ve typed this in (go ahead—try it!), you’re ready to run your code routine. To do so, place the cursor anywhere inside the subroutine so the Visual Basic editor knows what code you’re interested in. Now, on the Visual Basic toolbar, click the Run button (which looks like the play button on a VCR control), or, from the menu, choose Run → Run Sub/UserForm. Figure 16-3 shows the result.

Figure 16-3. Your first code routine isn’t terribly useful, but it does prove that you know enough to write a line of code and run it.

Access runs the code in a subroutine one line at a time, from start to finish. When you show a MsgBox, your code pauses until you click the OK button in the Message box, at which point it carries on and finishes the subroutine.

Instead of running code routines straight from the editor, Access fans wire them up to form events, just as you can with macros. Here’s how you can try this out:

The first time you add code to a form, Access creates a new module for that form. This module’s named after the form and placed in a special group in the Project window named Microsoft Office Access Class Objects (see Figure 16-4). If you add more code to the same form, whether it’s connected to the same control or another one, Access inserts the code routine in the existing module for that form.

Figure 16-4. The module for a form is always named Form_[FormName]. Here’s the module you’ll see for a form named OneButtonForm.

When you follow these steps, Access doesn’t just create a new module; it also inserts a new, empty subroutine for your event. Assuming your button was named ButtonOfPower, you’ll see code that looks like this:

	Private Sub ButtonOfPower_Click()
	End Sub

This subroutine looks similar to the subroutine you created earlier, but with two differences:

To try out your subroutine, you need to add some code. So far, you’ve learned only one code command, so try using that to show a message:

	Private Sub ButtonOfPower_Click()
	    MsgBox "You clicked the ButtonOfPower."
	End Sub

Now switch back to the form and click your button. You should see the message shown in Figure 16-5, which indicates that your code caught the event and responded successfully.

Figure 16-5. Events make code run automatically. In this case, if you click the ButtonOfPower, then Access instantly shows a message.

If you delete the ButtonOfPower later on, Access doesn’t remove your code. Instead, it stays there, inactive. This behavior’s good if there’s something really useful in that code that you might want to use somewhere else later on. (In this case, a little cutting and pasting can help you out.) But if it’s just a bunch of old code, use the Visual Basic editor to delete the subroutine as soon as you get the chance.

Based on what you’ve learned so far, you may wonder why you would ever bother to create an ordinary module by hand. After all, there’s no convenient way to run the code, and you can’t connect it to a control event in a form.

Ordinary modules become useful if you create a fantastically useful piece of code that you want to use in several different places. You could design the perfect search routine and use it in two, three, or four dozen different forms. And you could cut and paste the code into each subroutine that needs it. Except that duplicate code is always a Bad Idea (just like duplicate data). Why? Consider what happens if you need to fix a problem or add an improvement. With duplicate code, you’ll be forced to hunt down each copy of the code and repeat the same change. That’s a sure way to waste your holiday weekends.

The solution’s to take your useful, reusable code routine, and place it in a module. You can then call that code routine wherever you need it—and you can edit it just once when you need to update or fix it. To call a code routine in another module, you use the name of the module, followed by a period (.), followed by the name of the subroutine. Here’s an example:

	Private Sub ButtonOfPower_Click()
	    MyCodeRoutine
	End Sub

Here’s a play-by-play account of what happens when you use this code:

You can break a single task into as many subroutines as you want. You can also call subroutines that call other subroutines, which call still more subroutines, and so on. Access doesn’t care. All it’s interested in are the actual code statements.

If the ButtonOfPower_Click and MyCodeRoutine subroutines are both in the same module, there’s a bit of a shortcut. You no longer need to include the module name when you call MyCodeRoutine. Instead, you can use this code:

	Private Sub ButtonOfPower_Click()
	    MyCodeRoutine
	End Sub

Now Access assumes MyCodeRoutine must be in the same module, and it looks for it there. Also, in this case it doesn’t matter whether MyCodeRoutine is public or private—either way, your code can call it.

As you learned in Chapter 15, the most exciting macros are those that take charge of your forms and controls. In VB code, this task’s spectacularly easy. You just need to know the names of all the controls you want to work with.

Suppose you’re creating a (somewhat dangerous) code routine that clears a text box. You plan to use this routine to reset the text in the Description field. Here’s the line of code that does the job:

	Description = ""

This line’s a basic Visual Basic assignment statement (a line of code that modifies a piece of data), and it all revolves around the equal sign (=). When Access runs this line of code, it takes the content on the righthand side of the equal sign (in this case, an empty set of quotation marks that represents no text), and it stuffs that content into the receptacle on the left-hand side (in this case, the Description field). The end result’s that the current content in the Description field is wiped out.

Of course, you can also substitute a specific piece of text:

	Description = "Type something here, please"

The effect of running this code is just the same as if you typed in the new text yourself (except it happens a whole lot faster). As you know, when you modify any field, you place the current record into edit mode. As soon as you move to another record or close the form, Access commits your edit and saves your new values in the database.

There’s only so much you can do with fixed text values. After all, when you use a fixed piece of text, you need to decide at the outset exactly what you want to use. By the time you actually click a button and trigger your code, you may want something different. For that reason, programmers rarely used fixed values in this way. Instead, they use more complex expressions, which are a lot like the Access expressions you used for query calculations (Section 7.1) and validation rules (Section 4.3).

With text, you can use the & operator to create a large piece of text out of several smaller pieces. Here’s an example that takes the current description and adds a sentence at the end that identifies the product by name.

	Description = Description & " This is a description for " & ProductName & "."

If the Description starts off as “Enjoy delectable waves of fudge.”, it may end up being “Enjoy delectable waves of fudge. This is a description for Fudge Tsunami.”

	Description = Description & _ 
	  " This is a description for " & _ 
	  ProductName & "."

More commonly, expressions manipulate numeric or date values. Here’s the code for an IncreasePrice button that ratchets up a price by 10 percent every time you click the button (and best of all, you can click it as many times as you like):

	Private Sub IncreasePrice_Click
	    Price = Price * 1.10
	End Sub

For a review of the different operators you can use with expressions to perform different types of calculations (like addition, multiplication, division, and so on), go back to Section 7.1.2.

Properties aren’t anything new. After all, you’ve spent considerable time tweaking them with the Property Sheet to get just the right formatting and behavior (see Section 13.1.2). However, properties show a whole different side when you change them using your code. With code, you can modify properties dynamically in response to various actions (like clicking a button or editing the text in a text box). This technique opens up a world of new possibilities.

The secret to accessing the built-in features of an object is the lowly period (which programming nerds call the dot operator). Suppose you want to modify the background color of the Description text box. You can do this job by setting the BackColor property of the corresponding text box object. Here’s how it’s done:

	Description.BackColor = vbYellow

This line of code takes the Description object, and then uses the dot operator to pick out its BackColor property. The BackColor is then set with the help of a specially-created keyword, called vbYellow. As you saw with events, the name of a property in code doesn’t always match the name of the property in the Property Sheet. In code, property names never include spaces.

You can use this line of code in any subroutine in a form module, as long as that form really and truly has a text box control named Description.

If you don’t include the dot, then you end up using the default property. For the text box object, the default property’s Value, which represents the content of the field. That’s why you can write code like this, which doesn’t include the dot operator:

	Description = "A very fine product, indeed."

So now that you know that all the controls on your forms are objects with a whole range of useful details that you can change, the important question is: How do you know what properties there are, and how do you find the ones you want? Several guidelines can help you out:

Figure 16-6. When you type an object name, and then enter the period, Visual Basic pops up a list of choices. If you type a few letters, Visual Basic moves to the matching part of the list. If you see the property you want, then you can insert it by clicking it or pressing the Space bar.

Table 16-1 lists some control properties that you may want to manipulate with Visual Basic code.

	Price.BackColor = vbRed

	Forms("Product").Price.BackColor = vbRed

	Forms!Product!Price!BackColor = vbRed

Methods let you perform actions with an object. In many cases, calling a method does more than setting a property. In fact, a single method may launch a complex operation that affects many properties. The Requery method tells your form to get the latest data from the database, and then refresh all its controls.

To use a method, you type the object name, followed by a period, followed by the method name. However, you don’t use the equal sign because you aren’t setting the method. You’re just calling it into action.

Here’s an example that refreshes the current record on a form using the Refresh method:

	Form.Refresh

In some cases, a method requires some extra information. If this is the case, you’ll know, because Visual Basic’s IntelliSense lets you know as you write your code (see Figure 16-7).

Figure 16-7. The rarely used Move method lets you reposition and resize a control in one blow. Once you type the name of this method, the Visual Basic editor shows you the four values you can supply. In this example, only the first value (Left) is required—the others are placed in square brackets, which means you can leave them out.

If you need to supply extra information for a method, you must add a space after the method name, followed by the appropriate value. If you need to supply several values, you separate each one with a comma. Here’s an example that moves a control to the top left corner of a form:

	Description.Move 0, 0

Table 16-2 lists the most important control methods.

As you know, events are the notifications that objects use to tell your code something important has just happened. You’ve already mastered events, and you’ve used them in this chapter to react to button clicks. For a list of the most common control events, refer to Section 15.5.1.

One topic that you haven’t considered yet is how events can provide extra bits of information. As you may have already noticed, every subroutine includes a pair of parentheses. Here’s a second look:

	Private Sub ButtonOfPower_Click()

In the examples you’ve seen so far, those parentheses don’t contain anything at all. However, they exist for a reason. Some events provide your code with additional event information, and this information’s sandwiched between the two parentheses.

Consider the On Key Press event of a text box, which occurs every time someone types a character. It provides a special numeric code that represents the key that was pressed. (Programmers call this the ASCII code.)

If you add a subroutine to respond to the On Key Press event, then Access generates code like this:

	Private Sub MyTextBox_KeyPress(KeyAscii As Integer)
	End Sub

This code means that the On Key Press event’s providing your code with another piece of information. It’s an integer (whole number) named KeyAscii, and you can use it in your own code. Here’s an example that simply shows the key code in a Message box:

	Private Sub MyTextBox_KeyPress(KeyAscii As Integer)
	    MsgBox "Your pressed the key with the code: " & KeyAscii
	End Sub

Some events provide several pieces of information. In these cases, you’ll spot a whole list between the parentheses. Each piece of information’s separated by a comma, and called a parameter.

Here’s an example for the On Mouse Move event (which occurs when you move the mouse pointer over the control). The opening declaration for the subroutine is so long that it had to be split over two lines using the underscore:

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

In this case, you get four pieces of information. The Button parameter indicates which mouse buttons are currently pressed. The Shift parameter indicates whether the Shift, Ctrl, and Alt keys were held down while the mouse was moved. Finally, the X and Y parameters indicate where the mouse pointer is (its coordinates).

Record editing’s a two-stage process. First, you change one or more field values, which places the record into edit mode. Then, you close the form or move to another record, which commits your change. Or you hit Esc, which cancels your changes, and then reverts to the original values.

If you’re using the Record Selection bar (meaning the Record Selectors property of the form’s set to Yes in the Property Sheet, which is the standard setting), Access indicates when you’re in edit mode by changing the tiny arrow in the form’s top-left corner to a tiny pencil icon. This icon’s a helpful indicator that something’s changed on your form and you need to decide whether to go through with the update. However, Access newbies and pros alike can easily miss the tiny pencil icon. That’s why some people prefer to make the change much more obvious by showing a message on the form, or changing the background color.

The following example demonstrates this technique. Figure 16-8 shows the result.

Figure 16-8. Top: The form seems normal at first glance. Bottom: If you change any value, the form background changes color, and a text message appears at the bottom.

In order to create this example, you need to start by building the right form. Take an ordinary form, and then add a label to the form footer (see Section 13.1.1). Give the label a respectable name, like InfoMessage, by changing the Name in the Property Sheet. Now you’re ready to write some code.

Before you add the code to your form, you need to determine two things:

In this example, you need to respond to the On Dirty event of the form. This event occurs when the record’s changed in any way, and the form switches into edit mode. (Deeply repressed programmers say this moment’s when the form “becomes dirty.”) If several values are modified, the On Dirty event fires only for the first change. After that, the form’s already in edit mode, and already “dirty.”

Here’s the subroutine you need to react to the On Dirty event of your form:

	Private Sub Form_Dirty(Cancel As Integer)
	End Sub

You can type this into an existing form module by hand (as long as you use exactly the same name for your subroutine), or you can add it using the Property Sheet (just select the Form in the Property Sheet list, find the On Dirty event, choose Event Procedure from the list, and then click the ellipsis button).

Now comes the fun part—writing the code. First, you need a statement that changes the form’s background color. Although the form object doesn’t provide a BackColor property, the objects that represent the individual parts of the form (Details, FormFooter, and FormHeader) do. So you can use code like this:

	Detail.BackColor = vbRed

You also need to fill in a message in the label:

	InfoMessage.Caption = "You have modified this record. " & _
	 "If you move to another record, your changes will be applied. " & _ 
	 "To cancel your changes, hit the Esc key."

Place these two code statements into the Form_Dirty subroutine, and you’re ready to go.

	Detail.BackColor = RGB(266, 160, 122)

Right now, the form has a flaw. When you make your first change, the label appears and the background color changes, just as it should. However, once you commit that change by moving to another record, the color and message remain. This result’s obviously not what you want.

To fix this problem, you need to react to another event: the form’s After Update event. This event takes place after Access has successfully applied the change. Here’s the code you need to return the form back to its normal appearance:

	Private Sub Form_AfterUpdate()
	    Detail.BackColor = vbWhite
	    InfoMessage.Caption = ""
	End Sub

Figure 16-9. To pick a color, click the color grid. (The crosshairs shows your current location.) Then, use the vertical slider to adjust your color’s intensity. You see the Red, Green, and Blue values change as you change the color.

The example still isn’t quite complete. Besides committing a change, someone can also hit Esc to cancel it. You need to respond to this possibility as well, and use the same code to return the form to normal. In this case, you use the form’s On Undo event:

	Private Sub Form_Undo()
	    Detail.BackColor = vbWhite
	    InfoMessage.Caption = ""
	End Sub

This step completes the example. To see all three subroutines together, and try them out, download the sample database for this chapter (Section 3.4.2.3 explains the deal on sample databases).

A mouseover effect is an action that takes place when you move the mouse over some region of a form. You could do things like highlight the control underneath by changing its formatting or content. Web designers often use mouseover effects to create buttons that change their appearance when you hover over them.

You can easily create a mouseover effect in Access. You simply need to respond to the On Mouse Move event. You can use the form’s On Mouse Move event if you want to watch the mouse move all over. More typically, you can use the On Mouse Move event for specific controls, which allows you to detect when the mouse moves over those controls.

The form in Figure 16-10 uses a mouseover effect.

As usual, to create this, you need to start by adding the extra controls you need, like the Don’t Click Me button (which we’ll name DoNotClickButton) and the image (named HappyFace).

Once those details are in place, you need to create two subroutines. The first responds to the On Mouse Move event of the button. It swaps in the n you move over the button:

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

	    HappyFace.Picture = "c:ImagesUnHappy.jpg"
	End Sub

This code assumes that you’ve placed the picture file (named UnHappy.jpg) in a folder named c:Images.

As with all your code, you can type this into an existing form module by hand or, more conveniently, you can use the Property Sheet to create it (Section 16.2).

Figure 16-10. Top: The form as it first appears. Bottom: When you move the mouse over the Don’t Click Me button, the image at the side changes from a happy face to a frowning face. Move anywhere else, and the happy face returns.

The second subroutine responds to the On Mouse Move event of the form’s detail section, which happens when you move out of the button and over the blank space around it. This subroutine switches the image back to its original happy face:

	Private Sub Detail_MouseMove(Button As Integer, _
	  Shift As Integer, X As Single, Y As Single)
	    
	    HappyFace.Picture = "c:ImagesHappy.jpg"

	End Sub

The way this example’s currently written has one weakness. Right now, it relies on the UnHappy.jpg and Happy.jpg files being in specific locations on the hard drive. This detail’s hard to guarantee—after all, who’s to say you won’t move them somewhere else or try to open the database on another computer?

A better solution is to place the images in the same folder as the database file. You can point Access to that location using code like this:

	HappyFace.Picture = CurrentProject.Path & "Happy.jpg"

This code works using a special object that’s always available in any code you write: the CurrentProject object, which provides information about your current database and the objects it contains. CurrentProject has a Path property, which gives you the location of the current database as a text string.

With this code in place, you can confidently copy your database anywhere, as long as you make sure the image files are placed in the same folder.