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