Transcript Automating Tasks With Macros

Automating Tasks With Macros
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
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
An example of a switchboard
4
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
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
The Macro window
7
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.
8
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
The Macro Single Step dialog
box
10
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.
11
Create a macro
• 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
13
Tile windows to improve
efficiency
14
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.
15
Add a command button to a form
• 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.
16
An Access Form with
a command button
17
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.
18
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.
19
The Picture Builder dialog box
20
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.
21
Create a dialog box form
• 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
An example of a dialog box
23
Dialog box properties, settings,
and functions
24
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
A list box on a form in Design
View
26
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.
27
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
An example of an SQL
statement
29
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.
30
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.
31
An example of a macro group to
be used for a switchboard
32
The Switchboard Manager
dialog box
33
The completed switchboard
34