Transcript Data Macros

Automating Tasks
with Macros
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.
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.
Editing a Macro
 Open macro in Design view
− Change action arguments.
− Reorder actions.
− Add a new action.
− Remove an action.
− Add comments.
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.
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.
Collapsing, Expanding, and
Grouping Macro Actions
 Example:
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.
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.
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.
Managing Macros
 Two ways to add a Sub macro:
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

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.
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.
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.
Conditional Macros
 Conditional
Macros:
perform
conditional
actions.
− To create a conditional macro, need to use the
If action:
 Select the If action from the Add New
Action list
 Drag If action from the Program Flow
section of the Action Catalog.
− The If action requires a single argument,
which is a condition.
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.
Conditional Macros
 Building a Condition:
− Use the compare operators: <, > <=, >=, <>, =
− Logical operator: And & Or.
− Functions:
IsNull()
 Len()
 …

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.
Data Macros
 Table Events:
Event
Description
Occurs just before Access commits an edit or inserts a new
record. If you need to distinguish between the two, you can
Before Change create conditions that use the IsInsert property, which is True
for inserts and False for updates.
Before Delete
Occurs just before Access removes a record from a table
After Insert
Occurs after Access has finished inserting a new record.
After Update
After Delete
Occurs after Access has finished updating a record after an
edit.
Occurs after Access has removed a deleted record from the
table.
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.