Step 1: Creating Data Environment Designer

Perform steps 1, 4, 5, and 12-14 from pages VB 6.66 - VB 6.67 of your book, substitute the steps listed below for
the other steps listed on those pages. In Place of the Crystal Report Control discussed in Chapter 6 of your book ,
Microsoft has developed its own Report module discussed below. This topic creates the customer data report using
a Data Environment designer as a data source. The Data Environment designer uses the Sound Advice
database developed in the lab steps 1 and 2 to create a simple report. The three reports require the use of three
tables, Customers, Employees, and Compact Discs. Only the exact steps are specified for the Customer Report.
Follow the same procedure to create the Employee and Compact Disc reports. The finished reports resemble the
figures on page VB 6.67 of your book.

Customer Report containing the following fields: Customer ID, Name, Address, City, State, Zip Code,
Balance and Employee ID.

Before you begin the step-by-step process, ensure that the Sound Advice database (Sound Advice.mdb) is present
on your floppy. If it is not present, complete the in the labs 1 and 2 from chapter 6 first.

To create a simple hierarchical cursor in the Data Environment designer:

1. Retrieve the Sound Advice.vbp project from your floppy disk.

2. On the Project menu, click Add Data Environment to add a designer to your project. If the
designer is not listed on the Project menu, click Components. Click the Designers tab, and click Data
Environment to add the designer to the menu.
3. Note The first four kinds of ActiveX designers loaded for a project are listed on the Project menu. If more
than four designers are loaded, the later ones will be available from the More ActiveX Designers submenu
on the Project menu.
4. Right-click on Connection1 to display the short-cut menu, click on Properties. On the Data Link
Properties dialog box, click Microsoft Jet 3.51 OLE DB Provider. This selects the correct OLE DB
provider for accessing a Jet database.
5. Click the Next button to get to the Connection tab.
6. Click the ellipsis button (...) next to the first text box.
7. Use the Select Access Database dialog box to navigate to the Sound Advice.mdb file, which should be
located on your floppy disk as a result of completing in the labs 1 and 2 at the end of chapter 6.
8. Click OK to close the dialog box.
9. Right-click the Connection1 icon, and click Rename. Change the name of the icon to SoundAdvice.
10. Right-click the SoundAdvice icon, and then click Add Command to display the Command1 dialog box.
In the dialog box, set the properties as shown below:

Property Setting

Command Name Customers

Connection Sound Advice

DataBase Object Table

Object Name Customers

11. Click OK to close the dialog box.

 12. Right-click the SoundAdvice icon, and then click Add Command to display the Command1 dialog box.
In the dialog box, set the properties as shown below:

Property Setting

Command Name Employees

Connection Sound Advice

DataBase Object Table

Object Name Employees

13. Click OK to close the dialog box.

14. Right-click the SoundAdvice icon, and then click Add Command to display the Command1 dialog box.
In the dialog box, set the properties as shown below:

Property Setting

Command Name CompactDiscs

Connection Sound Advice

DataBase Object Table

Object Name CompactDiscs

15. Click OK to close the dialog box.

16. Set the properties of the project and designer according to the settings below, then save the project:

Object Property Setting

Project Name Sound Advice3

DataEnvironment Name deSoundAdvice

Step 2: Creating the Data Report (Customers)

Once the Data Environment designer has been created, you can create a data report. Because all of the fields in the
data environment will be used in a report, this series of topics creates a report that displays all the fields.

To create a new data report:

1. On the Project menu, click Add Data Report, and Visual Basic will add it to your project. If the designer
is not on the Project menu, click Components. Click the Designers tab, and click Data Report to add the
designer to the menu.
2. Note The first four kinds of ActiveX designers loaded for a project are listed on the Project menu. If more
than four designers are loaded, the later ones will be available from the More ActiveX Designers submenu
on the Project menu.
3. Set the properties of the DataReport object according to the table below:

Property Setting

Name rptSoundAdviceCustomers

Caption Sound Advice Customer Report

