CGS2545 Database Concepts

1
CGS2545 Database Concepts

• Creating an Access Application System

Download the demo file used in the presentation
at http//www.cs.ucf.edu/phillips/downloads/wood
en_crafts.mdb
2
Purpose of Application System

• It is unreasonable for the DB designer to think
  that the database users will be as comfortable
  working with the various views of an Access
  database.
• There are a number of built-in tools that allow
  designers to create a system that is
  user-friendly for common database tasks.
• Calculated Controls
• Macros
• Wizards
• Switchboard Manager

3
Lookup Wizard

• Lookups give users a selection menu of options
  for a particular field.
• These are especially useful when you want to
  provide a limited number of options where data
  must be entered accurately.
• On a form, use the combo box control from the
  toolbox to start the lookup wizard.
• In design view of a table, choose lookup wizard
  as the data type.

4
Lookup Wizard (cont)

• Once created, forms using this field will provide
  a selection menu (combo box) offering choices
  from the table.
• Its often better to base the combo box off a
  table/query, but this may needlessly add to the
  complexity of the table relationships.

5
Input Mask Wizard

• Start the input mask in the properties section of
  the table design view.
• Fields often aided by an input mask are
  available.
• Review input masks examples sheet at
  http//www.ralphphillips.com/resources/

6
Designing Forms and Reports

• Basic forms and reports can be created very
  quickly using the Access wizards.
• By going into design view for a form or a report,
  you have greater control over the overall look of
  the object and how data is presented within the
  object.
• Working with controls is a big part of creating
  forms and reports that show data derived from
  other data.

Its no problem making a report that shows the
wholesale cost of items in inventory, but what
about a report that shows the sale price for
items from a particular vendor?
7
A Basic Report
8
Design View

• In design view, you can see the major portions of
  the report and the controls that make up the data
  presented on the report.
• Changes made to the design view of a report (or
  form) affect the appearance of that object.

9
Types of Controls

• There are three types of controls youll work
  with in reports and forms
• Bound controls are bound to some database field.
  The field that displays the number of units on
  hand is a bound control.
• Unbound controls are used to put things in the
  report/form that are not connected to database
  data. Titles and headings are unbound controls.
• Calculated controls, like the one used on the
  previous slide contain a formula that calculates
  somethingusually one or more data fields.

10
Control Properties

• Right click on a control to access the properties
  sheet for a particular control.

11
New Calculated Control

• I wanted to modify this inventory report to show
  the total cost of each item in inventory.
• I copied/pasted the Cost label control and called
  it Total Cost
• I extended the blue border line under the Total
  Cost label control
• I added a text box control to the right of the
  Cost control
• I added the formula CostOn Hand
• I formatted the control to currency.

12
Calculated Control and Function

• A sum() function is added to a calculated control
  in the page header to show the total value of
  inventory.
• sum(On HandCost)

13
Controlling Tab Order

• Click view-tab order to view the tab order
  dialog box.
• You can control the order fields become active as
  the user presses the tab button for navigation.
• Larger forms that use multiple columns and groups
  of related fields (billing address and shipping
  address) can be made more usable if the tab order
  is set logically.

14
Creating Macros

• A macro is a recording of a series of actions
  that can be started with just one actionoften
  clicking a button.
• From the main objects window, click the Macros
  object and then click the new button to create
  a new macro.

15
Actions and Arguments

• Use the action menu to state what is going to
  happen.
• Some macros may contain multiple actions.
• For each action, check to see if any arguments
  need to be set.
• This macro opens up the form named Product
  (already created) and opens it in a read-only
  state.
• The form could also be opened in edit mode or
  directly to a blank form for adding a new product.

16
Managing Macros

• You can quickly create similar macros by making
  one, then copying/pasting, then modifying the new
  macro.

17
Switchboards

• A switchboard is a special form with buttons that
  activate different objects on the database via
  macros.
• Instead of having the end-user go though a list
  of macros (which could be very long), you can
  provide a short menu of buttons with the most
  common actions.

Not all macros need to be started with a
switchboard. Some macros may only be needed when
looking at a form. The designer can create
buttons on a form (or report) that activate a
macro for exporting the data or printing.
18
New Switchboard

• If there are no existing switchboards, the
  switchboard manager will prompt you to start a
  new one.
• Here, you can create the first switchboard the
  user will see and any subsequent switchboards.
• The more macros, forms, and reports being used,
  the more helpful switchboards will be.
• The technical proficiency of the database users
  is also a big factor in creating switchboards.

19
Switchboard Pages

• One switchboard can lead to another switchboard
  to help direct the user to where they need to go.
• The new default switchboard for this database
  takes the user to either the suppliers area or
  the inventory area.

20
Switchboard Actions

• Each switchboard will have several actions
  associated with it.
• Note that in addition to running macros, a
  switchboard has built-in functions for opening
  forms in edit and add mode.

21
Using the Switchboard

• You can access your new switchboard from the
  forms area of the objects window. Remember, a
  switchboard is simply a custom form.
• You can then modify the form in design view just
  as you could any other form.

22
Start Switchboard Automatically

• Right-click on the object window title bar and
  choose the startup option.
• Here you can set the switchboard to open when the
  database is opened. You can also hide the object
  window so that the end-user is less likely to
  click on something other than what youve planned
  for them to.