Access07:Heights:Macros

Introduction
A macro is a stored sequential procedure, or set of instructions. Just as you follow a recipe to bake a cake so that it turns out just like the last one, a macro allows you to save instructions that Access can follow over and over.

A macro can be used to;


 * make your forms and reports work together,


 * find and filter records automatically,


 * set values in controls,


 * be used for specialised data handling,


 * automate data transfers, and so on.

Macros are common in Word (Word processing) and Excel (Spreadsheets), and allow you to program without programming. In Word/Excel you can record a sequence of steps in a macro, but Access requires you to "program them".

Everything you can do with a macro can be done in Visual Basic (VB) and as your skills develop you will use macros less and less. Macros can be thought of as a fast programming method useful for prototyping, and in Access will probably be replaced with VB code in a production system.

Macros in Access may be preferred to VB code when performance is an issue, as macro code executes much faster than VB code. Access 2007 (by default) places macros on buttons when opening forms and reports. Earlier versions used to create VB Code.

A basic macro to change a control on a form
To illustrate how a macro works we will create a basic macro that when run will change the background colour of the height field on the form created earlier. While not especially useful it does show how to create a macro and the steps involved.

If the message bar displays a "Security Warning" click on the [Options] button and in the pop-up window click the "Enable this content" radio button. (Failure to do this may cause the macros to display an error!) .. see the diagram following

Building a macro

1. With the form frmPeople open, select the Create tab > Macro > Macro



Adding the code

2 Make sure the active cursor is in the Action cell of the first row.


 * Use the drop down selector arrow and choose SetProperty (in older versions of Access this used to be called SetValue).

3 Add a comment if you want

4 Click in the Control Name field, type in the name of the Height field on the form (PeoHeight) You can check this by going back to the form, changing to design view, clicking on the Height field, then right click > Properties, then change to the [All] tab. The control Name is the first on the list.. You can Hightlight it and Control + C (copy) so you can paste it into the macro. When you are done close the property pane and return to the macro! You may need to click on the Macro Tools at the top to redisplay the macro toolbar. (This would have been much easier if the SetProperty could use the Tools > Builder wizard!!! which we will use in the example following)

5 Back at the Macro, change the Property to Back Colour (use the drop arrow) and in the value box, enter 8454143, this believe it or not is the computer value for yellow!



6 Click on the disk icon and save the macro as mcrHeightBackColor

Add a button to the form to activate the macro

7 Back to the form frmPeople, and change to design view (if you are not already there)

8 Select the Form Design Tools tab (at the very top)

9 Click on the button control (make sure the magic wand wizard button is active)

10 Click on the form to position the button and the Command Button wizard should appear.



11 Select [Next] and make sure the HeightBackColor macro is selected, and click Next

12 Accept the Picture and click on [Next]

13 Name the button cmdMacroHeight, and click on [Finish], a button will appear on the form

14. Change the People form to Form View

15. Click on the button and the Background colour of the Height should change to yellow

Task

Create another button to change the background colour back to white.

A conditional macro
This page illustrates how a macro can be extended to become far more powerful, by using a condition. To illustrate how a conditional macro works we will create a macro that when run will change the background colour of the name field of the form created earlier, depending on the value in the PeoFullName field. While not especially useful it does show how to create a macro and the steps involved. Since you are going to do a little "Clayton’s" programming, we really need to do some basic design first. It should be noted that a macro object can contain a group of macros. This allows for them to be organised logically. Refer to the Help files or a book on MS-Access if you want to use this feature.

Purpose:

When the Macro is run, if the First letter of FullName is greater than M * then ** change the background colour of FullName to yellow, * otherwise ** change it to a light fuscha.

Method:

1. Create new macro (name = mcrNameBackColor)

2. Using the Macro Tools > Design Tab, in the ribbon bar select the Conditions button on the Show/Hide section



3. Click in the Condition area on the first line and click the Tools > Builder wizard. Navigate to the PeoFullName field (This makes sure you get the object hierarchy right)

4. Add the >"M" to complete the condition

5. In the Action column click the drop arrow and choose SetProperty, and complete the Action Arguments as shown in the table below

6. Complete the rest of the macro as shown in the table below. Note that the second and third rows in the condition column have three dots (this links these rows to the one above) and the StopMacro action means that the following lines are not run as well.

Notes


 * Read a macro sequentially, i.e. from the top to the bottom.
 * The condition executes the associated action if true otherwise goes to the next line if false.
 * Multiple lines can be joined together using three full stops "...".
 * The StopMacro is needed or the true condition will continue throught the false lines as well (remember the computer will read from the top to the bottom - until told to stop!)

7. Save the macro (name = mcrNameBackColor)

8. Add a button to link to the macro on the People Form (frmPeople)