Microsoft Access 2002

1
Microsoft Access 2002

• Automating Tasks With Macros

2
Access Macros

• Used to automate Access
• Carry out a sequence of steps automatically in
  response to some event (like user pushing some
  button on a form)
• Not a keystroke recorder
• Relatively simple
• Not really programming
• Relatively versatile
• Good way to learn about automation
• But sometimes you need more

3
VBAGeneral ProgrammingObject Manipulation
Access VBA Applications
General Programming Concepts
Access Object Model
4
A Few Reasons to Use VBA

• Macros can be tough to maintain
• Write your own functions
• Looping through recordsets
• Create and manipulate objects
• Error handling
• Communication with other applications
• Transaction processing

See Should I use a macro or Visual Basic? in
Help
5
Switchboard to put it all together

• The switchboard is a form that opens when you
  start the underlying database and is usually used
  to provide the user with a set of choices.
• This provides a well-organized interface for the
  user and eliminates the need for them to interact
  directly with the database window.
• This also makes it possible to hide the
  functionality from the user so that they cannot
  make changes to the database objects.
• Lets see Switchboard in Overseas.mdb
• Was created using the Switchboard Manager, a
  built in Access utility

6
What is a switchboard?

• The switchboard is a form that opens when you
  start the underlying database and is usually used
  to provide the user with a set of choices.
• This provides a well-organized interface for the
  user and eliminates the need for them to interact
  directly with the database window.
• This also makes it possible to hide the
  functionality from the user so that they cannot
  make changes to the database objects.
• The form you create for the switchboard is called
  a dialog box, which asks for user input in the
  way of a selection.
• an unbound form

7
An example of a switchboard
The figure below is an example of a switchboard
form. The user would use this form to open the
various objects in the database.
Notice that this switchboard provides command
buttons to provide access to all the options
available to the user.
8
Run and add actions to macros

• You can create a macro with a series of actions
  that will repeat these commands whenever it is
  invoked.
• An action is an instruction to Access to perform
  an operation, such as opening a form or
  displaying a query.
• You can also automate tasks with Visual Basic for
  Applications (VBA) but it is easier for a
  beginner to create macros.
• With macros, you can simply select the actions
  you want from a list of actions.
• Once the macro has been created, you can add
  actions to it by editing the macro in the Macro
  window.

9
Use the Macro window to add actions

• It is within the Macro window that you will
  supply the action name (chosen from a list), any
  comments you want to make, and the arguments for
  the action.
• Arguments are additional facts needed to run the
  action.
• Much like function arguments are additional facts
  needed to calculate the result of a function
• Each type of action has its own set of arguments.
  
• A commonly used action is the Msgbox action,
  which will display a message to the user by way
  of a small form.
• Another commonly used action is the FindRecord
  action that will find the first record matching a
  set of criteria.

10
The Macro window
This figure shows the Macro Window. In this
window you can add macro actions and set the
arguments for the macro actions.
Notice that each action has a comment column.
This column is used to document the macro. It is
a good idea to write a comment about how this
particular action will be used.
Notice also the lower section of the window. This
section contains the properties for the currently
selected action.
see macro Employer Data in Overseas.mdb
11
Creating macros

• Start with a blank macro and then add the actions
  to it.
• Select actions from drop downs or Drag an action
  from the database windows into the macro window.
• Each type of object has a default set of
  arguments.
• For example, if you drag a table into the macro
  window, the default arguments are to open the
  table in datasheet view in edit mode.
• Drag as many objects as you want to the macro
  window.
• You can either accept the default arguments or
  you can edit them to meet your needs.
• Run the macro and observe the results of the
  macro.

12
Tile windows to improve efficiency
WindowsTile Vertically
This figure shows the Macro window and the
database window tiled on the screen. This is a
great way to drag objects to the macro window
because you can see them both at the same time.
13
Lets Create/Explore a Few Macrosin Overseas.mdb

• Northeast open the Employer Positions form
• See next slide
• Northeast - Open NSJI Reports Form
• Open various reports from that form
• Need to attach macros to the various Command
  Buttons
• Exit to Access
• Exit from Access

14
Macro to open Employer Positions form
What we want to happen
Drag
Which Form?
Which View Form, Design, Print Preview,
Datasheet?
Which Records?
15
Create macro groups

• If you have several small related macros, you
  might consider grouping them together with other
  small macros in a macro group.
• A macro group is a macro that contains other
  macros.
• This makes it easier for you to maintain a large
  collection of macros.
• See the ViewReports macro in Northeast-AdvFormsRep
  orts.mdb

16
Add a macro to a macro group

• When you group macros, each individual macro
  within the group will have a name assigned to it.
  
• The name consists of the name of the macro group,
  followed by a period, followed by the name of the
  individual macro.
• When you add a macro to a macro group, you add a
  new name to the Macro Name column.
• However, if you are simply adding an action to a
  macro within the group, you add only the new
  action in the Action column under the macro name.

17
A Simple Report Selection Form

• We want the appropriate report to open in Print
  Preview mode when user pushes the button
• Lets create the individual macros to launch each
  report
• Then well modify the Command Buttons to run the
  macros when theyre pushed

18
Add a command button to a form

• On the toolbox, you have a command button tool
  that allows you to place a command button on a
  form.
• You can use the Command Button Wizard to help you
  place the command button or you can simply place
  the command button yourself.
• The Wizard will help you attach an action to the
  button
• Well do it ourselves and attach the macros we
  made
• Click the command button tool on the toolbox,
  move your mouse to the form and draw a box where
  you want the command button to appear.
• The default text on the command button will
  appear however, you can change this and other
  properties on the command button's property sheet.

19
Attach a macro to a command button

• Once you have added a command button to a form,
  you can attach a macro to it.
• In most cases you will attach the macro to the
  command button's OnClick property.
• Whenever the user clicks on the command button,
  the attached macro will be executed.
• To attach the macro to the command button, right
  click the command button and then click on
  Properties to display the command button's
  property sheet.

20
Conditional Logic in Macros

• Can do If-Then-Else type of conditional logic
  with Macros
• View Conditions and then can add logical
  condition
• Put ... in Condition to indicate addition then
  logic
• Else logic begins with blank Condition field
• Lets build a macro that mimics our Print button
  on frmCustomerOrders in FineFood database
• Forms!frmCustomerOrder!grpPrint1
• Customer!CustomerNumForms!frmCustomerOrder
  !CustomerNum

Our condition
Setting the Where condition for opening the
report via macro
21
Use the Switchboard Manager to create a
switchboard

• First, create all the macros you will need for
  the switchboard and then create the switchboard
  that will execute the macros.
• You can use the Switchboard Manager to help you
  create the switchboard. Its on the Database
  Utilities submenu.
• The Switchboard Manager allows you to specify
  what buttons should be on the switchboard and
  identify the command to execute when each of the
  buttons is clicked.

22
An example of a macro group to be used for a
switchboard
This figure show a completed macro group
containing six macros. These macros will serve as
the actions for the switchboard.
23
The Switchboard Manager dialog box
This figure is the figure page of the Switchboard
Manager. Notice that the Main Switchboard has
been created by default. You use this dialog box
to add additional pages to the switchboard.
24
The completed switchboard
This final figure shows the complete switchboard,
which has buttons for each of the objects with
which the user can interact.