Transcript a-c6

Exploring Microsoft
Office Access 2007
Chapter 6
Data Protection
Robert Grauer, Keith Mulbery, Maurie Wigman Lockley
Committed to Shaping the Next Generation of IT Experts.1
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
Objectives






Establish Data Validity
Create a Lookup
Modify a Lookup by Adding and Deleting
Values
Create and Modify a Multivalve Lookup Field
Work with Input Masks
Add and remove pivot fields
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
2
Objectives





Create Forms Using the Forms Tools
Create Custom Forms Using Design View
Add Action Buttons and Combo Box Controls
Create Subforms
Fashion Functional Formats
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
3
Data Validity

It is important data be accurate and valid


It is the only way to ensure accuracy
Remember:


Reports, forms, queries all based on table data
Garbage In = Garbage out
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
4
Data Validation in Access


Tools that help control the type and accuracy
of data entered
Some validation provided in Access by
default



Once data type declared, data entered must
match
Primary key fields must be populated
Data in primary key fields cannot be duplicated
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
5
Types of Validation
Default Value property
Required property


Required – data must be entered in field
Default Values – supplies a given value in each field
automatically
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
6
Types of Validation
Input Mask property


Lookup field properties
tab
Input Mask – forces data conformity
Lookup Lists – values that can be chosen for a field
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
7
Setting Required Field Properties
YES value in
Required property


In Design view
 Select the field
 In Field Properties pane,
select Required
Ensures field must be
populated in all records
Required property
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
8
Setting Default Value Field
Properties
Value entered in
Default property

In Design view





Select the field
Select Default Value field
Enter the value desired
Ensures that field must be
populated in all records
Used primarily when the
majority of records will share
the same field value
Default Value
property
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
9
Validation Rule
Validation Text


Allows the construction of a logical rule that
data must comply with
Mathematical rules may be built with the
Expression Builder
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
10
Validation Text
Validation Text

Serves as an informative message or prompt
 Informs the user that data entered is
invalid
 Provides a general explanation of why
validation was not met
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
11
Validation Message
With validation text
Without validation text

Validation text



A user friendly message
Appears when data entry is violated
No validation text

Results in a difficult to understand message
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
12
Creating Lookup Fields


Lookup fields
 Speeds data entry
 Ensure data accuracy
Create a lookup field in
the easiest manner
 Select Lookup Wizard
in the drop-down data
type field box
 The Lookup Wizard will
begin
Select Lookup
Wizard
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
13
Using the Lookup Wizard –
Choose this options
Screen 1
to type in values

Type your own
values
Field list
OR

Use existing
values
Choose this option to
use existing values
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
14
Using the Lookup Wizard –
Screen 2
Type the
values
you wish
to use
OR
Choose the
tables and/or
queries needed
Choose the field
you wish to pull
values from
Type field values you desire
OR
 Choose the table/query and the fields needed

Copyright © 2008 Pearson Prentice Hall. All rights reserved.
15
Using the Lookup Wizard –
Screen 3
Click Enter a
label for the
column

When supplying your own values


You will be prompted to label (name) the column
Click Finish to end the Wizard
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
16
Using the Lookup Wizard –
Screen 3
Click Enter a
label for the
column

When using fields from a table/query

You will be prompted to choose the sort order for
your field
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
17
Using the Lookup Wizard –
Screen 4
Drag column
edge to adjust
width

When using fields from an existing table or
query

You will be prompted to adjust the width of your
columnCopyright © 2008 Pearson Prentice Hall. All rights reserved.
18
Using the Lookup Wizard –
Screen 5
Click Enter a
label for the
column

When using existing fields


You will be prompted to label (name) the
column
Click Finish to end the Wizard
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
19
Modifying a Lookup Field




Identify the record source of the data
Change the data in the source object (add or
delete values)
Data immediately added or deleted to the
field
This step does not reclassify existing data

Existing data must be updated manually or with a
query
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
20
Trouble Identifying Record
Source?
Row Source
value



Click the Lookup tab in the Field Properties section
Locate the Row Source property
Object name after the word FROM identifies source
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
21
Create a Multivalue Lookup Field
Click Enter a
label for the
column

To allow for more than one value

Select the Allow Multiple value check box
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
22
Input Masks




Specifies exact formatting of input data
Uses special characters for format
specification
Only used with text and date fields
Common mask characters



0 Digit (0 to 9, entry required)
9 Digit or space (entry not required)
# Digit or space (entry not required)
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
23
Create an Input Mask
Use the Input
Mask Wizard
Enter mask
characters directly
into Field
Properties
May be entered directly
OR
 May use the Input Mask Wizard

Copyright © 2008 Pearson Prentice Hall. All rights reserved.
24
Forms


Provide an attractive user interface
Aids in data entry
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
25
Methods of Form Creation

You can create a form using:






Form, Split and Multiple Form Tools
Datasheet Tool
Form Wizard
Blank Form Tool
Pivot Chart and Table Tools
Form Design View
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
26
The Form Tool
Drop data field
Form created with
Form tool



One click method
Creates a very simple and basic form
Can be altered and enhanced in Design view
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
27
The Split Form Tool
Form
View
Split Form
Tool
Datasheet
view

Creates a two part form including all fields



One section is in Form view
One section is in Datasheet view
Edits are synchronized
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
28
Multiple Items Tool
Multiple Items Tool


Resembles a datasheet
Faster navigation/data entry than normal
forms
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
29
Datasheet Forms

Identical copy of a form in
terms of looks
Has different protection
features applied than the
original form

To create



More Form command
Choose More Forms from
the Forms group
Select Datasheet from the
drop down list
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
30
PivotTable and PivotChart Forms
Work the same as in query/table
view
 Create from PivotChart button
on the Forms group
OR
 Click More Forms Tool in the
Forms group
 Choose PivotTable from the
drop down list

Copyright © 2008 Pearson Prentice Hall. All rights reserved.
More Form Tool
31
Using Design View to Create a
Form
Field List
Design Grid



Allows for customization and personalization of forms
Click the Blank Form tool in the Forms group
Drag Fields onto the Design grid
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
32
Action Buttons
Navigational Button
Button tool


Allows you to add navigational/other types of
buttons
Wizard guides you through the creation
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
33
Subforms
Primary form
(parent)
Subform (child)



Form that exists within another form
Updated/viewed separately or with its parent form
Normally, a one-to-many relationship exists between
the two forms
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
34
Form Customization

To customize a form

First remove the default
layout



Accomplished with the
Remove Layout tool
Reposition controls as
desired
Reposition several controls at
once by selecting multiple
(shift-click) controls
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
35
Form Customization

Also customize by:

Adding a theme



Click Autoformat on the
Format Tab
Add additional unbound
controls where useful
Use groupings, labels
and pictures for visual
effect
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
36
Copyright © 2008 Pearson Prentice Hall. All rights reserved.
37