Download - Ch04 Access
Collecting Data for Well-Designed Forms
1
Chapter 4
“Making good decisions is a crucial skill at every level.”—Peter Drucker
XPXPChapter Introduction• Automate process of acquiring data needed for
day-to-day operation of business• Forms can show only one record at time
– Provide many advantages to database users– Flexibility for users and designers
• Consist of one or many pages• Design all forms in a database to create
consistent look and feel
2Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach
XPXPTools Covered in this Chapter• AutoFormat (predefined and custom)• Calculated field• Combo box (for locating a record)• Command button• Control Wizards
3Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach
XPXPTools Covered in this Chapter (continued)• Find tool• Form properties• Form Wizard• Subform control• Tab control• Tab order
4Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach
XPXPLevel 1 Objectives:Developing Simple Forms to Facilitate Data Entry• Design forms for efficient data entry• Create simple forms for data entry and editing• Develop a consistent user interface
5Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach
XPXPDesigning Forms for Efficient Data Entry• Electronic form
– Object used to enter, update, and print records – Present records in format that makes data easy to
enter and retrieve• Guidelines for designing electronic forms:
– Provide meaningful title– Organize fields logically– Use appealing form layout– Include familiar field labels
6Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach
XPXPDesigning Forms for Efficient Data Entry (continued)• Guidelines for designing electronic forms:
– Be consistent with terminology and abbreviations– Allow for convenient cursor movement– Prevent and correct errors– Include explanatory messages for fields
• Before creating form in Access– Sketch on paper– Verify database integrity – Test tables and relationships – Examine and enter sample data
7Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach
XPXPComparing a Well-Designed Form with a Poorly Designed Form
8Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach
XPXPVerifying Database Integrity• Accurate data
– Major goal in every database
• Errors can be prevented by the design of database– All IDs created with AutoNumber field type– Prevent manual entry of these fields
• Examine each table in design view – Verify that field properties in place
• Test – Format masks– Relational integrity
9Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach
XPXPExamining and Entering Data• Enter data into records using
– Table’s datasheet view – Using form that includes fields from one or more
tables• Most Access database applications use forms for
data entry – After database released for regular business use
10Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach
XPXPCreating Simple Forms for Data Entry and Editing• Access provides many ways to create forms
– Options for creating simple forms• Form tools• Form wizard
11Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach
XPXPCreating a Form Using a Simple Form Tool• Click the Form tool on the Create tab• Access creates form by
– Arranging all fields in table in columnar format – Displays first record in form window– Converts subdatasheet into subform
• Move from one field to next– Press Tab key
12Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach
XPXPCreating a Form Using an AutoForm Wizard (continued)• Move from one record to another
– Click buttons on navigation bar at bottom of main form
– Data in subform changes
13Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach
XPXPUsing the Form Wizard• Specify field order• Specify particular layout and style for form• Click the More Forms button on the Create tab,
and then click Form Wizard
14Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach
XPXPStarting the Form Wizard
15Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach
XPXPSelect the Fields you Want to Show on the Form
16Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach
XPXPDeveloping a Consistent User Interface• Forms in database share same design
– Present consistent user interface– Users learn how to use forms once
• Apply what they learn to all forms in database
17Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach
XPXPfrmCustomer in Design View
18Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach
XPXPExamining a Form in Design View• Record source
– Underlying object – Provides fields and data in form
• Bound form– Displays data from fields in record source– Type for data-entry forms
• Unbound forms – Do not have record source– Designed to help users navigate through database
19Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach
XPXPExamining a Form in Design View (continued)• Link form to record source
– Using design elements called controls• Work with form in design view
– Manipulate controls• Place control on form
– Use toolbox toolbar• Detail section for main body of form
– Displays records and contains all bound controls
20Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach
XPXPToolbox Tools for Forms
21Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach
XPXPToolbox Tools for Forms
22Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach
XPXPImportant tools on the Arrange tab
23Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach
XPXPBlank Form in Design View with Typical Sections
24Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach
XPXPExamining a Form in Design View• Form header
– Displays information that always appears on form even when records change
– Top of screen• Form footer
– Displays information that always appears on form– Bottom of screen– Often contain
• Instructions for using form • Buttons to perform actions
25Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach
XPXPExamining a Form in Design View (continued)• Page headers and footers
– Display information at top or bottom of every page– Appear only when printing form by default– Headers
• Useful for column headings dates, and page numbers
– Footers• Display summaries and page numbers
• Grid– Form background
26Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach
XPXPChanging a Title to a Form• Open or enlarge form header or form footer
– Drags bottom of section bar to increase height of section
– Use label to create header/footer• Adjust label properties
27Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach
XPXPSaving a Custom Style as an AutoFormat
28Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach
XPXPLevel 1 Summary• Forms provide easy to use interface
– Normally used in production databases to access/edit data
• Create using– Form wizard
• Use AutoFormats to customize form appearance– Create custom AutoFormats
29Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach
XPXPLevel 2 Objectives: Creating Forms that Use Fields From More than One Table• Create multitable forms• Improve navigation on forms• Control form printing
30Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach
XPXPAdding a Subform to an Existing Form• Subform
– Form embedded in another form• Primary form
– Called main form– Underlying table usually has one-to-many
relationship with table underlying subform• Main form and subform linked
– Subform displays only records that related to current record in main form
31Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach
XPXPAdding a Subform to an Existing Form (continued)• Use Wizard to create subform
– Access automatically synchronizes main form with subform
– Only if tables containing fields for form related– Subform must also have field with same name or
compatible data type and field size as primary key in table underlying main form
• Main form can have more than one subform• Subform can also contain another subform
32Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach
XPXPModifying the Form Layout• Select control
– Eight handles appear on corners and edges– Upper-left corner is move handle– Other seven handles called sizing handles
• Resize control
• Move text box and attached label together– Select text box– Move pointer to anywhere on border except on move
handle or sizing handle– Pointer changes to hand shape
33Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach
XPXPModifying the Form Layout (continued)• Labels
– Move with text field or independently– Edit text– Resize
34Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach
XPXPCreating a Form from a Query• User view
– Custom form that shows only fields particular user wants
– Might or might not be used for data entry– In some cases fields may be locked so all user can do
is look at data• Use Form Wizard to create form based on query
– Access asks which table to view results by
35Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach
XPXPCreating a Form from a Query (continued)• Prevent editing
– Set form properties to No• Allow edits• Allow deletions• Allow additions • Data entry
36Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach
XPXPAdding Command Buttons to a Form• Command buttons
– Users click to perform common tasks• Access provides collection of command buttons
– Associated with actions– Can contain
• Text • Standard icons available from Access• Graphics
37Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach
XPXPAdding Command Buttons to a Form (continued)• Create
– Using Command Button Wizard – By adding button to form then setting properties
• Be consistent when creating command buttons– Location on form– Order
38Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach
XPXPCommand Button Options
39Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach
XPXPAdding an Unbound Graphic to a Form• Insert image
– Click Logo button in the Controls group– Inserts graphic into form header– Move and resize as needed
40Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach
XPXPExploring Other Form Properties• Open property sheet for form
– Right-click form selector button in upper-left corner of form– Click Properties on shortcut menu
• Common reason to use form properties– Decide to base form on different record source from one used
to create form
• When using form to add data to underlying tables– Be sure to include primary and foreign key(s) fields in form
41Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach
XPXPCommon Form Properties
42Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach
XPXPCommon Form Section Properties
43Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach
XPXPControlling Form Printing• Control form’s vertical spacing on printed page • Include date and page number
– Using Access-provided functions
44Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach
XPXPPrinting a Selected Record in Form View• Print Preview button
– Preview form – See how it will print
45Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach
XPXPLevel 2 Summary• Add subform to existing form• Modify form layout• Create form from query• Create command buttons• Modify form properties• Print form
– Use print preview
46Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach
XPXPLevel 3 Objectives: Creating Forms for Completing Daily Business Tasks• Improve usability of forms• Place calculations on forms• Develop advanced forms
47Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach
XPXPImproving the Usability of Forms• Use form controls
– Speed up process of locating particular record• Include calculated control in form• Create multiple-page forms • Create forms with multiple subforms• Learn about tab order• Control focus in form to skip unbound controls
48Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach
XPXPLocating a Record Quickly• Find tool
– Click field to search for particular value– Open find and replace dialog box– Enter value to match – Access searches for records that contain same value
in selected field• Combo box
– Displays list of values – Users select one from list
49Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach
XPXPWays to Locate a Record
50Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach
XPXPLocating a Record Quickly (continued)• Use Control Wizard to add combo box to form• Focus
– Indicates control currently active and ready for user action
• Combo Box Wizard– Provides three options for listing values in combo box
• Look up values in table or query• Let users type value • Let users select value that Access matches to find record
51Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach
XPXPSorting the Contents of a Combo Box for Locating a Record• Access sorts contents of combo box
– Based on default sort order of record source• Use control’s property sheet to specify sort order
for record source• Row source property
– Indicates how Access should provide data to combo box
– Provides build button to open SQL query builder window
52Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach
XPXPAdding a Calculation to a Form• Include calculated field on form
– Create field in query– Use query as record source for form– Or add an unbound control that contains calculation
directly to form• Create unbound control
– Text box button– Enter calculation
53Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach
XPXPStreamlining the Main Pharmacy Processes in a Complex Form• Process analysis
– List steps and substeps employees perform – Indicate which database objects used to complete
each task
54Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach
XPXPStep 1: Registering New Customers or Confirming Customer Identity• Usually completed by technician• Uses command button wizard to add two
command buttons to open associated forms
55Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach
XPXPStep 2: Filling and Refilling Prescriptions• Started by technicians • Pharmacist must check for
– Drug interactions – Confirm instructions and dosages
56Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach
XPXPCreating Forms with Many Subforms• Arrange fields in logical groups
– According to how employees most often use them• Click subform/subreport button
– Start subform wizard• Prevent data errors
– Change properties of subform so it does not allow edits, deletions, or additions
57Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach
XPXPCompleted frmPrimaryActivity with Three Subforms
58Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach
XPXPCreating Multipage Forms Using Page Break• Too many fields to fit comfortably on one screen
– Continue form onto additional page – Place page break in form – Page up or page down key moves from page to page
59Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach
XPXPCreating Forms Using Tab Controls for Multiple Subforms• Tab control
– Add multiple subforms to form in compact way– Each subform has tab at top
• Layered one on top of other
– Add first tab by clicking tab control button– Add additional tabs by
• Right-clicking blank spot in tab control• Clicking insert page
60Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach
XPXPForm with Tab Controls
61Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach
XPXPChanging Tab Order and Tab Stop• Form created with form tool or Form Wizard
– Places fields in order they appear in table design
• After fields rearranged for more logical data entry – Form needs modification to arrange order of movement
through fields– Called tab order
• Test each form by – Opening form – Pressing tab key to see if cursor stops in each field in
appropriate order
62Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach
XPXPLevel 3 Summary• Locate records using
– Find tool– Combo box
• Add calculations to forms• Create forms with multiple subforms• Tab canvas• Set tab order
– After modifying field order on form
63Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach
XPXPChapter Summary• Forms provide easy to use interface• Create using Wizards• Use subforms to display related data• Create form from query• Create command buttons• Create forms that display large amount of data
– Multiple subforms– Multiple pages– Tab canvases
64Succeeding in Business with Microsoft Office Access 2007: A Problem-Solving Approach