Jan's Access Forms & Reports: Create Report Manually

You already learned a lot about managing controls in the lesson Controls in the section on forms. You worked with multiple controls at once in the lesson Creating a Form Manually. The same methods for selecting, formatting, aligning, and moving controls also work on reports. (If you skipped those lessons, you might want to review them now.)

Where to Make Adjustments

Getting a report laid out just right can be a frustrating experience. When controls are not quite lined up evenly, it is more obvious in a report on paper than it is in a form on the screen. Lines may print jagged if they are slanted. You cannot always tell from the Print Preview. The rendering on screen may not quite match what prints.

Common adjustments:

Errors in Expressions

When you use expressions to create calculated values, it is easy to make mistakes. Typing is not as easy as it looks!

Common types of errors in expressions:

Step-by-Step: Create Report Manually

What you will learn:

to create a report manually
to create a calculated control
to handle a circular reference

Start with: , resource files, worldtravel.mdb from the previous lesson

You are going to create a report that lists the clients of World Travel Inc. There are not too many records yet, but you have to start somewhere!

Think first! What arrangement will work well for the controls? Going across the page? Going down the page? Do you need all fields from the source or just some of them? What is the purpose for the report?? What order will the user need the data that you show?

In this case, World Travel Inc. wants to use the report to see an overview of where their clients are so that they can target their mailings and advertising better. You will only need the client's name and address this time.

Create Blank Report

If necessary, click on the Reports icon in the Database Window.
Click on the New button in the Database window's toolbar.
The New Report dialog opens with several choices.
Click on Design View.
Select the table Clients from the drop list.
Click on OK.
A blank report opens in Report Design View.
If necessary, open the Field List by clicking the Field List button on the toolbar.
It shows the fields from the Clients table.
Save the report with the name Clients Report.

Add Controls

You can add controls to your report by dragging them from the Field List or by using the Toolbox tools, just like you could for a form.

Select the fields for the name and address in the Field List:
FirstName, MiddleName, LastName, Suffix
Address, City, State, Region, PostalCode, Country
(Hint: Hold CTRL key down while clicking on each field.)
Drag and drop them on the Detail section.
Switch to Print Preview to see how this looks.
There are 6 pages with a lot of wasted space.

A single record takes a lot of space and is not easy to read in a column.

The records show in the order that they appear in the Clients table
Switch to Design View.

Create Calculated Text Value: FullName

It is more natural for people to read complete names and addresses instead of individual fields. You can concatenate the text to create those values. Recall that the ampersand character (&) is used to glue together text values.

You will edit some controls to combine text values and you will delete the rest of the labels and controls and move the controls into a neat arrangement. This is like what you would do to create mailing labels.

Select the label FirstName and edit it to read Name.

The default value for a label is the name of the Control Source for the matching data control. You can change a label without breaking anything.
Select the text box FirstName.
If necessary, open the Properties dialog.
You are going to change the Control Source to an expression that will combine the name parts.
Change the value for the Name property to FullName.
Why change the name? To avoid a circular reference! You need to include FirstName in the expression for the complete name in this same control.
Click in the Control Source property, which still reads FirstName, and then on the ellipsis button (with 3 dots) that appears.

The Expression Builder dialog opens.

This dialog gives you point-and-click access to all of the existing objects and functions in your database. It can really help when you are not quite sure of the syntax of a function or the name of an object or control.

You don't really need these helpful features this time. The names of the fields are easy to remember. The large text area just gives you room to see what you are typing.
Edit the text in the top section to read:
=[FirstName]&" "&[MiddleName]&" "&[LastName]&" "&[Suffix]
- The expression for a calculated control MUST start with the equals sign (=).
- The ampersand is the glue between two text values.
- The text values can be fields, like [FirstName] or literal text like " ", which produces a single space in the resulting text. Also common for calculated names is ", " which produces a comma and a space.
Click on OK to accept the new value for Control Source.
Click out of the Control Source property.
Now Access applies your expression to the control.
Switch to Print Preview.
Inspect each record. How do the records look?
- Some names are cut off.
- You do not need the MiddleName, LastName, and Suffix fields anymore. It was smart to leave them in place until after checking the width of the Name control. Sometimes it might not be clear whether or not characters have been cut off unless you can look at all of the fields.

Manage Name Fields

You can now get rid of several controls and labels and resize the calculated control.

Switch to Report Design View.
Delete the controls and labels for MiddleName, LastName, and Suffix.
Delete the label for the Name control.
Select the Name control.
In the Properties dialog, change the Width to 2".
(You could drag the right edge of the control to the right, using the ruler to determine when the control is 2" wide.)
Change the Can Grow property to Yes.
This will wrap the text onto another line if it won't fit.

The Clients table allows a total of 75 characters between the fields FirstName, MiddleName, LastName, Suffix! By allowing the control to grow, you are prepared for whatever the table will allow.
Click out to accept the change.
Switch to Print Preview. How do the records look?
- The longest current name shows completely!
- Extra white space where deleted controls were.
- Address control is too short
- Need to combine the other address fields in a calculated value.

Create Two Line Address

You will concatenate several fields to create the second line of an address. When it is obvious to the users what the data represents, you can omit the labels.

Switch to Report Design View.
Edit the Control Source for the City control to concatenate the fields City, State, Region and PostalCode.
=[City] & " " & [State] & " " & [Region] & " " & [PostalCode]
Click on the report's background to deselect the control.
Surprise! There is a green triangle in the upper left corner of the control. This means that there is a problem.
Click on the control again to select it.
A button appears.
Hover over the button and a down arrow appears.
Click the down arrow and a menu appears.
The problem is a circular reference in your expression. You used the name of the current control in the expression that defines the value for the control. This will not work!
Click on Edit Control Name .
Change the name to CityState and click out of the property to accept the change. The button disappears. Neat, and very helpful, too!
Delete the controls for State, Region, and PostalCode.
Delete the labels for Address, City, and Country.
Select both the Address and City controls.
Change the Width property to 2" wide.
(You can set both at the same time in the dialog. Dragging works too since they were the same size to begin with.)
Move the controls to new positions to match the illustration.
Drag the bottom edge of the Detail section upwards, to reduce the section to the smallest height possible.
Save the report. (Clients Report)
Switch to Print Preview. How do the records look?
Better.

This report is not very easy to read. In the next lesson you can make changes to add a title, group the records, and divide them from each other more clearly.

Forms & Reports:Create Report Manually

Report Controls