4. On the Properties window, click DataSource and then click deSoundAdvice. Then
click DataMember and click Customers.
5. Important To set the DataSource property to deSoundAdvice, the Data Environment designer must be
open. If it is closed, press CTRL+R to display the Project window, then double-click the data environment
icon.
6. Right-click the Data Report designer, and click Retrieve Structure.
7. Change the Report Width property in the Property Window to 9000 twips.
8. Choose the Window top-menu option and click on Tile Vertically. This will place the Data Report
Designer and the Data Environment Designer on the screen next to each other. Move and close other
windows on your screen accordingly to provide the best working environment.
9. From the Data Environment designer, drag the Customer ID field (under the Customers command) onto
the Detail (Customers_Detail) section.
10. The Detail (Customers_Detail) section can contain any field from the Customers command.
11. Move the Label control (leftmost Customer ID box) (RptLabel) named Label1 to Page Header
(PageHeader) section right above the Customer ID field in the Detail (Customers_Detail) section.
12. If you do not want a Label control to be included with the TextBox control, you can uncheck the Drag and
Drop Fields Caption option on the Field Mapping tab of the Data Environment designer's Options dialog
box.
13. From the Data Environment designer, drag the Name field (under the Customers command) onto
the Detail (Customers_Detail) section. Move the Label control (leftmost Name box) RptLabel)
named Label2 to Page Header (PageHeader) section right above the Name field in the Detail
(Customers_Detail) section
.
14. From the Data Environment designer, drag the Address field (under the Customers command) onto
the Detail (Customers_Detail) section. Move the Label control (leftmost address box) RptLabel)
named Label3 to Page Header (PageHeader) section right above the Address field in the Detail
(Customers_Detail) section.
15. From the Data Environment designer, drag the City field (under the Customers command) onto the Detail
(Customers_Detail) section. Move the Label control (leftmost City box) RptLabel) named Label4 to Page
Header (PageHeader) section right above the City field in the Detail (Customers_Detail) section.
16. From the Data Environment designer, drag the State field (under the Customers command) onto the Detail
(Customers_Detail) section. Move the Label control (leftmost State box) RptLabel)
named Label5 to Page Header (PageHeader) section right above the State field in the Detail
(Customers_Detail) section.
17. From the Data Environment designer, drag the Zip Code field (under the Customers command) onto
the Detail (Customers_Detail) section. Move the Label control (leftmost Zip Code box) RptLabel)
named Label6 to Page Header (PageHeader) section right above the Zip Code field in the Detail
(Customers_Detail) section.
 18. From the Data Environment designer, drag the Balance field (under the Customers command) onto
the Detail (Customers_Detail) section. Move the Label control (leftmost Balance box) RptLabel)
named Label7 to Page Header (PageHeader) section right above the Balance field in the Detail
(Customers_Detail) section.
19. From the Data Environment designer, drag the Employee ID field (under the Customers command) onto
the Detail (Customers_Detail) section. Move the Label control (leftmost Employee ID box) RptLabel)
named Label8 to Page Header (PageHeader) section right above the Employee ID field in the Detail
(Customers_Detail) section.
20. To put the title in the Report Header (ReportHeader) section on the report perform the following steps.
Right-click in the Report Header (ReportHeader) section of the report on the short-cut menu point
to Insert Control then click on Report Title. This will put the title control in the Report Header
(ReportHeader) section of the report. Change the Caption property of the control in the property window
to read "CUSTOMER REPORT". To put the date in the Report Header (ReportHeader) section to
appear under the CUSTOMER REPORT header on the report perform the following steps. Right-click in
the Report Header (ReportHeader) section of the report on the short-cut menu point
to Insert Control then click on Current Date (Short Format). This will put the date control in the Report
Header (ReportHeader) section of the report. Increase the width of this report band and drag the date
control under the CUSTOMER REPORT header. See page VB 6.68 of your book to see the correct format
of the report header and the detail lines of the report.
21. To put your name, class, and section number in the Report Footer (ReportFooter) section on the report
perform the following steps. Right-click in the Report Footer (ReportFooter) section of the report on the
short-cut menu point to Insert Control then click on Label. This will put a label control in the Report
Footer (ReportFooter) section of the report. Change the Caption property of the control in the property
window to read "your name IFS 110 section number".
22. Resize the Data Report designer's sections to use as little space as possible in each of the report bands.
23. It's important to resize the height of the Details section to be as short as possible because the
height will be multiplied for every record in the Sound Advice database. Any extra space
below or above the detail line text boxes will result in unneeded space in the final report.
24. Save the project.

Step 3: Preview the Data Report Using the Show Method

Now that the data environment and the data report objects have been created, you are almost ready to run the
project. One step remains: to write code to show the data report.

To show the data report at run time

1. On the Project Explorer window, double-click the frmCustomers icon to display the Form designer.
2. On Toolbox, click the General tab.
3. When you add a Data Report designer to your project, its controls are added to the tab named DataReport.
To use the standard Visual Basic controls, you must switch to the General tab.
4. Click the CommandButton icon and draw a CommandButton on the Customer form between the Update
and the Close command buttons.
5. Set the properties of the Command1 control according to the table below:

Property Setting

Name cmdReport

