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:
To see the list of events for a form's different parts , follow these steps:
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
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:
Figure 15-19 shows an example.
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.
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:
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.
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:
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.