PowerPoint Presentation to Accompany
GO! with Microsoft® Access 2010
Chapter 7
Creating Advanced Forms and Reports
with Microsoft Access 2010
© 2011 Pearson Education, Inc. Publishing as Prentice Hall
1
Objectives
• Create a split form
• Create a form and a subform
• Create a multi-page form
• Create and modify a subreport
• Create a report based on a parameter
query
• Create an alphabetic index
with Microsoft Access 2010
© 2011 Pearson Education, Inc. Publishing as Prentice Hall
2
Create a Split Form
• Split form displays data in two views
– Form view
– Datasheet view
• Both views on single form
• Data in each view
– From same source and synchronized
with Microsoft Access 2010
© 2011 Pearson Education, Inc. Publishing as Prentice Hall
3
Create a Split Form
• Able to add, delete, edit data in either
view
• Advantage of a split form
– Find record in Datasheet view
– Then edit same record in Form view
with Microsoft Access 2010
© 2011 Pearson Education, Inc. Publishing as Prentice Hall
4
Create a Split Form
• Split form displays in Layout view
with Microsoft Access 2010
© 2011 Pearson Education, Inc. Publishing as Prentice Hall
5
Create a Split Form
• Split form properties listed in
Property Sheet
with Microsoft Access 2010
© 2011 Pearson Education, Inc. Publishing as Prentice Hall
6
Create a Split Form
• To convert existing form to a split
form
– Form must be open in Design view
– On Property Sheet Format tab:
• Click in Default View property setting box
• Click arrow
• Click Split Form, Close
with Microsoft Access 2010
© 2011 Pearson Education, Inc. Publishing as Prentice Hall
7
Create a Form and a Subform
• A subform is embedded in a main
form
• A subform is used to
– View data, enter data, and edit data related
to main form
with Microsoft Access 2010
© 2011 Pearson Education, Inc. Publishing as Prentice Hall
8
Create a Form and a Subform
• Subform similar to subdatasheet
• Form created from single table with
one-to-many relationship
– Then Access can add subform datasheet
– Subform displays records related to main
form
with Microsoft Access 2010
© 2011 Pearson Education, Inc. Publishing as Prentice Hall
9
Create a Form and a Subform
• To create a many-to-many table
relationship
– Must create a junction table
– Two one-to-many table relationships
created
• Junction table joins the two relationships
• Junction tables have primary keys from the two
tables
with Microsoft Access 2010
© 2011 Pearson Education, Inc. Publishing as Prentice Hall
10
Create a Form and a Subform
• Junction table
with Microsoft Access 2010
© 2011 Pearson Education, Inc. Publishing as Prentice Hall
11
Create a Form and a Subform
• Form created after using junction
table
with Microsoft Access 2010
© 2011 Pearson Education, Inc. Publishing as Prentice Hall
12
Create a Form and a Subform
• Use Form Wizard to create form and
subform when
– Subform design requires more control
OR
– More than one relationship established
between one side and other tables or
queries
• Subform not automatically created when form
created
with Microsoft Access 2010
© 2011 Pearson Education, Inc. Publishing as Prentice Hall
13
Create a Form and a Subform
• Second Form Wizard dialog box
– Shows preview of how data arranged
with Microsoft Access 2010
© 2011 Pearson Education, Inc. Publishing as Prentice Hall
14
Create a Form and a Subform
• Third Form Wizard dialog box
– Permits selection of two subform layouts
• Tabular
• Datasheet
– Tabular more customizable than datasheet
– Both use rows and columns
with Microsoft Access 2010
© 2011 Pearson Education, Inc. Publishing as Prentice Hall
15
Create a Form and a Subform
• Fields removed from stacked layout
– Easier to make adjustments to controls
with Microsoft Access 2010
© 2011 Pearson Education, Inc. Publishing as Prentice Hall
16
Create a Form and a Subform
• Create a subform by dragging related
table onto existing form
– Many side of relationship incorporated in
existing form with one side of relationship
– Helpful when table has more than one
relationship with other tables
– Existing form must be in Design view
with Microsoft Access 2010
© 2011 Pearson Education, Inc. Publishing as Prentice Hall
17
Create a Multi-Page Form
• Multi-page form displays data from
underlying table or query
– Displays on more than one page
• Enables division of long form into
sections displayed on pages, OR
• Subforms on different tab within
main form
with Microsoft Access 2010
© 2011 Pearson Education, Inc. Publishing as Prentice Hall
18
Create a Multi-Page Form
• Displays only necessary data
• Displays data in organized format
• Can transform existing form into
multi-form through tab controls
– Used to display data on main form on
different tabs
– Each tab represents separate form page
with Microsoft Access 2010
© 2011 Pearson Education, Inc. Publishing as Prentice Hall
19
Create a Multi-Page Form
• Tab control inserted into Detail
section of form
with Microsoft Access 2010
© 2011 Pearson Education, Inc. Publishing as Prentice Hall
20
Create and Modify a
Subreport
• Subreport is a report embedded in a
main report
• Main report can
– Contain a subform instead of subreport
– Contain up to seven levels of subforms and
subreports
– Be bound or unbound
with Microsoft Access 2010
© 2011 Pearson Education, Inc. Publishing as Prentice Hall
21
Create and Modify a
Subreport
• Bound report displays data from
– Underlying table, query, or SQL statement
• SQL statement uses Structured Query
Language
• Unbound report does not display
such data
with Microsoft Access 2010
© 2011 Pearson Education, Inc. Publishing as Prentice Hall
22
Create and Modify a
Subreport
• Use SubReport Wizard to create
subreport
– Underlying table/query relationships must
be established first
• Initial subreport control entered in
report is unbound
– No link to record source exists
with Microsoft Access 2010
© 2011 Pearson Education, Inc. Publishing as Prentice Hall
23
Create and Modify a
Subreport
• Subreport Wizard permits selection
of subreport
– Table, query, and fields to be used
• Subreport Wizard allows defining
linking fields between
– Main report and subreport
with Microsoft Access 2010
© 2011 Pearson Education, Inc. Publishing as Prentice Hall
24
Create and Modify a
Subreport
• Main report and subreport created
using Subreport Wizard
with Microsoft Access 2010
© 2011 Pearson Education, Inc. Publishing as Prentice Hall
25
Create and Modify a
Subreport
• Like main report, subreports can be
modified
– Performed through conditional formatting
• Conditional formatting applies
formatting to controls
– Based on a comparison to a rule set
with Microsoft Access 2010
© 2011 Pearson Education, Inc. Publishing as Prentice Hall
26
Create and Modify a
Subreport
• New Formatting Rule dialog box
with Microsoft Access 2010
© 2011 Pearson Education, Inc. Publishing as Prentice Hall
27
Create and Modify a
Subreport
• Can create a subreport by
– Adding an object to an existing report
– Dragging existing objects onto a report
• Objects include forms, subforms, queries,
reports or subreports
with Microsoft Access 2010
© 2011 Pearson Education, Inc. Publishing as Prentice Hall
28
Create and Modify a
Subreport
• If subreport does not display fields
as expected
– Use the Subreport Field Linker dialog box
– When unsure of which fields to link
• Click on Suggest button
with Microsoft Access 2010
© 2011 Pearson Education, Inc. Publishing as Prentice Hall
29
Create and Modify a
Subreport
• Total from subreport can display on
main report
– To see changes made to subreport in main
report
• Must first close subreport
– Sum added to subreport field causes
• Display of sum in the main form’s subreport
control
with Microsoft Access 2010
© 2011 Pearson Education, Inc. Publishing as Prentice Hall
30
Create a Report Based on a
Parameter Query
• Parameter query prompts for criteria
• Prompt appears prior to running
query
• Parameter query used as the record
source
• Enables user to set criteria for report
• Message box used to enter criteria
for parameter query
with Microsoft Access 2010
© 2011 Pearson Education, Inc. Publishing as Prentice Hall
31
Create a Report Based on a
Parameter Query
• Records displayed based upon
parameter values entered
with Microsoft Access 2010
© 2011 Pearson Education, Inc. Publishing as Prentice Hall
32
Create a Report Based on a
Parameter Query
• Parameters used in creation of report
– Can be printed on report
– Must add a text box control to report
– In text box control, Control Source property
• Can use Expression Builder to enter parameter
expression
• Parameter expression must start with = sign
• Parameter concatenation must be joined with &
sign
with Microsoft Access 2010
© 2011 Pearson Education, Inc. Publishing as Prentice Hall
33
Create a Report Based on a
Parameter Query
• Expression Builder dialog box used
to enter parameter query criteria
with Microsoft Access 2010
© 2011 Pearson Education, Inc. Publishing as Prentice Hall
34
Create an Alphabetic Index
• Reports can display an alphabetic
index
• Alphabetic index groups items
– To create an alphabetic index of a field,
report must group that field
with Microsoft Access 2010
© 2011 Pearson Education, Inc. Publishing as Prentice Hall
35
Covered Objectives
• Create a split form
• Create a form and a subform
• Create a multi-page form
• Create and modify a subreport
• Create a report based on a parameter
query
• Create an alphabetic index
with Microsoft Access 2010
© 2011 Pearson Education, Inc. Publishing as Prentice Hall
36
All rights reserved. No part of this publication may be reproduced, stored in a
retrieval system, or transmitted, in any form or by any means, electronic,
mechanical, photocopying, recording, or otherwise, without the prior written
permission of the publisher. Printed in the United States of America.
Copyright © 2011 Pearson Education, Inc.
Publishing as Prentice Hall
with Microsoft Access 2010
© 2011 Pearson Education, Inc. Publishing as Prentice Hall
37
37
Descargar

Create a Split Form - Missouri Western State University