Automating Tasks with Macros

1. Automating Tasks with Macros

2. Macro Essentials • A macro is a list of actions that happen when you run the macro. • Creating a Macro: • Choose Create  Macros & Code  Macro. • Choose first action in the Add New Action list. • Fill in the arguments for your action, using the text boxes that appear under it.

3. Macro Essentials • Running a Macro: there are four ways to run a macro. • Run it directly by double-click its name in Navigation pane, Or, if the macro is open, choose Macro Tools | Design  Tools  Run. • Can trigger it using a keystroke. • Run it automatically when the database is first opened. • Attach macro to a form.

4. Editing a Macro • Open macro in Design view • Change action arguments. • Reorder actions. • Add a new action. • Remove an action. • Add comments.

5. Collapsing, Expanding, and Grouping Macro Actions • Collapsing, Expanding: by simply clicking the +/– button that appears to the left of the action name. • Collapsing: hide some of this information. • Expanding: Expand the action back into view. • Grouping Macro Actions: subdivide a single macro in sections that help you concentrate on the portion of the macro that interests you.

6. Collapsing, Expanding, and Grouping Macro Actions • To create a group: • In the Add New Action list, choose Group. It’s the second entry. • Enter a group name. • Add actions to your group. • Every group starts with the group name and ends with the text “End Group”. In between are the actions of the group.

7. Collapsing, Expanding, and Grouping Macro Actions • Example:

8. Solving Macro Problems • These steps to use single-step debugging: • Open macro in Design view. • Choose Macro Tools | Design ➝ Tools  Single Step. • Choose Macro Tools | Design ➝ Tools ➝ Run • Click Step, Continue, or Stop All Macros, depending on what you want to do next.

9. Managing Macros • To manage macros, can use the sub macro feature. • A sub macro is a small, named bundle of actions. • The nifty of sub macros is can put many sub macros into a single macro object. • Using sub macros, you can keep related functionality close together, easier time finding the macro you need.

10. Managing Macros • To create sub macros: • Opening an existing macro • Choose Sub macro from the Add New Action list. • Or drag the Program Flow ➝ Sub macro action from the Action Catalog and drop it onto your macro • Either way create a new blank sub macro.

11. Managing Macros • Two ways to add a Sub macro:

12. Configuring a Startup Macro • Macro AutoExec: • Macro automatically run when open database • Macro always include the actions like: • Opens a few important forms and reports. • Imports data from another file. • Runs a cleanup query. • Macro name is AutoExec

13. Connecting Macros to Forms and Tables • Understanding 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. • Section events: forms are divided into sections, each section has its own events. • Form events: includes events that fire when the form is opened, closed, when move from one record to the next and an update.

14. Connecting Macros to Forms and Tables • To see the list of events for a form’s different parts: • Open your form in Design mode. • Select the item that has the events you want to examine. • In the Property Sheet, choose the Event tab. • Select event appropriate.

15. Attaching a Macro to an Event Create and save a macro. Open form in Design view, and make sure the Property Sheet is visible. Select a control, a section, or the entire form. In the Property Sheet, choose the Events tab, and then find the event you want to use. In the Event box, click the drop-down arrow, and choose the macro you want to use.

16. Conditional Macros • Conditional Macros: perform conditional actions. • To create a conditional macro, need to use the If action: • Select the Ifaction from the Add New Action list • Drag If actionfrom the Program Flow section of the Action Catalog. • The If action requires a single argument, which is a condition.

17. Conditional Macros • Here’s how it works: • If the condition turns out to be True, Access runs the actions in the If box. • If the condition turns out to be False, Access skips over the actions in the If box and continues with the next action after the If action.

18. Conditional Macros • Building a Condition: • Use the compare operators: <, > <=, >=, <>, = • Logical operator: And & Or. • Functions: • IsNull() • Len() • …

19. Data Macros • Data Macros is a macro run when an event occurs in a table. • Data macros respond to table events like create a record, edit a record, and send an email. • Data macros support a different set of actions. • Data macros can stick with database even when transfer it to another platform.

20. Data Macros • Table Events:

21. Data Macros • Example: Create a data macro that prevents a blank value for EmailAddress: • Open table in Datasheet view. • Choose Table Tools  Table  Before Events  Before Change. • Add the actions you need: • If action tests to see if the rules are being broken. [WantsEmail]=Yes And IsNull([EmailAddress]). • RaiseError action cancels the change and provides an error message.