Section 15.5. Connecting Macros to Forms

15.5. Connecting Macros to Forms

The slickest macros work with the forms in your database. Using this combination, you can create macros that fire automatically when something happens (like when a button's clicked or a record's changed). You can also build much more flexible macros that don't have fixed argument valuesinstead, they can read the data they need from the text boxes on a form.

In the following sections, you'll learn how to help macros and forms come together.

15.5.1. Understanding Events

So far, you've been running macros the hard way: by finding the ones you want in the navigation pane, and then launching them by hand. But in a well-oiled database, macros seldom play an upfront role. Instead, they hide behind the scenes until they're sparked into action. You could create a macro that's triggered when you click a button, open a form, or make a change in a text box. These triggers are known as events .

A form has three types of events:

  • Control events . These events are the most useful. They happen when you do something with a control. For example, when you click a button, it fires an On Click event. (This is a great time to perform just about any action.) When you change a value in a text box, you get an On Change event. (This is a good time to check that the text makes sense using your crafty validation code.) You'll notice that most event names start with the word "On".

    Note: Many controls have the same events. If you have two text boxes and a button on your form, then they all have an On Click event. However, there's no confusion for Access, because it keeps track of what event happens, and what control it happens to .
  • Section events . As you learned earlier, forms are divided into sections so you can separate the header and footer content from the rest of the record. Each section has a few of its own events, which fire when you move the mouse around (On Mouse Move), or click somewhere on a blank space (On Click). These events tend to be less useful for macro programmers.

  • Form events . A long list of more general events relate to the form. This list includes events that fire when the form's first opened (On Open) and when it's closed (On Close), when you move from one record to the next (On Current), and when you complete a data operation like an update (After Update).

To see the list of events for a form's different parts , follow these steps:

  1. Open your form in design mode .

    If the Property Sheet isn't visible, then show it by choosing Form Tools Design Tools Property Sheet.

  2. In the Property Sheet, choose the Event tab .

    Now you'll see the list of events that are provided by the control, as shown in Figure 15-18.

Figure 15-18. Here are the events for a typical text box. When you click one of the Event boxes, a one-line description of the event appears in the Status bar at the window's bottom-left section. As you can see, right now all the Event boxes are empty, which means there aren't any attached macros.

The biggest challenge in using events is figuring out which ones to employ . If you take a few seconds exploring the events on your form, you'll find dozens of events, many that are rarely used or ridiculously specialized. That's where Table 15-1 fits init highlights some of the most useful events for macro programming.

Table 15-4. Useful Events for Macro Programming




All Controls

On Enter

On Mouse Move

Occurs when you move to a control for the first time (either by pressing a key like Tab or by clicking with the mouse).

Occurs when you move the mouse over the control.

Any Editable Control

On Change

Occurs when you modify the value in a control.


On Click

Occurs when you click a button. Other controls have click events too, but most people are in the habit of clicking buttons to get things done.

Combo Box

On Not In List

Occurs when you type in an entry that's not in the list.


On Load

On Close

On Current

On Dirty

On Undo

Before Insert, Before Update, Before Del Confirm

After Insert, After Update, After Del Confirm

Occurs when the form's first opened (and you can initialize it).

Occurs when the form's closed. You can cancel this event if you want the form to stay open.

Occurs when you move to a record (including when you open the form, and then move to the first record).

Occurs when you make the first change in a record. It's now in edit mode.

Occurs when you back out of edit mode and cancel your changes (usually, by pressing Esc).

Occurs when you're in the process of applying an insert, update, or delete. You can cancel this event if you don't like what you see (for example, if you find invalid data).

Occurs after the operation's complete. You can't cancel it any longer, but you may want to react to the change to perform another task or update the information you're showing.