Caption Report
 6. In the button's Click event, paste the code below.

Private Sub cmdReport_Click()

rptSoundAdviceCustomers.Show
End Sub

7. Save and run the project.

8. Click Report to display the report in print preview mode.

9. Use the techniques listed above to set up the Data Report Designers for the Employees and Compact
Discs tables in the Sound Advice database and place the Report command buttons on their
corresponding forms. When complete your project will contain one Data Environment (Sound Advice)
and three different Data Reports representing the Customers, Employees and Compact Discs tables in
the Sound Advice databas

Guide to designing
reports
Applies To

Access provides you with a number of tools that help

you to quickly build attractive, easy-to-read reports
that present the data in a way that best suits the needs
of its users. You can use the commands on
the Create tab to create a simple report with a single
click. You can use the Report Wizard to create a more
complicated report, or you can create a report by
adding all the data and formatting elements yourself.
Whichever method that you choose, you will probably
 make at least a few changes to the design of the report
to make it display the data the way that you want. This
article discusses the general process of designing a
report and then shows you how to add specific design
elements to your report.

In this article
 Decide how to lay out your report
 Use control layouts to align your data
 Add or remove report or page header and footer
sections
 Tips for formatting different data types

Decide how to lay out your

report
When you design a report, you must first consider how
you want the data arranged on the page and how the
data is stored in the database. During the design
process, you might even discover that the arrangement
of data in the tables will not allow you to create the
report that you want. This can be an indication that the
tables are not normalized — this means that the data is
not stored in the most efficient manner.

Make a sketch of your report

This step is not required — you might find that the
Access Report Wizard or the Report tool (both of which
are available on the Create tab, in the Reports group)
provide a sufficient starting design for your report.
However, if you decide to design your report without
using these tools, you might find it helpful to make a
rough sketch of your report on a piece of paper by
drawing a box where each field goes and writing the
field name in each box. Alternatively, you can use
programs such as Word or Visio to create a mockup of
the report. Whichever method that you use, be sure to
include enough rows to indicate how the data repeats.
For example, you can use a row for product
information, then several repeating rows for that
product's sales, and finally a row of sales totals for the
product. Then, the sequence repeats for the next
product and so on until the end of the report. Or,
perhaps your report is a simple listing of the data in the
table, in which case your sketch can contain just a series
of rows and columns.
Note: This technique is also very useful if you are designing a report for someone else. In this case,
the other person can draw the sketch before you begin work.

After you create your sketch, determine which table or

tables contain the data that you want to display on the
report. If all the data is contained in a single table, you
can base your report directly on that table. More often,
the data that you want is stored in several tables that
you must pull together in a query, before you can
display it on the report. The query can be embedded in
the RecordSource property of the report, or you can
create a separate, saved query and base the report on
that.

Decide which data to put in each

report section
Each report has one or more report sections. The one
section that is present in every report is the Detail
section. This section repeats once for each record in the
table or query that the report is based on. Other
sections are optional and repeat less often and are
usually used to display information that is common to a
group of records, a page of the report, or the entire
report.

The following table describes where each section is

located and how the section is typically used.
Section Location Typical
contents
Report Appears only once,  Report title
header at the top of the  Logo
section first page of the  Current
report. date

Report Appears after the Report totals

footer last line of data, (sums, counts,
section above the Page averages, and
Footer section on so on)
the last page of
the report.
Page Appears at the top  Report title
header of each page of  Page
section the report. number

Page Appears at the  Current

footer bottom of each date
section page of the report.  Page
number

Group Appears just The field that

header preceding of a is being
section group of records. grouped on
Section Location Typical
contents
Group Appears just after Group totals
footer a group of records. (sums, counts,
section averages, and
so on)

For information about adding or removing report

header and footer sections or page header and footer
sections, see the section Add or remove report or page
header and footer sections in this article. You can add
group header and footer sections by using the Group,
Sort, and Total pane in Layout view or Design view.

Decide how to arrange the detail data

Most reports are arranged in either a tabular or a
stacked layout, but Access gives you the flexibility to
use just about any arrangement of records and fields
that you want.

Tabular layout A tabular layout is similar to a

spreadsheet. Labels are across the top, and the data is
aligned in columns below the labels. Tabular refers to
the table-like appearance of the data. This is the type of
report that Access creates when you click Report in
the Reports group of the Create tab. The tabular
layout is a good one to use if your report has a
relatively small number of fields that you want to
display in a simple list format. The following illustration
shows an employee report that was created by using a
tabular layout.

Stacked layout A stacked layout resembles a form

