Automating Tasks With Macros - PowerPoint PPT Presentation

1 / 34
About This Presentation
Title:

Automating Tasks With Macros

Description:

Design a switchboard and dialog box for a graphical user interface ... The switchboard is a form that opens when you start the underlying database and ... – PowerPoint PPT presentation

Number of Views:97
Avg rating:3.0/5.0
Slides: 35
Provided by: meya9
Category:

less

Transcript and Presenter's Notes

Title: Automating Tasks With Macros


1
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
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
8
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.

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

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

13
Actions created by dragging specific objects
14
Tile windows to improve efficiency
15
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.

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

17
An Access Form with a command button
18
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
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.

20
The Picture Builder dialog box
21
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.

22
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

23
An example of a dialog box
24
Dialog box properties, settings, and functions
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
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
An example of an SQL statement
30
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.

31
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
An example of a macro group to be used for a
switchboard
33
The Switchboard Manager dialog box
34
The completed switchboard
Write a Comment
User Comments (0)
About PowerShow.com