ACCESS LNC

week_9_pdf4.pdf

Home >Computer Science homework help >ACCESS LNC

Week 9 - What We'll Be Working on This Week Now that you have a clear understanding of how forms work and how to create them, we're going to start adding the cool things that will take your forms to a higher level, in terms of what they're capable of doing for you. We will add a number of controls, to calculate fields in a form or subform, to provide drop-down lists for data entry, and to display fields on the form such as check boxes, option buttons, or toggle buttons. We are also going to look at some of the properties you can attach to the objects on forms, to control when they are displayed, whether or not the cursor will stop on the object when the Tab key is pressed, and whether or not the object may be changed in any way. In addition, we are going to apply conditional formatting to forms so that the appearance of the data will change based on whether or not the specified condition has been met. This will pretty much do it for forms, and provide you with some slick options for your own database files. The final section of this week's lessons will deal with using the Report Wizard to create reports. This week we will create a series of simple reports based on one or two tables, and make some modifications to the finished report. Then next week we will continue with reports, and see how they can be further modified and customized.

Goals:

 Adding calculated controls to a form page 2

 Adding calculated controls to a subform page 8

 Changing properties for the form page 13

 Applying conditional formatting on the form page 15

 Creating a List Box on a form page 21

 Adding a combo box to the form page 26

 Adding a check box, control button, and toggle button to the form page 30

 Attaching a control source to a check box, option button, or toggle button control on the form page 33

 Creating an option group on the form page 35

 Creating a report page 43

 Creating a report based on one table with the Report Wizard page 48

 Modifying the report page 52

 Changing the sort order of the data in the report page 55

 Basing a report on multiple tables page 59 This is where we're headed. So let's get to it!

Goals for this section:

 Adding a calculated control to a form

 Adding a calculated control to a subform

 Changing properties for the form

 Applying conditional formatting on the form

Types of controls

 Bound control - A control whose source of data is a field in a table or query is called a bound control. You use bound controls to display values that come from fields in your database. The values can be text, dates, and numbers, Yes/No values, pictures, or graphs. For example, a text box that displays an employee's phone number might get this information from the Phone Number field in the Employees table. You bind a control to a field by identifying the field from which the control gets its data. You can create a control that is bound to the selected field by dragging the field from the Field List pane to the form or report. The Field List pane displays the fields of the form's underlying table or query. To display the Field List pane, open the object in Layout view or Design view, and then on the Design / Tools group, click Add Existing Fields. When you double-click a field in the Field List pane, Access adds the appropriate type of control for that field to the object.

 Unbound control - A control that doesn't have a source of data (such as a field or expression) is called an unbound control. You use unbound controls to display information, pictures, lines or rectangles. For example, a label that displays the title of a form is an unbound control. A phone number field that has the label Phone number but is missing the value to be displayed is shown as an Unbound control

Calculated control - A calculated control gets its value from values in the table and is an expression containing functions and operators, in addition to fields, that produces a result. You specify the value that you want to use as the source of data in the control by defining an expression. An expression can be a combination of operators (such as = and + ), control names, field names, functions that return a single value, and constant values. The value shown in a calculated control changes as the values in the underlying fields change, but you cannot directly edit a calculated control. As we saw with queries, calculations are relatively easy to add to your Access database so that you don't need to bother with

exporting the data to Excel and then bringing it back in order to do the calculations that are missing. You can add calculated controls to your forms and reports, as well, using the Text Box tool in the Design / Controls group. Calculated controls are unbound because they do not update table fields. These fields cannot be edited on either forms or reports. In order for a calculated control to work, the fields referenced in the formula do not need to be physically present on the form or report in which the calculated control resides. As long as the fields exist in the record source, Access will be able to locate the data and perform the indicated calculation. This is particularly good news where forms are concerned, because you might have a value in an auto-enter field that needs to be part of a formula but doesn't need to be displayed on the form. For example, if you have created an auto-enter date field in a table to keep track of the creation date for records, you could then subtract that date from the current date to determine the number of days that have elapsed since the record was created. Because Access is entering the record creation date for each record automatically when the record is created, there's absolutely no reason to see that field on the form. Access 2010 added a new Data type - Calculated. Calculated Control vs. Calculated Data type. Do not confuse a calculated expression in a form, report or query with the new Calculated data type. Calculated data types store the result of their expression directly in the table, but calculated expressions are calculated only when Access executes the expression. Calculated columns are part of life on a spreadsheet, but do not belong in a database table. Never store a value that is dependent on other fields: it's a basic rule of normalization. Break the rule, and you have to worry about how to maintain the calculation correctly in every possible circumstance. This may seem simple, but it creates more problems than it solves. You will quickly find that the expressions are limited. You will also find it makes your database useless for anyone who needs to use it in versions of Access prior 2007. Instead of reading the data, they will get a message telling them that Access cannot read the data. Even more serious, the calculated results are not reliable. If you change the Expression after data has been entered into the table, the existing results may not be updated correctly, so you cannot rely on the results. A compact/repair does not recalculate, so there is no obvious way to repair the bad results. My personal suggestion is not to use the Calculated Data type in design but rather use a calculated control as we will see. As you will see, the calculation in the control is based on an expression you enter in the Control Source property box of the Property Sheet. An expression always begins with an equal sign (=), contains operators and functions, in addition to the names of the fields or other controls involved in the calculation. An example of a calculated control that sums the material cost and the labor cost is:

=[curMaterialCost]+[curLaborCost] This calculated control sums the values in these two currency fields. Case doesn't matter when you type the field names, but spelling is critical. In addition, all formulas must begin with an equal sign (as in Excel) and no spaces should be left between the different items in the formula. There are five types of operators are available for use in expressions.

 Arithmetic operators: You use the arithmetic operators to calculate a value from two or more numbers or to change the sign of a number from positive to negative or vice versa.

Operator Purpose Example

+ Sum two numbers [Subtotal]+[SalesTax]

- Find the difference between two numbers or indicate the negative value of a number.

[Price]-[Discount]

* Multiply two numbers. [Quantity]*[Price]

/ Divide the first number by the second number. [Total]/[ItemCount]

\ Round both numbers to integers, divide the first number by the second number, and then truncate the result to an integer.

[Registered]\[Rooms]

MOD Divide the first number by the second number, and then return only the remainder.

[Registered] Mod [Rooms]

^ Raise a number to the power of an exponent. Number ^ Exponent

COUNT Displays the number of records with a value in the Bid Number field

COUNT([Bid Number])

SUM Displays the total of the values obtained by multiplying the Qty and Price values in each record.

SUM([Qty]*[Price])

 Comparison operators: You use the comparison operators to compare values and return a result that is True, False, or Null.

Note: In all cases, if either the first value or the second value is null, then the result is also null. Because null represents an unknown value, the result of any comparison with a null value is also unknown.

Operator Purpose Example

< Returns True if the first value is less than the second value. Value1 < Value2

<= Returns True if the first value is less than or equal to the second value.

Value1 <= Value2

> Returns True if the first value is greater than the second value Value1 > Value2

>= Returns True if the first value is greater than or equal to the second value.

Value1 >= Value2

= Returns True if the first value is equal to the second value. Value1>= Value2

<> Returns True if the first value is not equal to the second value Value<> Value2

 Logical operators: You use the logical operators to combine two Boolean values and return a true, false, or null result. Logical operators are also referred to as Boolean operators.

Operator Purpose Example

AND Returns True when Expr1 and Expr2 are true. Expr1 And Expr2

OR Returns True when either Expr1 or Expr2 is true. Expr1 OR Expr2