that you fill out when you open a bank account or
make a purchase from an online retailer. Each piece of
data is labeled, and the fields are stacked on top of
each other. This layout is good for reports that contain
too many fields to display in a tabular format — that is,
the width of the columns would exceed the width of
the report. The following illustration shows an
employee report that was created by using a stacked
layout.

Note: In the Report Wizard, this layout is referred to as a columnar layout.

Mixed layout You can mix elements of tabular and

stacked layouts. For example, for each record, you can
arrange some of the fields in a horizontal row at the
top of the Detail section and arrange other fields from
the same record in one or more stacked layouts
beneath the top row. The following illustration shows
an employee report that was created by using a mixed
layout. The ID, Last Name, and First Name fields are
arranged in a tabular control layout, and the Job Title
and Business Phone fields are arranged in a stacked
layout. In this example, gridlines are used to provide a
visual separation of fields for each employee.
Justified layout If you use the Report Wizard to
create your report, you can choose to use a justified
layout. This layout uses the full width of the page to
display the records as compactly as possible. Of course,
you can achieve the same results without using the
Report Wizard, but it can be a painstaking process to
align the fields exactly. The following illustration shows
an employee report that was created by using the
Report Wizard's justified layout.
The justified layout is a good layout to use if you are
displaying a large number of fields on the report. In the
preceding example, if you use a tabular layout to
display the same data, the fields extend off the edge of
the page. If you use a stacked layout, each record takes
up much more vertical space, which wastes paper and
makes the report more difficult to read.

Top of Page

Use control layouts to align

your data
Control layouts are guides that you can add to a report
while it is open in Layout view or Design view. Access
adds control layouts automatically when you use the
Report Wizard to build a report, or when you create a
report by clicking Report in the Reports group of
the Create tab. A control layout is like a table, each cell
of which can contain a label, a text box, or any other
type of control. The following illustration shows a
tabular control layout on a report.

The orange lines indicate the rows and columns of the

control layout, and they are visible only when the
report is open in Layout view or Design view. Control
layouts help you achieve a uniform alignment of data in
rows and columns, and they make it easier to add,
resize, or remove fields. By using the tools in
the Table and Position groups on the Arrange tab
(available in Layout view or Design view), you can
change one type of control layout to another, and you
can remove controls from layouts so that you can
position the controls wherever you want on the report.

Top of Page

Add or remove report or page

header and footer sections
As mentioned earlier in this article, headers and footers
are report sections that you can use to display
information that is common to the entire report, or to
each page of a report. For example, you can add a Page
Footer section to display a page number at the bottom
of each page, or you can add a Report Header section
to display a title for the entire report.

Add report or page header and footer

sections
1. In the Navigation Pane, right-click the report that
you want to change, and then click Design View on
the shortcut menu.
2. Verify which sections are already on the report. The
sections are separated by shaded horizontal bars
called section selectors. The label on each section
selector indicates what the section directly below it
is.

Every report has a Detail section and can also

contain Report Header, Page Header, Page Footer,
and Report Footer sections. In addition, if there are
grouping levels in the report, you might see group
headers or footers (such as the File As
Header shown in the preceding illustration). By
default, group headers and footers are named by
using the field name or expression that is the basis
of the group. In this case, the name of the grouping
field is "File As."

3. To add page header and footer sections or report

header and footer sections to your report, right-click
 any section selector and then click Page
Header/Footer or Report Header/Footer on the
shortcut menu.
Caution: If the section is already present on the report, Access warns you that it will delete the
existing section and the controls it contains.

You can now move existing controls or add new

controls to the new sections.

Access always adds page and report header and footer

sections in pairs. That is, you cannot add a page or
report header section without also adding the
corresponding footer section. If you do not need both
sections, you cannot delete a section, but you can
resize the unused section to a height of zero (0) to
avoid adding extra vertical spacing to your report.
Position the pointer at the bottom of the unused
section until it turns into a double-headed arrow ,
and then drag upward until the section is hidden. If
there are any controls in the section, you must delete
them before you can fully hide the section.
Remove report or page header and
footer sections
1. In the Navigation Pane, right-click the report that
you want to change, and then click Design View on
the shortcut menu.
2. Right-click any section selector and then click Page
Header/Footer or Report Header/Footer on the
shortcut menu.

If you are removing a header and footer pair and those

sections contain controls, Access warns you that
deleting the sections will also delete the controls and
that you will not be able to undo the action.
Click Yes to remove the sections and delete the
controls, or click No to cancel the operation.

Top of Page

Tips for formatting different