Note: The update, insert, and delete confirmation events (the last two rows in the table) also apply to any editable control. A text box also uses the Before Update and After Update events to indicate when its value has changed. In Chapter 17 (Section 17.1.3), you'll see an example that uses this event to react immediately when a specific field's changed (rather than wait until the entire record's updated).

If you browse the Property Sheet's Event tab, then you'll find many more events, including ones that let you react when someone presses a key, clicks somewhere with the mouse, or moves from one control to the next. You don't need to worry about all these options right now. Once you've learned how to respond to an event with a macro, you'll be able to deal with just about any event.

15.5.2. Attaching a Macro to an Event

Now that you've seen the events that forms and controls offer, it's time to try hooking up a macro. The basic sequence of steps is easy:

  1. Create and save a macro, as described in Section 15.1.1 .

  2. Open your form in design mode, and make sure the Property Sheet's visible .

  3. Select a control, a section, or the entire form .

  4. In the Property Sheet, choose the Events tab, and then find the event you want to use .

  5. In the Event box, click the drop-down arrow, and then choose the macro you want to use .

Figure 15-19 shows an example.

Figure 15-19. In this example, the On Click event of a button's being connected to the MailResults macro you saw in Section 15.3.3. Now, whenever you click the button, the MailResults macro runs.

15.5.3. Reading Arguments from a Form

Earlier in this chapter, you saw macros that could search for records, print reports , and email data. In all of these cases, the macro arguments were fixed valuesin other words, you type them in exactly, and they never change. in Section 15.3.1 you saw a macro that searched for the text "hay." Convenient as this macro is, you can't reuse it to search for anything else. If you want to dig up different text, then you need to create a whole new macro.

Embedded Macros

I created a macro with the Command Button wizard. How can I edit it ?

When you drop a button onto a form, Access fires up the Button wizard, which creates a macro for you (Section 13.2.7). The macros that the Command Button wizard creates are embedded macros, which means they're stored inside the form object. This system has some advantages (for example, you can transfer your form from one database to another without losing the associated macros). It also has a downsidenamely, you can't edit or run the macro independently.

Fortunately, you can still modify your embedded macros (or just take a look at them if you're curious ). The trick's to use the Property Sheet. Here's how:

  1. Select the control that uses the macro (in this case, a button).

  2. Find the event that has the attached macro. In the case of the button, it's the On Click event. You'll see the text [Embedded Macro] in the Event box, rather than a macro name .

  3. Click once inside the Event box. The ellipsis ( ) button appears next to it.

  4. Click the ellipsis button to edit the embedded macro in the familiar macro builder.

To make more flexible macros, you can use an expression instead of a fixed value. You've already used expressions extensively in the past (see Section 7.1 for examples with queries, 345 with reports, and 410 with forms), so you won't have any problem building basic expressions that combine text, add numbers , and use Access functions. But when you're creating a macro, the most useful expressions are those that can pull a value out of a form. All you need to know is the control's name.

To see how this process works, you can revise the filtering example shown earlier and create a search form like the one shown in Figure 15-20.

To create this example, you need to start by adding the text box you need for searching. Here's how:

  • Open the form in design mode .

  • Select Form Tools Design Controls Text Box, and then draw the text box onto the form .

  • Once it's there, select it, and then, in the Property Sheet, choose the Other tab .

  • At the top of the Other tab, change the Name property to something more descriptive, like SearchText .

Note: You don't always have to create a new control. Macros can read the value in any control on your form, including those that are linked to the database record. However, in this example, you need a way to supply some text that isn't part of a record, so it makes sense to add another text box for that purpose.

Figure 15-20. Instead of searching for the word "hay," this example finds any text you want. The trick? You supply the search text in a text box at the top of the form.

Next, it's time to build the macro. You no longer need the OpenForm action (which you used in the original macro), because you can assume that Access will launch this macro from the already open AnimalTypes form. So the first action you need is GoToControl, with the Control Name argument set to Description.

The second action's FindRecord. However, instead of setting the Find What argument to a fixed piece of text (=" hay "), you point it to the SearchText control using the control name (= SearchText ). If there are any spaces or special characters in the control name, make sure you wrap the name in square brackets (= [SearchText] ).

Note: If you're referring to a field or control on the current form, all you need to do is use the field or control name. However, sometimes you may want to refer to a control on another form. In this case, you need a wacky-looking syntax that indicates the form name and the control name. If you want to refer to a control named SearchText on a form named SearchForm, you'd write = Forms!SearchForm!SearchText .

Once you've polished off the macro, the last step's adding a button to the Animal-Types form to trigger it. Here's how:

  • Select Form Tools Design Controls Button, and then draw the button onto the form .

  • Hit the Esc key to cancel the Button wizard .

  • In the Property Sheet, choose the Events tab .

  • Click the drop-down arrow next to the On Click event, and then, from the list, choose the macro you've just created .

  • Now choose the Format tab, and, in the Caption field, enter the word Search . This descriptive text appears on the button .

This step completes the example. To try it out, switch to Form view, type some-thing in the SearchText text box, and then click the Search button. You'll skip ahead to the next matching record.

15.5.4. Changing Form Properties

Not only can you read form values, you can also change them. The trick's a macro action called SetValue. SetValue is a remarkably powerful action, because it can change any property of a control. You can use it to change the text in a control, hide it, change its formatting, and so on. (For more about different control properties you may want to use, see Chapter 13.) The only catch is that Access considers SetValue to be an unsafe action, so it doesn't let you perform it in an untrusted database (Section 15.2.2).

SetValue has only two arguments. The first argument, Item, identifies what you want to change. You can modify a form, section, field, or control. The second argument, Expression, sets the new value. You can use a fixed value, or you can read the value you want from another control using an expression.

If you want to create a macro that clears the search text from the SearchText text box, then you could add a SetValue action, and set the Item property to SearchText and the Expression property to "" (which represents a bit of text with nothing in it).

Note: This example assumes you're using SetValue on the current form (for example, by pressing a button named Clear). If you're running the macro from the navigation pane, you'll need to change SearchText to the full name Forms!AnimalTypes!SearchText so it clearly tells Access which form you're using.

If you like SetValue, then you may be interested in the related SetProperty action. The SetProperty action changes one of a control's properties. (You choose what property you want to change with the Property argument.) You can use SetProperty to change a control's color , position, or captioning. However, the most common way to use SetProperty is to modify the Enabled property (to lock down controls that shouldn't be editable) or the Visible property (to hide controls that aren't relevant). You can set both properties to True or False.

SetProperty's nicest quality's that Access always considers it a safe action. The only drawback's that Access doesn't let set the Text property of a control, because then you could use it to modify a table.

Access 2007[c] The Missing Manual
Access 2007[c] The Missing Manual
ISBN: 596527608
Year: 2007
Pages: 153 © 2008-2017.
If you may any questions please contact us: