Microsoft Access 2002

1
Microsoft Access 2002

• Tutorial 9 Automating Tasks With Macros

2
Design a switchboard and dialog box for a
graphical user interface

• Database developers interact directly with
  Access.
• However, often you do not want the user of the
  database to interact directly with Access
  rather, you would provide an interface that
  removes the user away from the Access interface.
• A Graphical User Interface (GUI) is a collection
  of windows, menus, dialog boxes and other
  graphical components used to communicate with a
  program.
• Often, the first view of a custom GUI is a
  switchboard.

3
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.

4
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.
5
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.

6
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.
• 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.

7
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.
8
Single-Step a macro

• When you run a macro, the series of actions are
  executing one after the other.
• When you are testing a macro, sometimes it is
  useful to run the macro one step at a time.
• This is called single stepping and causes the
  macro to perform one action, then waits for you
  to step to the next action.
• This allows you to gain a clearer view of how the
  macro is working.
• When you single step through a macro, Access
  displays a dialog box called the Macro Single
  Step dialog box.

9
Use the Macro Single Step dialog box

• This Macro Single Step dialog box displays
  details about the next action in the macro.
• You have three choices as to how you want to
  respond
• You can step through the macro one step at a time
• You can halt the macro
• You continue the macro
• Single-stepping is used to help you determine if
  you have placed the actions in the right order
  and whether the actions are working as you expect
  them to.

10
The Macro Single Step dialog box
The following figure shows the Macro Single Step
dialog box. Notice that the dialog box provides
information about the current macro action.
Notice also that you have three command buttons
from which you will choose what to do next.
11
Create a macro

• Start with a blank macro and then add the actions
  to it.
• 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
Actions created by dragging specific objects
The figure below lists different kinds of objects
in Access along with the default action created
by dragging one of these objects to a Macro
window. It also lists the default argument values
created when dragging these objects to a Macro
window.
13
Tile windows to improve efficiency
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.
14
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.

15
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.

16
A macro group with two macros
In the figure below, you see an example of a
Macro group window. Notice the new column added
for the Macro name. Each macro in the group will
contain a name in this column. Actions that will
be taken within that macro will appear in the
action column but without a name.
17
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.
• 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.

18
An Access Form with a command button
In this figure you can see that a command button
has been added to the form. Notice the command
button tool on the toolbar, which was used to
create the command button.
Notice also that the control wizard should be
turned off so that you can control what
properties will be set for the command button.
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
Modify a macros property settings

• You can change the OnClick property to the name
  of the macro you want to run when the user clicks
  the command button.
• In the property sheet you can change the Caption
  property, which represents what is printed on the
  command button.
• If you prefer to have a picture on the button,
  you can choose one from the Picture Builder
  dialog box.
• For example, if the button will print a record,
  you might want to add a picture of a printer on
  the button.

21
The Picture Builder dialog box
In the figure below, you see the Picture Builder
dialog box, which contains a list of pictures
supplied by Access. You can choose one of these
pictures to appear on your command button or you
can add a picture of your own.
22
Create a dialog box form

• A dialog box is actually a form with which the
  user interacts.
• You can add many different controls to the form
  such as command buttons, list boxes, text boxes,
  labels, etc.
• To create a dialog box, you begin by adding a
  blank form.
• You will probably want to change some of the form
  properties before you begin adding controls to
  the form
• To change the text that appears in the form's
  title bar, enter a new value in the form's
  caption property
• There are several other properties that you might
  want to set for the form depending on the
  particular application
• Each property can be set on the Property sheet

23
An example of a dialog box
In this figure, you can see an example of a
dialog box. This particular dialog box list the
queries in a database.
In this example, the user can choose a query and
then select one of the command buttons on the
form to complete the action on the selected query.
24
Dialog box properties, settings, and functions
This figure shows the properties and their values
for the Queries dialog box form shown in the
previous slide. Note that this is just an
example. You might make different selections
based on the application you are working on.
25
Add a list box to a form

• On your dialog box, you might want to offer the
  user a list of choices.
• A list box is a control that displays a list of
  values that a user can brows through.
• You will usually add a label close to the list
  box to indicate what is contained in the list
  box.
• To add a list box to a form, choose the List Box
  tool on the toolbox and then move your mouse to
  the form in the position where you want the list
  box to appear.
• Once the list box is on the form, it can be sized
  and moved around just as you would any other
  control.

26
A list box on a form in Design View
In the figure below, a list box has been added to
the form. Notice that the list box has an
attached label. In this example, the attached
label will be removed because a label has already
been added to the form.
27
Use an SQL statement to fill a list box with
object names

• The standard language for querying, updating, and
  managing relational databases is SQL (Structured
  Query Language).
• Whenever you create a query in Access, Access is
  creating SQL statements to display datasheets
  according to the Query specification.
• If you want to view these SQL statements for a
  query, you can choose SQL view from the View
  menu.
• SQL uses the SELECT statement to specify what
  data is retrieved from a database and how it
  presents the data.

28
Understanding SQL statements

• Just like any other language, there are rules of
  the language called syntax.
• In order to program in SQL you need to learn the
  rules.
• However, you can read an SQL statement created by
  Access and get a pretty good idea of what the
  statement does.
• The SQL statements match up with the query
  specifications every choice made in the design
  window is reflected in the SQL statement.

29
Access the MSysObject table

• To use an SQL statement for a list box that will
  display a list of the queries in the database,
  you will need to retrieve the list of queries
  from the Access System Tables.
• The particular table you must access is called
  the MSysObject table.
• This table keeps track of all objects in the
  database.
• The MSysObject table contains some special
  queries that you probably would not want to
  include in a list of queries.

30
An example of an SQL statement
The figure below shows an example of an SQL
statement with a query. Note that this statement
was created by Access in the background. It is
not mandatory that you know SQL in order to use
Access.
31
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.
• 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.

32
Switchboard considerations

• The Switchboard Manager allows you to create only
  one switchboard for a database however, the
  switchboard can contain multiple pages.
• The main page of the switchboard will display
  when the switchboard opens.
• You can place buttons on the main page that will
  cause other pages in the switchboard to open.
• The switchboard manager is available on the
  Database Utilities option on the Tools menu.

33
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.
34
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.
35
The completed switchboard
This final figure shows the complete switchboard,
which has buttons for each of the objects with
which the user can interact.