Transcript Document

XP
Microsoft Access 9
Automating Tasks With Macros
1
XP
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.
2
XP
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.
3
XP
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.
4
XP
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.
5
XP
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.
6
XP
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 also the lower section of the window. This section
contains the properties for the currently selected action.
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.
7
XP
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.
8
XP
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.
9
XP
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.
10
XP
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.
11
Actions created by dragging
specific objects
XP
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.
12
XP
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.
13
XP
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.
14
XP
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.
15
XP
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.
16
XP
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.
17
An Access Form with
a command button
XP
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.
18
XP
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.
19
XP
Modify a macro’s 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.
20
XP
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.
21
XP
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
22
XP
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.
23
Dialog box properties, settings,
and functions
XP
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.
24
XP
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.
25
XP
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.
26
Use an SQL statement to fill a
list box with object names




XP
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.
27
XP
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.
28
XP
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.
29
XP
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.
30
Use the Switchboard Manager
to create a switchboard



XP
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.
31
XP
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.
32
XP
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.
33
XP
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.
34
XP
The completed switchboard
This final figure shows the
complete switchboard,
which has buttons for each
of the objects with which
the user can interact.
35