EQV Returns True when both Expr1 and Expr2 are true, or when both Expr1 and Expr2 are false.

Expr1 EQV Expr2

NOT Returns True when Expr is not true. Not Expr

Xor Returns True when either Expr1 is true or Expr2 is true, but not both. Expr1 Xor Expr2

 Concatenation operators: You use the concatenation operators to combine two text values into one.

Operator Purpose Example

& Combines two strings to form one string. string1 & string2 string1: Harriet string2: Shanzer result: HarrietShanzer string1: Harriet string2: Null result: Harriet

+ Combines two strings to form one string and propagates null values (if one value is Null, the entire expression evaluates to Null).

string1 + string2 string1: Harriet string2: Shanzer result: HarrietShanzer string1: Harriet string2: Null result: Null value

 Special operators: You use the special operators to return a True or False result as described in the following table.

Operator Purpose Example

Is Null or Is Not Null

Determines whether a value is Null or Not Null. Field1 Is Not Null

Like "pattern" Matches string values by using the wildcard operators ? and *.

Field1 Like "instruct*"

Between val1 And val2

Determines whether a numeric or date value is found within a range.

Field1 Between 1 And 10 - OR - Field1 Between #07-01-07# And #12-31-07#

In(val1,val2...) Determines whether a value is found within a set of values.

Field1 In ("red","green","blue") - OR - Field1 In (1,5,7,9)

Hands-On Activity: Adding a calculated control to a form

Before beginning: Your Home Tech Repair database file is open, and there are no objects currently open.

Our objective is to calculate the gross pay for employees in the frmEmployeeHRDataEntry form, by multiplying the curHourlyRate value by the value in the sngHours field.

1. Display the frmEmployeeHRData in Form View

Note: If for some reason you don't have the frmEmployeeData form, open tblEmployeeHRData and create a Form based on the table. (Create / Forms group Form icon.)

2. Observe the Hourly Rate (Currency) field and the Hours (Number) field. These fields can be used in

a formula to calculate the Gross Pay for each employee. 3. Switch to Design View.

4. In the Design / Controls group, click the Text Box icon . The mouse pointer changes to the Text Box icon with a plus sign attached to it.

5. In the grey Detail section of the form, click to the right of the curHourlyRate and sngHours field controls. When you do, an Unbound control is added to the form.

Note: On your form the number of the label object (which is reflected in the label to the left of the object) will probably be different than the one that is reflected in the above graphic (Text31:) Remember that Access sequentially numbers every object that is created, so the number of this particular object will be determined by what you have already added to your database. But remember – this is a temporary name that you're going to change, so you shouldn't waste time wondering why a particular number has been assigned to the object. You have much more important things to think about than the number of the object!

6. Click in the Unbound control, to place the cursor there. When you do, the word Unbound is removed

from view, and the cursor flashes in the object.

7. Type the following formula to multiply the Hourly Rate by the number of hours: =[curHourlyRate]*[sngHours] 8. Press Enter. Pressing Enter after you create a formula is always a good idea, because if there are

any errors Access will tell you so immediately.

9. Assuming you typed the formula correctly and you did not get any error messages, you should now see selection handles around the periphery of the object.