data types
When you create a report by using the Report tool
(available on the Create tab, in the Reports group), or
by using the Report Wizard, Access adds the fields to
the report for you and creates the most appropriate
control to display each field, based on the field's data
type. If you are adding fields to a report yourself, the
preferred method is to drag each field from the Field
List to the report. As with the Report Wizard or
the Report tool, Access creates the most appropriate
control for each field, depending on the field's data
type. For most data types, the most appropriate
(default) control to use is the text box.

The following sections provide tips about how to

format some of the special case data types.

Multivalued fields The default control for a

multivalued field is a combo box. This can seem like a
strange choice for a control on a report, because you
can't click the arrow on a combo box in a report.
However, in the context of a report, a combo box
behaves like a text box. The arrow is visible only in
Design view.

If the field contains multiple values, those values are

separated by commas. If the combo box is not wide
enough to display all the values on one line and
the CanGrow property of the combo box is set to Yes,
the values wrap to the next line. Otherwise, the values
are truncated. To set the CanGrow property for a
control, open the report in Design view or Layout view,
click the control, and then press F4 to display the
control's property sheet. The CanGrow property is
located on both the Format tab and the All tab of the
property sheet for the control.

Rich text fields The default control for a rich text

field is a text box. If the text box is not wide enough to
display all the values on one line and
the CanGrow property of the text box is set to Yes, the
values wrap to the next line. Otherwise, the values are
truncated. To set the CanGrow property for a control,
open the report in Design view or Layout view, click the
control, and then press F4 to display the control's
property sheet. The CanGrow property is located on
both the Format tab and the All tab of the property
sheet for the control.

A rich text field helps you to format text in a variety of

ways. For example, several words in a field can be
underlined, and several other words in the same field
can be in italic. You can, however, still set an overall
formatting style for the text box that contains the rich
text. The text box formatting applies only to the text
that has not been specifically formatted by using rich
text formatting.

Set formatting styles for a text box

that displays a rich text field
1. Right-click the report in the Navigation Pane, and
then click Layout View on the shortcut menu.
2. Click the text box that displays the rich text field, and
then, on the Format tab, in the Font group, click the
formatting style that you want to apply.

Access applies the formatting to all text in the rich

text field that has not already had that type (but not
value) of formatting applied in a view that supports
data entry, such as Datasheet view for a table or
query, or Form view for a form. For example, if a
portion of the text in the field is formatted with a red
font color, and you apply a blue font color to the text
box, Access turns all of the text blue except for that
which was individually formatted as red. As another
example, if a portion of the text in the field is
formatted with an 11-point font size, and you apply
a 14-point font size to the text box, Access applies
the 14-point font size to all of the text except for
that which was individually formatted at 11 points.

Attachment fields Attachment fields use a special

control that is not used for any other data type. You
can attach multiple files to a record by using a single
Attachment field, but the field can only display
information about one attachment at a time. By
default, the attachment control displays either an
icon or an image, depending on the file type of the
attachment that is currently displayed by the control.
If you want, you can set the properties for the
attachment control so that all attached files are
displayed as icons, or so that the field simply
displays a paperclip icon and the number of
attachments. Assuming that you already use an
attachment control on your report, you can use the
following procedure to adjust the control's
properties for different uses of the control.
Set the display properties for an
Attachment field
1. Right-click the report in the Navigation Pane, and
then click Layout View on the shortcut menu.
2. Click the attachment control. If the property sheet is
not already displayed, press F4 to display it. On the
property sheet, click the Format tab.

Use the following table as a guide for setting the

attachment control's properties.
Property Setting
Display As  Image/Icon displays graphics as images and
all other files as icons. This is the default
setting.
 Icon displays all files as icons.
 Paperclip displays a paperclip icon followed
by the number of attachments in parentheses.

Default To make a default picture appear in the

Picture attachment control when there are no attached
files, click in the property box, browse to the
picture that you want, and then click Open.

Note: The default picture is not displayed if the Display

As property is set to Paperclip.

Picture Select the alignment that you want from the list.
Alignment The default setting is Center. Adjusting this setting
can produce unexpected results, depending on the
setting of the Picture Size Mode property.
 Property Setting
Picture Size This setting is available only if the Display
Mode As property is set to Image/Icon.

 Clip displays the image in its actual size. The

image is clipped if it is too big to fit inside the
control.
 Stretch stretches the image so that it fills the
entire control.

Note: Unless the attachment control is the same

exact size as the image, using this setting will distort
the image, making it appear stretched either
vertically or horizontally.

 Zoom displays the image as large as possible

without clipping or distorting the image. This
is the default setting.

3. If you are using the control to display graphics,

adjust the size of the attachment control so that you
can see the amount of detail that you wan