Section 16.2. Putting Code in a Form | Access 2007[c] The Missing Manual

16.2. Putting Code in a Form

Running a code routine directly is a bit awkward . At least when you run a macro directly, you can launch it from the navigation pane or use a handy button in the ribbon (Section 15.1.2). Neither option's available with VB code. Instead, you need to open the Visual Basic editor, choose the right module, scroll to the right subroutine, and then click Run. No one actually takes this approach, because it's just too tedious .

Fortunately, there's a better approach. You can place code in a form, and then set it to run automatically when something important happens. The following sections explain how.

16.2.1. Responding to a Form Event

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:

Open a form in design mode .

The fastest way's to right-click a form in the navigation pane, and then choose Design View.
Add a new button .

To do so, choose Forms Tools Design Controls Button, and then draw the button onto your form.
If the Property Sheet isn't currently visible, then choose Forms Tools Design Tools Property Sheet .
In the Property Sheet, choose the Event tab, and then select the button's On Click event .

When you click inside an Event box, a drop-down arrow appears in it.
Click the drop-down arrow next to On Click, and then choose [Event Procedure] .

This step tells Access that you're supplying VB code for this event, rather than a macro.
Click the ellipsis ( ) in the On Click Event box .

This step opens the Visual Basic editor and creates a code routine for your button. If you've already created the code routine, then Access switches to your existing code so you can edit it.

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.

Tip: Form modules don't appear in the navigation pane. If you want to edit them, then you have to open the Visual Basic editor yourself, and then, in the Project window, double-click the module. Or you can open the corresponding form, select the appropriate event, and use the ellipsis button to jump straight to the code routine that interests you.

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:

It starts with the word Private . This word ensures that other modules can't use this subroutine. Instead, it's accessible only to the form where your button's placed. (If you don't specify Private, then you get the default accessibility, which is Public, and your code routine's available to all. Most of the time, it doesn't matter which one you use, but Private's considered tidier.)
It has a name in the format [ControlName]_[EventName] . For example, the subroutine shown above responds to the On Click event of a button named ButtonOfPower.

Note: Wait a secondisn't it the On Click event? Just to keep you on your toes, Visual Basic uses a slightly different naming convention for events than the Access form designer. It ignores the word "On" and leaves out any spaces, which means that On Click becomes just Click. It's best not to worry about the naming discrepancy. Instead, let Access create the right subroutine names for you.

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.

Tip: You don't need to save your module when you make changes. Instead, you can freely jump back and forth between your code window and the form you're using in the Access window to test every change you make.

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.

16.2.2. Calling the Code in a Module

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.

WORD TO THE WISE
How Code Connects to Events

It's all in the namethe subroutine name, that is. When you open a form that has a matching code module, Access looks for code routines with specific names. If it finds a subroutine named MyButton_Click, then it checks first for a control named MyButton, and then it verifies that this control has an event named Click. If both details check out, then this code routine becomes an event handler , which is fancy programmer-speak that means your code's linked to the event. When the event happens (for example, when the button's clicked), Access runs the code in your subroutine.

If Access finds a subroutine named MyButton_Click and the form doesn't have a control named MyButton, there's no need to panic. Access simply assumes you've created a subroutine for your own personal use. Since this subroutine isn't an event handler, Access doesn't run it automatically in response to an event. However, your code can still call upon it when needed, as described on this page.

This system introduces two possibilities to trip yourself up. First, don't change the name of an event handler on your ownif you do, you'll break the connection between your form and your code, and the event handler won't run when the event takes place. (In other words, you click the button, but nothing happens.) Second, don't change the name of your control using the Property Sheet on the form, because that also breaks the connection. Or, if you really must fix up a bad name, just make sure you change the name of the subroutine to match the new control name.

In both these cases (renaming the subroutine and renaming the control), Access doesn't warn you about the possible effect. So keep these guidelines in mind to prevent unwanted surprises .

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 itand 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 click the ButtonOfPower button .
Access finds the ButtonOfPower_Click code, and then runs it .
The code runs another routine, the MyCodeRoutine subroutine in the MyModule module. This code shows the Message box you saw earlier (Section 16.1.2) .
After MyCodeRoutine completes, Access runs the rest of the code in ButtonOfPower_Click. In this example, there aren't any code statements, so the process ends here .

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.

Note: You can use this trick only with public subroutines. Private subroutines aren't accessible to code outside the module where they're stored. All subroutines are automatically public unless you add the word Private before the word Sub. So the MyCodeRoutine example you saw in Section 16.1.2 is public.

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 privateeither way, your code can call it.

Tip: If you want to reuse a piece of code in several places in the same form (for example, in response to different button clicks), then consider creating your own subroutine and putting your code there. If you want to reuse a piece of code in several different forms, then consider putting it in a subroutine in a separate module.

16.2.3. Reading and Writing the Fields on a Form

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.

Note: You can also use the familiar square brackets so that it's [Description] rather than Description. The brackets are optional, unless you've been reckless enough to break the good field naming rules you learned about in Section 2.5.1. If you have a text box name with a space in it, then you always need to wrap the name in square brackets.

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."

GEM IN THE ROUGH
Splitting Long Lines of Code

If you're dealing with overly long lines of code, it's a good time to use Visual Basic's line continuation character , which is a fancy name for the underscore (_). End any line with a space and the underscore , and you can continue your code straight away on the next line:

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

If you're going to use this trick, then it helps to indent every line except the first one so you can see at a glance that they're part of one code statement.

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.

Note: Visual Basic treats Yes/No fields (Section 2.3.7) as True/False fields. The end result's the same, but the syntax you use is just a bit different. To set the value of a Yes/No field, you use one of two built-in Visual Basic keywords: True or False.