[If you got an error when you pressed Enter, go back and try the formula again. Chances are you spelled one of the field names wrong or added spaces where there shouldn't be any.]

10. Switch to Form View and observe the calculation:

 Access doesn't automatically format the contents of a calculated control. We'll have to format this value to Currency with 2 decimal places.

 The label will also have to be changed so it's more indicative of the contents of the calculated control.

11. Switch back to Design View.

12. With the calculated control still selected, click the Property Sheet icon on the Design / Tools group to display the Properties window for this object.

13. Click the Format tab of the Properties window, to display the

14. Click the to display the drop-down list of Format options.

15. Scroll down in the list, to locate and select Currency.

16. Click in the Decimal Places line.

17. Click the to display the drop-down list, and select 2.

18. Close the Properties window.

19. Click in the label object, to the left of the calculated control.

20. Once the label object is selected, double-click on the current (temporary) name, to select it.

21. Type Gross Pay as the new label for the calculated control. [Leave the colon after the name.]

22. Click anywhere off the label object, to remove the selection.

23. Move the label object so it is fully visible and close to the calculated control.

24. Switch to Form View and look at the changes.

25. Switch back to Design View and make any other necessary changes so that your form looks similar to the following:

Note: If the calculated control field is not wide enough to display the value, you will see: ##### in the control field. If that is the case, you can widen the field while you are in Form View.

26. Re-save and close the form when done.

On Your Own Create a Form based on the tblWorkOrders table. On the form, calculate the number of days that the job took, by subtracting the Start Date from the Completion Date. Label the calculated control: Length of Job (days). Place the calculated control to the right of the Start Date and Completion date fields on the form. Place the label above the calculated control. Right-align the contents of the calculated control and the label. Save the form as frmWorkOrderJobLength. Change the label that is displayed in the title bar of the form window so that it reflects a user friendly name of the form, rather than the name of the Lesynski form name. Your finished form should look similar to the following:

When finished, save and close the form and close tblWorkOrders.

Adding a Calculated Control to a Subform In Access you have the ability to add calculated controls not only to forms, but also to subforms. To add a calculated control to a subform, display the subform in Design View and create the control as you would normally do on any form. Re-save and close the form. When you subsequently open a form in which that subform has been embedded, the calculation will appear.

Hands-On Activity: Adding a calculated control to a subform

Before beginning: The Home Tech Repair database file is open, and there are no objects currently open.

Our objective is to calculate the total cost of each job by adding the Material and Labor cost in the frmWorkOrderSubform. Because this subform has been embedded in the frmCustomer form, the calculation will appear there, as well.

1. Display the frmWorkOrderSubform form in Design View.

2. Click the Text Box icon in the Design / Controls group .

3. Click in the grey area of the Detail section of the form to the right of the Material Cost and Labor Cost fields.

4. Click in the Unbound control object, and add the following formula:

=[curMaterialCost]+[curLaborCost]

5. Press Enter.

6. Select the label object, and double-click the contents to select the temporary label name.

7. Change the name to: Total Job Cost (Remove the colon).

8. Format the calculated control to Currency with 2 decimal places, and right-align the value.

9. Move the field label above the calculated control.

10. This form was originally created as having a Datasheet format. In order to see it in a more user friendly form. For this exercise, go to the Property Sheet and select Single Form from the Form Format / Default View: Switch to Form View and observe the calculated control.

11.

Note: If you are using a subform in Datasheet view when it is embedded in another form, you have to switch to Datasheet view to adjust how the datasheet looks and then save the subform in Datasheet view to preserve the look you want. You must also use the Datasheet view of the form to make adjustments to fonts and row height. The font in Datasheet view is independent of any font defined for the controls in Form View. If you build a tabular form and then decide to use it as a subform in Datasheet view, you will see the field names as the column headings rather than the captions. In Datasheet view, columns display the defined caption for the field only when the bound control has an attached label. In a tabular form, the labels are detached from their respective controls and displayed in a separate section of the form design.

Notice that because this subform was originally designed with the Datasheet style, the calculated control is displayed as another column of the datasheet. I have moved the column to the left for screen capture purposes. By default, the column is inserted as the last column on the left. 12. For ease of display, I have changed the subform to Datasheet View in the Property sheet.

13. Re-save and close frmWorkOrderSubform.

14. Display form frmCustomer in Form View.

Note: If you see #Name? in a calculated field, then something is wrong with your calculation. Access displays #Name? in a control when the name you supplied as the source of the control's value is not valid. You use the ControlSource property to specify the source of the control's value. For example, you might have misspelled the name, or the source might have been renamed or deleted. You may also see #Name? in a control if you place an expression in the control's ControlSource property and you insert a space before the equal sign that starts the expression.

15. In the subform, scroll to the right to display the Total Job Cost column. The calculation is performed

for every record in the subform.

16. Close frmCustomer.

A Look at Some Wonderful Access Properties As we've seen, every object in an Access database has properties attached to it. Whether or not you change those properties is entirely up to you. The properties of a given object are accessed by first selecting the object, and then clicking the Property

Sheet icon on the Design / Tools group . The available properties will change to accommodate the type of object that's currently selected.

Remember: Once the Properties window is displayed, clicking on different objects on the form will display the properties for that object. It isn't necessary to close the Properties window in order to select a different object.

We're going to look at a number of properties that you might find particularly useful:

 Visible: This option has only two values, Yes (the default) and No. If you don't want the object to be displayed on the form but you want it to continue operating in the background, this option will help. For obvious reasons, you wouldn't want to set this property to No for fields in which data must be entered on the form. For auto-enter fields, on the other hand, it's useful.

 Display When: There are three options associated with this property: o Always (the default) - to display the control in Form View, in Print Preview, and when you

print the form. o Print Only - to display the control only when you view the form in Print Preview or you

print the form. o Screen Only - to display the control only when in Form View

 Locked: This option would be particularly useful for auto-enter fields (like the Record Entry Date field that we've created in tblEmployeeHRData), to prevent users from inadvertently overwriting the date with new data. Obviously, if this were to happen it would defeat the purpose of having an auto-enter field in the first place.

 Tab Stop: This option will control whether or not the cursor stops on an object when you press the Tab key. By default, every field will be activated when you tab through the fields. In the case of picture fields, a calculated control, or an auto-enter field, this would not be necessary and would actually end up wasting data-entry time.

Hands-On Activity: Changing properties for the form

Before beginning: The Home Tech Repair database file is open, and there are no objects currently open.

1. Display the frmEmployeeHRData form in Design View. 2. Select the Record Entry Date field control object. (If this field is not on your form from a previous

exercise, add it to the form.)

3. Click on the Property Sheet icon on the Design / Tools group to display the properties for the selected object.

4. Select the All tab on the Property Sheet window. 5. Click in the Visible line and click the to display the drop-down list of options. 6. Click No. 7. Close the Properties window. 8. Switch to Form View and notice that the Record Entry Date field has been removed. However, the

current date will continue to be entered into that field for new records. 9. Switch back to Design View, and select the Gross Pay control object.

10. Display the Property Sheet window.

11. On the All tab of the dialog box, click in the Locked line.

12. Click the to display the drop-down list of options, and select Yes.

13. Click in the Tab Stop line of the properties window and display the drop-down list of options.

14. From the drop-down list, choose No. We don't want changes to be made to this object, nor do we want the tab to stop there when we're moving from field to field.

15. Close the Properties window.

16. In the Design View of the form, select the curHourlyRate field control.

17. Hold down the Shift key and click on the sngHours field control. Two objects should be selected.

Question: Do you know why we switched to Design View to select these two field control objects?

18. Display the Properties dialog box. Notice that when multiple objects are selected, the title bar of

the Properties dialog box reads Multiple selection.

19. On the Properties dialog box, click in the Display When line and click the to display the drop- down list of options.

20. From the drop-down list, choose Screen Only. This will cause these two fields to continue to be displayed when the form is displayed on screen, but they won't print.

21. Close the Multiple selection Properties dialog box.

22. Switch to Form View.

23. Press the Tab key repeatedly to cycle through the fields, and notice that the Gross Pay field is no longer activated.

24. Go to Print Preview from File / Print / Print Preview and notice the record for Charles Adams (record 1): The Hourly Rate and Hours data don't appear, but the Gross Pay value does.

25. The Record Creation Date field isn't displayed on the form and therefore won't print either.

26. Advance to the next page (while still in Print Preview) and notice the overflow of Charles Adams record. This is because the page size in Design View is greater than 7.5 inches.

27. Close the Preview window.

28. Re-save the form. [Leave it open.]

Applying Conditional Formatting to the Data in a Form If you've used the Conditional Formatting command in Excel, you know what this is all about. You simply select a field control, and specify the desired condition and the formatting that is to be applied to the field when the condition is met. Then, for every record where the condition is met, the field value will be displayed with the selected formatting. The Conditional formatting feature allows you to define dynamic

modification of the formatting of text boxes and combo boxes on forms. You can define an expression that tests the value in the text box or combo box or any other field on the form. If the expression is true, Access will modify the BOLD, Italic, Underline, Back Color, Fore Color, and Enabled properties for you basted on the custom settings you associate with the expression. The procedure for applying conditional formatting to a field is as follows:

1. Display the form in Design View. 2. Select the field control to which you want to apply conditional formatting.

3. Choose the Conditional icon from the Format / Control Formatting group. The Conditional Formatting dialog box is displayed.

If a condition has not been set, then select the New Rule option. In the Select a rule type box, you can choose to create a conditional formatting rule that checks other values in the current record or use an expression. You can also choose to compare the value of this control with values in the same control for other records.

4. In the Condition 1 section of the dialog box, leave Field Value Is selected in the first box.

5. Set the operator in the second box as appropriate. If you choose an operator other than between or not between, there will be only one value box.

6. Set the value for the condition.

7. Select the desired Format options on the Conditional Formatting dialog box (Bold, Italic, Font color, etc.). Once formatting options have been selected, you'll see a preview of how the field value will appear when the indicated condition is met.

8. Click OK. This format setting is dynamic, in that the formatting will continually change in the field based on whether or not the condition is met. Once conditional formatting has been applied to objects on the form, the object(s) on which the form was based will not have the same formatting applied to them.

Hands-On Activity: Applying conditional formatting on the form

Before beginning: The frmEmployeeHRData form is displayed in Form View.

Our objective is to apply conditional formatting to the Hours field so that any value below 40 will be displayed in boldface and red. This will tell you at a glance which employees are part- time.

1. Switch to Design View.

Note: A form must be displayed in Design View in order to apply conditional formatting to its objects.

2. Select the sngHours field control.

3. Choose the Conditional icon from the Format / Control Formatting group. The Conditional Formatting dialog box is displayed.

4. Click on New Rule. Keep the Check values in the current record or use an expression option.

5. In the Edit the rule description: section of the dialog box, leave the first box set to Field Value Is.

6. Display the contents of the second text box and choose the operator less than.

7. In the third text box, enter the value 40.

8. In the Preview area, click the Boldface icon to apply boldface to the value if the condition is met.

9. In the Preview area, select red as the font color. The Preview pane displays the formatting that will be applied when the condition is met.

10. Click OK, to apply the condition to the field control.

11. Switch to Form View and look at the value in the Hours field:

 In record 1 (Charles Adams) the Hours value is 40, and therefore it's black.

 In record 2 (Barbara Bensen) the value of 30.5 is red and boldface because it's below 40.

12. Change the Hours in record 2, (Barbara Bensen), and change the Hours from 30.5 to 40.

13. Tab to move off the field. Notice that it automatically changes to black because the condition is no longer met. Conditional formatting is dynamic, and as such, it will cause the objects to change

continually in accordance with whether or not the condition is met.

14. Move off the focus, to save the change to record 2.

15. Re-save and close the form.

Goals for this section:

 Creating a List Box on a form

 Adding a combo box to the form

 Adding a check box, control button, and toggle button to the form

 Attaching a control source to a check box, option button, or toggle button control on the form

 Creating an option group on the form

List Boxes and Combo Boxes

We've mentioned the dynamic link between objects before, so you already know that if you base a form

on a table, the form will inherit the properties of the table. We have numerous examples of this in our

database:

 In tblCustomer, there's a Value List in the strContactTitle field. When we created the

frmCustomerDataEntry form based on that table, the Value List was automatically added to the

form.

 In tblEmployeeHRData, there's a Value List in the strSpecialty field that was inherited by

frmEmployeeHRData.

 In tblWorkOrders both the Supervisor ID and the Helper fields have Value Lists, which in turn

were inherited by frmWorkOrderJobLength.

You're probably fine with this. But what happens in the following situation? You have all of your tables

created; you're in the process of building your forms, and you realize that you need other value lists on a

form to streamline the data-entry process. At this point your only option is to create the lists as either a

List Box or a Combo Box on the form, not in the table. Why? Once a form has been created based on the

table, a structural change to the table will not retrofit the existing objects that were based on that table.

List Boxes and Combo Boxes are similar in that they both display a list of available selections in a field.

The difference between them is that:

List Box:

 The contents of the list are always fully visible in the field.

 Occupies more space but shows more than one value.

 Multiple items can be selected.

 Checkboxes can be used

Combo Box:

 You will only see the list when the field is activated on the form.

 Occupies less space but shows only one value for visibility

 Multiple selections is not possible

 Checkboxes cannot be used within combo boxes

In our frmWorkOrderJobLength form we'll create one of each, so you can see the difference. First we'll

create a List Box for a Supervising Office field that contains just three values:

Building A Building B

Building C Then we'll create a Combo Box for the Principal Worker field, which will first need to be added to the tblWorkOrders table. Then we will add three names to the list

Timmons, Rose Stevens, Jack Long, Harvey

In order to be able to add the List Box for the Supervising office to the form, there needs to be a field in which to store the value in the field. In our case, we'll have to go back to the table on which this form was based to create the field. Then it will be available to us on the form.

Hands-On Activity 1: Creating a List Box on the form

Before beginning: Your Home Tech Repair database file is open, and there are no objects currently open.

1. Open the tblWorkOrders table in Design View.

2. Add two fields to the end of the list with the following field properties:

Field 1:

Name strSupervisingOffice

Data Type Short Text

Field Size 15

Caption Supervising Office

Field 2:

Name strPrincipal

Data Type Short Text

Field Size 25

Caption Principal

3. Save and close the table.

4. Make a copy of the frmWorkOrderJobLength form, and call the copy frmWorkOrders.

5. Display the frmWorkOrders form in Design View. Notice that neither of the two fields that we just

added to the table on which this form was based have been added to the form. This is just another example of how it pays to plan ahead.

6. In the Design / Controls group, make sure the Use Control Wizards button is selected .

Note: Whenever you're creating things like List Boxes, Combo Boxes, and other controls on a form, this Wizard will help you a lot.

7. In the Design / Controls group, click the List Box icon .

8. Click in an empty area of the form, to start the List Box Wizard.

Note: Don't worry about where you're placing the object to begin with. Just get it onto the form, go through the different Wizard screens, and then, once the List Box has been created, you can move the object wherever you want it on the form.

9. On the first List Box Wizard screen, select I will type in the values that I want option.

10. Click Next>

11. Number of Columns: 1

12. In the first Col1 cell, type Building A.

13. Press the (or Tab) on the keyboard to move the cursor down one cell.

14. In the second Col1 cell, type Building B.

15. Press the (or Tab) on the keyboard to move the cursor down one cell.

16. In the third Col1 cell, type Building C.

17. Click Next> to move to the next Wizard screen.

18. Select the Store that value in this field: option.

19. From the drop-down list of field names, select strSupervisingOffice.

20. Click Next> to move to the next Wizard screen.

21. Name the new list box Supervising Office List Box.

Note: Although it isn't imperative that you name your list boxes anything in particular, I heartily recommend that you give them meaningful names. Leaving the List Box with just a number could easily lead to chaos down the road, once you have created multiple boxes, because you could easily have a hard time remembering which one is which.

22. Click Finish.

23. Move the field label so that it's above the field list control.

24. Display the form in Form View and observe the List Box: All three of the entries in the list are fully displayed in the field.

25. For record 1, select Building A as the Supervising office.

26. For records 2 and 3, select Building B.

27. For record 4, select Building C.

28. Save and close the form.

29. Open tblWorkOrders and observe the Supervising office field:

Because we told the List Box Wizard that we wanted to store the contents of the List Box in the strSupervisingOffice field, the selected data for each record were transferred back to the table.

30. Close the table.

Hands-On Activity 2: Adding a Combo Box to the form

Before beginning: Your Home Tech Repair database file is open, and there are no objects currently open.

Our objective is to add a Combo Box to the form for the name of the principal worker who is responsible for each work order.

1. Open the frmWorkOrders form in Design View.

2. In the Design / Controls group, make sure the Use Control Wizards option is selected .

3. In the Design / Controls group, click the Combo Box icon .

4. Click anywhere in an unused area of the form, to start the Wizard.

Note: It doesn't matter where you click to place the Combo Box, because ultimately it will be moved in order to replace the strPrincipalWorker field.

The Combo Box Wizard starts.

5. On the first Combo Box Wizard window, select I will type in the values that I want option.

6. Click Next> to move to the next Wizard window.

7. Click in the first Col1 cell, and type: Timmons, Rose.

8. Press the (or Tab) on the keyboard, to move the cursor down one cell.

9. In the second Col1 cell, enter: Stevens, Jack.

10. Press the (or Tab), to move the cursor down one cell.

11. In the third Col1 cell, enter Long, Harvey:.

12. Click Next>.

13. On the next Wizard screen, select the Store that value in this field: option.

14. From the drop-down list, choose the strPrincipal field.

15. Click Next>.

16. Name the Combo Box Principal Worker Combo Box.

17. Click Finish.

18. Switch to Form View and display record 1.

19. Observe the difference between the List Box (whose contents are fully visible) and the Combo Box

(whose contents will only be visible when you click the ).

20. Click the to display the Combo Box for the Principal Worker field, and choose Timmons, Rose.

21. Activate record 2 and choose Stevens, Jack from the Combo Box.

22. Move the Principal Worker field so that it appears between the Supervisor and the Helper fields.

23. Change the label of the field to Principal Worker and right align the label and field control objects with the other objects on the form.

24. Re-save and close the form.

25. Open tblWorkOrders and observe the contents of the Principal Worker field: Timmons, Rose and Stevens, Jack have been entered in records 1 and 2 of the table.

26. Close the table.

Goals for this section:

 Adding a check box, control button, and toggle button to the form

 Attaching a control source to a check box, option button, or toggle button control on the form

 Creating an option group on the form

Adding Check Boxes, Option Buttons, and Toggle Buttons to the Form

So far we've created a List Box and a Combo Box on the form to facilitate the data entry process. But

Access has other options that you can use, as well. One of these involves check boxes, option buttons,

and toggle buttons.

 Check boxes: Displays a check mark in a small box if the value in the underlying field is Yes.

The box is empty if the value is No.

 Option buttons (also called a radio button): displays a black dot inside a circle if the value in the

underlying field is Yes. The circle is empty if the value is No.

 Toggle buttons: When the toggle button appears depressed (selected), the value in the

underlying field is Yes. If the button appears raised, the value is No. You can enter a caption for

the button by entering text in the control's property box. I personally find toggle buttons difficult to

"read" on the form (unless you're using them for pictures), and prefer to use check boxes and

option buttons. But we'll take a look at all three so you can decide for yourself.

You've already seen how Access automatically displays Yes/No fields as check boxes. But you can also

add a new check box, option button, or toggle button control to a form if you need it, and tie it to an

existing field.

One note before we do this: When you add a check box, option button, or toggle button to a form, there is

no control source attached to the control by default. In other words, the control will not be tied to any field.

This, obviously, is useless because when you enter a value into a control on one record, that same entry

will be made in all the records. It will be up to you, once the control has been added to the form, to select

it and add a Control Source property to the object. This will tie it to a field, so that the selection you make

in the check box, option button, or toggle button will be recorded and stored somewhere in the record

source. This is the only way you can get the control source to display a different piece of data for each

record.

Hands-On Activity 1: Adding a check box, control button, and toggle button to the

form

Before beginning: Your Home Tech Repair database file is open, and there are no objects currently open.

1. Make a copy of the frmEmployeeHRData form. Call the copy frmControlTest. The procedure is as

follows:

a. On the Navigation Panel / Forms objects, right-click on the frmEmployeeData form and

choose the Copy command from the shortcut menu.

b. Right-click again, and choose the Paste command.

c. Type frmControlTest as the name of the copy.

d. Click OK.

2. Display the frmControlTest form in Design View. We'll place the new controls on the unused portions

of the grey area of the form, to see how they're going to look and function.

3. In the Design / Controls group, click the Check Box icon .

4. Click in the unused grey area of the form to add the control to the form.

5. In the Design / Controls group, click the Option Button icon .

6. Click in the form below the Check Box control (Again, do not be concerned as to its actual placement

as it can be moved later).

7. In the Design / Controls group, click the Toggle Button icon .

8. Click in the form below the Check Box and the Option Button controls, to place the Toggle Button

control on the form.

9. Switch to Form View, and observe the three controls: The Check Box and the Option Button are

speckled filled, to reflect the fact that they haven't been used yet. As soon as you click on them, they'll

be displayed with a check mark or be white. The Toggle Button appears slightly raised, because it

isn't selected.

10. Click the Check Box, the Option Button, and the Toggle Button for record 1. All three options are

selected, but what does this mean? Because none of these controls are tied to any of the fields in the

form or table, they're just "floating" on the form without any real connection to anything.

11. Activate record 2 and observe the three controls that you just added to the form: Because you

selected the three controls on record 1, they will remain selected for every record in the record source

(that is, in the table), which of course is totally useless. However, Charles Adams and Barbara

Bensen are not insured, so if the Check Box, Option Button, or Toggle Button are meant to indicate

Yes or No for that field, this isn't the way to do it.

Obviously, this isn't the way you want (and need) these controls to work on the form. You want to be able

to click the control to indicate Yes for one record, but potentially leave it unselected to indicate No for

another record. In order to be able to accomplish this, the control must be tied to a field.

To tie a control to a control source, you do as follows

1. Display the form in Design View.

2. Select the Check Box, Option Button, or Toggle Button control object.

Note: This refers to the check box, the circle, or the button itself, not to the label to the right of the control.

3. Click the Property Sheet icon on the Design / Tools group.

4. Select the All tab on the Properties dialog box.

5. Click to place the cursor in the Control Source line.

6. Click the to display the list of available fields to which the selected control can be attached.

7. Select the field whose value you want changed when the Check Box, Option Button, or Toggle

Button is selected.

8. Close the Properties dialog box.

Hands-On Activity 2: Attaching a Control Source to a Check Box, Option Button,

or Toggle Button control on the form

Before beginning: The frmControlTest form is displayed in Form View.

1. Switch to Design View.

2. On the form, select the Check Box control. When the control is selected, selection handles appear

around the object.

3. Hold down the Shift key and click on the Option Button control, to select it as well.

4. Continue holding down the Shift key, and click the Toggle Button control to add it to the selection.

All three controls should be selected.

5. Click the Property Sheet icon on the Design / Tools group, to display the Properties dialog box.

6. Notice that the title bar of the Properties window indicates Multiple selection. Remember that you

can simultaneously apply the same property to multiple objects, rather than doing one at a time.

7. Select the All tab on the Properties dialog box.

8. Click to place the cursor in the Control Source line.

9. Click the to display the list of available fields to which the selected object(s) can be attached.

10. From the field list, choose ysnHealth.

11. Close the Properties dialog box.

12. Switch to Form View.

13. Display record 1 (for Charles Adams) and observe the original Insured? check box, as well as the

new check box, option button, and toggle button: All are selected, to indicate Yes for that field.

14. Display record 2 (for Barbara Bensen) and observe the controls: Because Barbara isn't insured, none

of the controls are selected.

15. Re-save and close the form.

Adding Option Groups to the Form

An option group offers a limited set of mutually exclusive alternatives, usually four or fewer. The option

group control consists of a frame around a set of check boxes, option buttons, or toggle buttons. If the

option group is bound to a field, the frame itself is the bound object, not the individual controls in the

group. Option groups are used only with number fields, but you can apply them to Yes/No fields by using

the numeric values for Yes (-1) and No (0).

When you create an option group, you will specify the values of the options in terms of meaningful

numbers to the underlying field. When you select an option in the group, that numeric value is stored in

the field. If Access isn't bound to a field, it uses the value of the option you choose to carry out one of a

list of actions, such as print the report you chose or open another form.

Although you can create an option group without the help of the wizard, using the Option Group Wizard

makes the job much easier. Suppose in one of your forms you want a set of Yes/No option buttons that

will work together to reflect a positive or negative value in the field. In order to do this, you should not

create two separate option buttons on the form, because there won't be any relationship between the two

controls. Instead, you need to create an Option Group that contains multiple values, and attach a control

source to the Option Group (as we did with the check box, option button, and toggle button controls

earlier).

If you want the Option Group to contain Yes and No values, so that when you click one of the buttons the

other one is automatically de-selected, the control source that is attached to the Option Group must be a

Text field.

Note: Using a Yes/No field as the Control Source will not work. The check box, option button, or toggle button control will appear on the form, but when you click on the control to select it, nothing will happen. In order for these controls to work correctly, the field must be a Text field. Another thing to bear in mind: Before you create the Option Group, you must make absolutely certain there is a field in your table for storing the values for the Option Group. If you are converting an existing field to an Option Group, that field will be the one where the Option Group values will be stored. If you're creating a new set of values in the Option group, on the other hand, you will need to add a new Text field to the table in which the Option Group will reside. If you don't have a field for storing the Option Group values, when you select a value in one record, that same value will be selected for all records. Obviously this is not the desired end-result.

Hands-On Activity: Creating an Option Group on the Form

Before beginning: Your Home Tech Repair database is open, and there are no objects currently open.

1. Open the frmControlTest form in Design View.

2. Expand the size of the Detail section to the right (to about 7"), to make room for the new controls.

3. In the Design / Controls group, ensure that the Use Control Wizards icon is selected .

Note: Although you certainly can create the Option Group on your own, the Wizard will save you a great deal of time and effort.

4. In the Design / Controls group, click the Option Group icon .

5. Click in an unused area of the Detail section of the form, to the right of the check box, option button,

and toggle button that you added previously. The Option Group Wizard is displayed.

Note: Remember that Access sequentially numbers every object that you create.

6. On the first screen of the Wizard, enter the labels that you want for the Option Group, as follows:

 In the first Label Names cell, type Yes.

 Press the down-pointing cursor arrow key or Tab to move the cursor down one cell.

 In the second Label Names cell, type No.

7. Click Next>.

Note: There's no need to move off the focus on the first Wizard screen to save the entries. Access will save the entries for you automatically.

On the second Wizard screen, you have the option of specifying a default value for the Option Group.

This refers to the entry you would like to be displayed in the field automatically for each new record. If

most of the entries in the field will be Yes, it can save you time by setting the default.

8. Choose the No, I don't want a default option.

9. Click Next>.

The next Option Group Wizard screen enables you to assign a value to each option in the group. By

default, the value 1 is assigned to Yes, and 2 is assigned to No. This means that when you attach

this group to a field, these values, not the words "Yes" or "No," will be reflected in the field of the

table.

Note: The contents in the Values list must be just that, VALUES. It isn't possible to enter text labels here.

10. Leave the values as they are, and click Next>.

On the next Wizard screen, you have the option of attaching the Option Group to a field.

Note: This is critical and must be done if you want the values already selected for records to be reflected in the new control group.

11. Select the Store the value in this field option.

12. From the drop-down list if available fields, choose ysnHealth.

Note: We're able to choose the ysnHealth field here because we are converting that field to an Option Group. Remember that if we were creating an Option Group for which there were no existing field in the table, we would have to add that new Text field to the table before defining the Option Group. (I keep mentioning this because it can trip you up if you forget about it.)

13. Click Next>.

On the next Wizard screen you can choose the type of controls that you want for the option group, as

well as the style.

14. Leave the Option buttons and Etched options selected, and click Next>.

15. Type Option Button Test as the name of the group.

16. Click Finish. The Option Group is added to the form.

17. Switch to Form View, and try clicking the Yes and No option buttons in the Option Group.

Because we attached the Option Group to a Yes/No field, the group doesn't work. Either the Insured?

field would have to be changed from a Yes/No field to a Text field in the table, or a new field would

have to be created in the table.

REMEMBER: Option groups can only be attached to Text fields. (I know I have mentioned this already, and I'm sounding like a broken record, but if you don't pay attention to this your Option Group will not work.)

18. Re-save and close the form.

19. Open the tblEmployeeHRData table in Design View.

20. Add a new field to the end of the field list, with the following properties:

Field Name strHealthTest

Data Type Short Text

Field Size 5

Caption Insured?

21. Save and close the table. The strHealthTest field is now ready to be attached to the Option Group.

22. Open the frmControlTest form in Design View.

23. Select the Option Group object.

Note: Make sure you select the entire object, not an individual item within the object. Selection handles should appear around the periphery of the entire Option Group object.

24. Click the Property Sheet icon on the Design / Tools group.

25. In the Control Source line, change the field name to strHealthTest.

When we attach the Option Group to a Text field, it will work.

26. Close the Properties dialog box.

27. Click the Add Existing Fields icon on the Design / Tools group.

28. Drag the strHealthTest field from the Field List to the form. [Place it somewhere below the Option

Button Test object.]

29. Switch to Form View.

30. For record 1, select Yes in the Option Button Test group. Because the Option Button test Option

group has Yes selected, the Insured? Text field now contains the value of 1.

31. In record 2, click No in the Option Group. When you do, the value 2 appears in the Insured? Text

field.

32. For record 3, select No in the Option Group, and for record 4 select Yes.

Note: You've probably figured this out already, but I need to mention it nonetheless. If you add the Option Group after the form has been based on the table and if you connect it to a new field that doesn't have data in it yet, you'll be doing a lot of back peddling to get the field filled in.

Plan Ahead!

33. Switch back to Design View.

34. Select the label on the Option Group.

35. Type Insured? to change the label.

36. Click anywhere off the object, to set the label.

37. Re-save and close the form.

On Your Own

1. In the frmCustomer form, create an Option Group with the following items listed:

PO Cash Check Credit Card

2. Choose No, I don’t want a default as the default selection for the Option Group.

3. Display the Option Group as shadowed option buttons.

4. Name the Option Group Preferred Payment Method.

5. Store the value in a field of the record source (tblCustomer). (Hint: There is currently no field in this

table that can store the values in this Option Group.)

6. Once the Option Group has been created, select PO for record 1 and Cash for record 2.

When finished, the form should look similar to the following:

7. Be sure to save the form when finished.

8. Close the form.

Goals for this section:

 Creating a report

 Creating a report based on one table with the Report Wizard

 Modifying the report

 Changing the sort order of the data in the report

 Changing the sort order for the rptCustomerList report

 Basing a report on multiple tables

An Overview of the Purpose and Structure of Reports

So far, you've created tables to store your data, queries to filter the data, and forms to display the data

on-screen in a user-friendly format as well as enable the user to input data. If you print the data from any

one of these objects, the end result will be more functional than attractive, and what you see on screen is

what the printout is going to look like.

This is where reports come in. A report is a database object that you use to display and summarize data.

Reports provide a way to distribute or archive snapshots of your data, either by being printed out,

converted to PDF files, or exported to other file formats. You can base the report on one or more tables or

queries, and include just the fields you want displayed. You'll also be able to include calculated fields, as

in forms, and summarize the data in the record source.

The structure of reports is similar to that of forms, in that you have sections (Detail, Header and Footer)

into which you will place controls. Much of what you've already learned about form design will also apply

to reports, which should make the Report Design window immediately familiar to you. But unlike forms,

where you can change the data in records and have those changes automatically reflected in the record

source, reports are Read-Only. If a change needs to be made to a record, you'll have to go back to the

record source (the table or query on which the report was based) to make the change there.

There are many different types of reports that you can create in Access. By far the easiest type is a

Report (previously known as AutoReport), which (like a Form) is created from the Create / Reports group

icon .

However, as in forms, Access will make all of the decisions regarding the design of the report, and all of

the fields in the record source are automatically included in the report. This Report option is really only

helpful if you're desperate to get a report out in a couple of seconds and can't take the time to go through

the steps of the Wizard.

The Report Wizard is by far the best way to create reports in Access. It will save you time, and at the very

least will get you close to what you want in the report. Through the Wizard you'll be able to create a wide

range of report types, from a single report based on a single table or query, to complex reports that group

and summarize your data. Then, once the Wizard has been used to get the basic report designed, you

can go back and add things like calculated controls for subtotals and grand totals, graphics, and

subreports.

Parts of a Report:

Report Header:

When a report is printed the Report Header is displayed at the beginning of the report on the first

page only. You would use the report header for information that might normally appear on a

cover page, such as a logo, a title, or a date. When you place a calculated control that uses the

Sum aggregate function in the report header, the sum calculated is for the entire report. The

report header is printed before the page header.

Design View:

Print View:

Page Header:

Appears at the top of every page. You use the Page Header to repeat the report title on every

page.

Design View:

Print View: This appears at the top of every page in the report:

Group Header:

Appears at the beginning of each new group of records. You use the group header to print the

group name. For example, in a report that is grouped by job specialty, Access uses the group

header to print the job specialty.

Design View:

Print View:

Detail Section:

Appears once for every row in the record source. This is where you place the controls that make

up the main body of the report.

Design View:

Print View:

Group Footer:

Appears at the end of each group of records. You use a group footer to print summary

information for a group of records. You can have multiple group footer sections on a report,

depending on how many grouping levels you have added.

Design View:

Print View:

Page Footer:

Appears at the end of every page. You use a page footer to print page numbers or per-page

information.

Design View:

Print View:

Report Footer:

Appears at the end of the report. You use the report footer to print report totals or other summary

information for the entire report.

Design View:

Print View:

Creating a Report

We'll start with the simplest way to create a report, and work our way up through the more complex

options that Access makes available to you for organizing and printing the data in your database.

To create a Report, you do as follows:

1. Open the object whose data are to be printed in the report.

Note: Tables and queries may be displayed in either Datasheet or Design View in order to create a Report on the basis of the object.

2. On the Create / Reports group, click the icon. The following is a partial screen shot of the

default Report layout.

3. If you wish to print the Report, go to File / Print.

4. If you want to save the Report, choose the Save ... command and give the report a name.

Hands-On Activity: Creating a Report

Before beginning: Your Home Tech Repair database file is open, and there are no objects currently open.

1. Open the tblCustomers table in Datasheet View. This is the object that will provide the data for the

Report.

2. On the Create / Reports group, click on the Report icon .

3. Observe the Report:

 Access takes all of the fields from the active object (tblCustomers) and displays them

horizontally in a single row layout

 Report header has been added to the report

o Report header contains the name of the object used to generate the report

o A Report icon

o Date and time that the report was generated

 Report footer has been added to the report

o Count of the number of records

 Page header has been added to the report

o All field labels have added so that they will appear at the top of every page of the report

 Page footer has been added to the report

o Number of pages in the report

 All field controls have been placed in the Details Section

4. If you wish to print the Report, go to File / Print.

5. If you want to save the Report, choose the Save ...command and give the report a name.

6. Close the Report without saving it.

7. Close the tblCustomers table.

Using the Report Wizard to Create a Report Based on One Table

I can't emphasize enough how much time the Report Wizard will save you. It's going to enable you to

select the fields you want included in the report, decide whether or not the report data will be sorted, and

in addition it will automatically add the page number and the current date to all reports. It may not give

you everything you want, but it will get you very close.

The Report Wizard will, by default, follow any Sort properties that have already been applied to the record

source. Therefore if your table data have been sorted (either by the primary key field or by other fields)

and you want to use the same Sort order for the report, you don't need to worry about applying a Sort to

the report. However, if you want a Sort order other than the one that was used in the record source, you

have the ability to apply a Sort through the Wizard or after the report has been generated.

Hands-On Activity: Creating a report based on one table with the Report Wizard

Before beginning: Your Home Tech Repair database file is open, and there are no objects currently open.

Our objective is to create a Tabular report based on the tblCustomer table, including all of the fields except strContactTitle, memNotes, and strPaymentMethod. The Sort order will follow that of the table, which is sorted by the Customer ID field (the primary key). No grouping will be applied to the data, and the orientation will be Landscape.

1. From the Create / Reports group, click on the Report Wizard icon to start the Report

Wizard.

On the first screen of the Report Wizard, you have the option of selecting the table or query that will

provide the fields and records for the report, as well as the fields that you want to include.

2. Click the on the Tables/Queries box, to display the list of available objects.

Note: In order to be able to create a report based on an object, that object must be physically present in the database file.

3. Choose Table: tblCustomers as the data source.

The Available Fields list will reflect the fields in the selected object.

4. Click the >> button to add all of the fields to the Selected Fields pane.

5. In the Selected Fields window, select the strContactTitle field.

6. Click the < button to move the field back to the Available Fields list. We don't want this field to be

included in the report.

7. In the Selected Fields list, select the memNotes field.

8. Click the < button to move the field back to the Available Fields list.

9. Do the same with the strPaymentMethod field.

Note: You may have named this field differently when you created it previously. This is the field to which you attached the second Option Group that you created, On Your Own.

10. Click Next>. The next Wizard screen enables you to group the data in the report. We're not going

to apply any grouping to this report.

11. Click Next> to move to the next Wizard screen. This screen enables you to apply a Sort order to

the data. We're following the natural Sort order of the table, so there's no need to make any

changes here.

12. Click Next>. The next Wizard screen enables you to choose a layout and orientation for the

report.

13. Leave Tabular selected as the layout.

14. Select Landscape as the orientation.

15. If you want to change the style of your report, you can select a theme from File / Themes group.

16. Click Next>.

17. Type rptCustomerList as the name of the report, and click Finish. The report is generated and

previewed.

18. Observe what the Report Wizard has done:

 It has used the object name (rptCustomerList) as the Report Header, in a 20-point font size.

 It has placed the Captions in the Page Header section so that they'll appear at the top of

every page of the report.

 The records are sorted by the Customer ID field, which is the primary key of the table.

 It has placed the date the report was generated and the number of pages in the report in the

page footer

Modifying the Report

Now that you have the report created, you can go about making some quick and easy changes to it in a

matter of minutes. Fields can be added, deleted, moved, and resized, the label in the Report Header

section can be changed, field labels may be modified, etc.

We'll make the following changes to our report:

 We'll change the Report Header label from rptCustomerList to Customer List.

 We'll delete the Contact MI field and shift the other fields to the right or left to add more space

between fields.

 We'll abbreviate some of the field labels so that they're completely visible at the top of each

column.

Hands-On Activity: Modifying the report

Before beginning: The rptCustomerList report is displayed in Print Preview.

1. Close Print Preview and the report is displayed in Design View.

2. Observe the contents of the different report sections:

 The report Wizard created Report Header, Report Footer, Page Header, Page Footer, and

Detail sections for the report. Because nothing has been added to the Report Footer section,

that section is closed.

 The =Now() function has been added to the Page Footer section, so that the current date is

reflected at the bottom of every page of the report.

 The ="Page"&[Page]& "of"&[Pages] variable in the Page Footer section will provide the

current page number and the total number of pages in the report.

3. Click on the rptCustomerList label in the Report Header section, to select it.

Note: Notice the green triangle in the upper left corner of the ruler. Clicking on the triangle displays a caution icon. Placing the mouse over the caution icon displays a warning message that ‘This report's width is greater than the page width, possibly causing extra blank pages to be printed’. Keep this in mind when you are designing your reports.

4. Click inside the label object, to place the cursor there, and edit the label to read Customer List.

5. In the Page Header section, click on the first field label, to select it.

6. Place the cursor in the label object, and double-click on the word Customer to select it.

Note: Your field name may be Cust ID instead of Customer ID. If so, double-click on the word Cust.

7. Press the Delete key on the keyboard, to delete the word Customer, and delete the additional space.

8. Click anywhere off the label. The first field label should now read ID.

9. Change the labels of the following fields, as well. Your current label names may differ than the ones

below:

Current label New label

Company Name Company

Contact FN Contact First

Contact LN Contact Last

Billing Address Address

Zip Code Zip

10. Select the MI field label.

11. Hold down the Shift key, and select the strMI field control.

12. Press Delete, to remove the field and its label from the report.

13. Select the Contact First field label and the strFirstName field control. [Use the Shift-Click method.]

14. Press the key on the keyboard 5 times, to micro-space the objects to the right.

Note: Be sure to remove the Control layout indicator (double crossed arrows on left side of the Page Header) by switching to the Arrange / Control Layout and click on the Remove icon.

15. Select the Contact Last field label and the strLastName field control, and press the key 4 times

to move these two objects slightly to the left.

16. Increase the size of the strCompanyName field control. (Click on the object to select it, place the

mouse pointer on the right center selection handle, and drag to the right.)

I have made some additional moving of fields in the following screen shot so that I can replicate it

here.

17. Re-save the report.

18. Click Print Preview from the File / Views, to see the report as it will look when printed. You will

probably receive the following message:

Eventually, you will need to make your page narrower.

[Leave the report open in Preview.]

Changing the Sort Order of the Data in the Report

As we mentioned earlier, a report will always follow the Sort order of its record source. And as you've just

seen, you have the option of choosing a different Sort order when building your report with the Report

Wizard.

But suppose you get to the point of previewing your report, and you realize you don't have the Sort order

you want. What do you do?

You do not scrap the report and start over!

To change the Sort order for an existing report, you do as follows:

1. Display the report in Design View.

2. Select the icon from the Design / Grouping & Totals group.

The Group, Sort, and Total window is displayed below the form.

3. Selecting the Add a sort option, displays the drop-down list of field names.

4. Select the name of the field to be used as the first Sort key. Ascending is automatically entered in

the Sort Order cell for that field.

5. If you want to sort in Descending order, click on the with A on top option and select with Z on top.

6. Follow the same methods indicated above, to specify all of the other fields that you want to use for

the Sort.

7. Once all of the Sort order fields have been selected, close the Sorting and Grouping window.

Hands-On Activity: Changing the Sort Order for the rptCustomerList report

Before beginning: The rptCustomerList report is displayed in Print Preview.

1. Right click on the report and select Design View from the short cut menu.

2. Choose the Group & Sort icon from the Design / Grouping & Totals group.

The Sorting and Grouping window is displayed below the form.

3. Click the Add a sort option, to display the drop-down list of field names.

4. Select strState from the list. Ascending is the default Sort Order.

5. Click on Add a sort and select the strCity field.

6. Click on Add a sort and select the strCompanyName field.

7. Leave Ascending as the Sort Order for all three fields.

8. Close the Sorting and Grouping dialog box by clicking on the x in the upper right corner.

9. Preview the report (Report View) and observe the change: Instead of being sorted by ID#, as it

previously was, the report is now sorted primarily by State. If there are duplicate states (which there

are), it sorts by City. In the case of duplicate cities, the company names are alphabetized.

10. Re-save the report, and close it.

Creating a Report Based on Multiple Tables

Using the Report Wizard, you can select multiple objects on which to base your report. However, make

absolutely certain that your relationships are correct before you do this. If you base a report on multiple

objects that have no relationship to one another, you're going to get duplicate records in your report.

The procedure for creating a report based on multiple tables is as follows

1. Start the Report Wizard.

2. From the drop-down list of available object names, choose the name of the first table on which you

want to base the report.

3. Click OK.

4. On the Report Wizard's next screen, select the fields that you want to include in the report. (Select an

individual field in the left pane and click the > button to move it to the right pane, or click the >> button

to move all of the fields at once.)

Note: Add the fields to the Selected Fields list in the order in which you want them to be displayed in the report. This will save time later because you won't need to move fields on the Report Design window.

5. From the Tables / Queries drop-down list, select the name of the second table on which you want to

base the report.

Note: If you have inadvertently clicked Next> to move to the next screen without specifying the second table, click the <Back button.

6. Select the fields from the second table that you want included in the report, as in step 4 above.

7. If you want fields from other tables to be included in the report, select that table and then choose the

fields. As long as you have compatible tables, you can continue selecting fields from them to include

in the report.

8. Once all of the desired fields have been selected, click Next>.

9. On the next Wizard screen, you can specify the field(s) to be used for grouping the data. To do this,

you would select the field and then click the > button to move it to the Preview window in the right. If

you don't want to group the data, just click Next> to move to the next screen.

10. On the next Wizard screen, you have the option of applying a different Sort Order to the records. If

you want to do this, select the fields and add them to the Sort order pane. If you want to use the

currently applied Sort Order, just click Next> to move to the next screen.

11. On the next Wizard screen, select the desired layout and orientation for the report, and click Next> to

move to the next screen.

12. Type a name for the report, and click Finish.

Hands-On Activity: Basing a report on multiple tables

Before beginning: The Home Tech Repair database file is open, and there are no objects currently open.

Our objective is to create a report based on the tblEmployeeHRData and tblEmployeePersonal tables. Because these tables have a One-to-One relationship based on the Emp ID field, the report will list each employee only once.

1. Select the Report Wizard icon on the Create / Reports group.

2. From the drop-down list of available object names, choose tblEmployeeHRData.

3. Double-click the following fields in the left pane, to add them to the Selected Fields pane:

strEmployeeID

strFirstName

strLastName

4. From the Tables / Queries drop-down list, choose tblEmployeePersonal.

5. Double-click the strAddress, strCity, strState, and sngZIP fields to add them to the Selected Fields

list.

6. Click Next>.

7. We don't want to group these records, so click Next> to bypass the Wizard's grouping screen.

The Sort screen is displayed.

We'll sort the records by Last Name, then by First Name.

8. Click the in the first Sort Order box, and choose the strLastName field. [Leave Ascending

selected as the Sort Order.]

9. Click the in the second Sort Order box, and choose the strFirstName field. [Leave Ascending

selected as the Sort Order.]

10. Click Next>.

11. Leave Tabular selected as the Layout and Portrait as the Orientation.

12. Click Next>.

13. Click Type rptEmployeeAddresses as the report object name.

14. Click Finish.

On Your Own

Make the following changes to the rptEmployeeAddresses report

1. Change the Report Header label from rptEmployeeAddresses to Employee Addresses.

2. Edit the field labels as follows:

Current Name New Name

Emp Last Name Last Name

Emp First Name First Name

Emp ID ID

3. Move the ID field control and its corresponding label to the left of the Last Name field.

Here's a strategy:

 Select both the strEmployeeID field control and its label, using the Shift-Click method.

 With the two objects selected, choose the Cut command. (the Home / Clipboard icon, or use

the Ctrl X keyboard shortcut). This places the selected objects on the Clipboard, where you'll

store them temporarily until you have freed up some space for them on the report.

Note: Be very careful not to Cut or Copy anything else while you're in the process of doing this, or you may lose what's on the Clipboard.

 Select the strLastName and strFirstName field controls and their labels.

 Use the key on the keyboard to shift the selected objects to the right, to fill the space

previously occupied by the ID field.

 Click in the Page Header section, and choose the Home / Clipboard icon (or use the Ctrl

V keyboard shortcut).

 When previewed, the finished report should look similar to the following:

4. Once you have finished making the changes to the report and previewing it, re-save the report

and close it.

Assignment for Week 9

Chapters 20 & 21 of the textbook.

Do Final Database Project Assignment #6. Due by midnight Eastern Standard Time on Sunday

March 27, 2016. In this assignment, you're going to create two forms that will incorporate what

you have learned in this week's and last week's lessons. A word of caution: This assignment will

probably take you quite a long time, so be prepared.

Here's the assignment:

Form #1

Our objective is to create a form with a subform, to display products and the orders that have

been placed for the different products. The finished form will look as follows (the style is up to

you):

1. Create the main form, with the following specifications:

 Name it frmProductDataEntry.

 Apply any background colors that you like.

 Calculate the Discounted Price, and format it to display the value as Currency with 2

decimal places.

Note: The Discounted Price would be calculated by multiplying the List Price by the Discount, and then subtracting that value from the List Price.

 Move and resize the objects on the form and align the left edges of objects so that they

look like the following:

 Add a 16 point bold label as a heading, as in the above graphic. The contents of the

heading should be displayed at the top of each page when the form is printed, as well as

when the form is displayed on screen. Ensure that the heading is centered horizontally

on the form.

 Align the top edges and the left edges of the contiguous objects, so that the

misalignment of objects on the form isn't distracting.

 Add a List Box for the Finish field. Include the following five entries in the list:

G1S

G2S

RGH

S4S

T&G

 Replace the original Finish field with the List Box, and change the label to read Finish.

 Control the Discount field so that it's displayed on the form, but wouldn't print if the form

were to be printed.

 Control the List Price field so that its contents cannot be edited on the form.

 Control the Discounted Price control so it isn't activated when you use the Tab key to

move from field to field on the form.

 Change the Tab order so that when a record is created or activated, the fields are

activated as in the following order:

Product Code