Transcript XP The Macro Single Step dialog box

XP
Microsoft Office Access 2003
Tutorial 10 – Automating Tasks With
Macros
New Perspectives on
Microsoft Office Access 2003 Tutorial 10
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.
New Perspectives on
Microsoft Office Access 2003 Tutorial 10
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.
New Perspectives on
Microsoft Office Access 2003 Tutorial 10
3
XP
An example of a switchboard
New Perspectives on
Microsoft Office Access 2003 Tutorial 10
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.
New Perspectives on
Microsoft Office Access 2003 Tutorial 10
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.
New Perspectives on
Microsoft Office Access 2003 Tutorial 10
6
XP
The Macro window
New Perspectives on
Microsoft Office Access 2003 Tutorial 10
7
XP
Single-Stepping 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.
New Perspectives on
Microsoft Office Access 2003 Tutorial 10
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.
New Perspectives on
Microsoft Office Access 2003 Tutorial 10
9
XP
The Macro Single Step dialog box
New Perspectives on
Microsoft Office Access 2003 Tutorial 10
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.
New Perspectives on
Microsoft Office Access 2003 Tutorial 10
11
Actions created by dragging
specific objects
New Perspectives on
Microsoft Office Access 2003 Tutorial 10
XP
12
XP
Tile windows to improve efficiency
New Perspectives on
Microsoft Office Access 2003 Tutorial 10
13
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.
New Perspectives on
Microsoft Office Access 2003 Tutorial 10
14
An Access Form with
a command button
New Perspectives on
Microsoft Office Access 2003 Tutorial 10
XP
15
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.
New Perspectives on
Microsoft Office Access 2003 Tutorial 10
16
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.
New Perspectives on
Microsoft Office Access 2003 Tutorial 10
17
XP
The Picture Builder dialog box
New Perspectives on
Microsoft Office Access 2003 Tutorial 10
18
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
New Perspectives on
Microsoft Office Access 2003 Tutorial 10
19
XP
An example of a dialog box
New Perspectives on
Microsoft Office Access 2003 Tutorial 10
20
Dialog box properties, settings,
and functions
New Perspectives on
Microsoft Office Access 2003 Tutorial 10
XP
21
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.
New Perspectives on
Microsoft Office Access 2003 Tutorial 10
22
XP
A list box on a form in Design View
New Perspectives on
Microsoft Office Access 2003 Tutorial 10
23
XP
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.
New Perspectives on
Microsoft Office Access 2003 Tutorial 10
24
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.
New Perspectives on
Microsoft Office Access 2003 Tutorial 10
25
XP
An example of an SQL statement
New Perspectives on
Microsoft Office Access 2003 Tutorial 10
26
XP
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.
New Perspectives on
Microsoft Office Access 2003 Tutorial 10
27
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.
New Perspectives on
Microsoft Office Access 2003 Tutorial 10
28
XP
An example of a macro group to be
used for a switchboard
New Perspectives on
Microsoft Office Access 2003 Tutorial 10
29
XP
The Switchboard Manager dialog box
New Perspectives on
Microsoft Office Access 2003 Tutorial 10
30
XP
The completed switchboard
New Perspectives on
Microsoft Office Access 2003 Tutorial 10
31