Jan's Working with Databases

Exercise Access 4-2: Ornaments

What you will do:

Rename an existing database Create and revise a form Insert images into records Print form records Create a report that calculates totals Create an AutoExec macro Document a macro

Start with:   Access open with no databases open, resource files

Many of us have collections, like coins, stamps, rocks, action figures, Barbie dolls, salt and pepper shakers, ball caps, stuffed bears, books, music CDs, etc. It can be hard to remember what you've got!

You are going to produce a form and some reports for a database for a collection of Christmas ornaments. This is a variation of the database that I created to keep track of my own family's ornaments. I cannot always remember whose angel is whose! In the heat of setting up the Christmas tree, such questions need a quick answer!

This database could be adapted for another holiday collection or another type of collection altogether. Each type of collectible has its own special characteristics that you might want to put in the records. A database of coins could include the type of coin, country of origin, name of the mint, year of minting, where you got it, what you paid for it, and its condition. A database of books could contain the title and author, publisher, year printed, original price, a summary of the plot, and whether you liked it or not.

My apologies for the fuzziness of some of the photos. They are from my early days with a digital camera. For more recent ornaments I scanned the box or the card! Much better.

Startup file

1. From the resource files, copy Ornaments-startup.accdb and paste it to your Class disk in the databases project4 folder. (This file is about 2 MB so it may take a while to write this to your USB drive. Be patient.)
   
2. Rename the file ex4-2-Ornaments-Lastname-Firstname.accdb .
   

Create Form

1. Open the Ornaments database from your Class disk.
   

2. Create a new form based on the Ornaments table, using the Form button.
   Records are sorted by ID number by default.

   The autoform uses a Stacked layout table.
   

3. View each of the records.
   Which controls need resizing? Does this layout read easily?
   

   The text fields are allowed only 50 characters by the table's design. Any more space is wasted.

   The photo is small. Two records do not have a photo.

Revise Form

You will make changes to allow some controls to grow when needed. You will rearrange the controls to fit on the screen better.

1. In Form Design View, drag the right edge of the one of the controls to the left to the 3.5" grid line. The whole table resizes.
2. Select the controls at the bottom of the form, from Description through Picture.
3. Cut and paste the selection to the Detail section and then move it to the right side of the layout table.
   The pasted controls and labels are in a layout table of their own.
4. Move the table as needed to fit both tables within 8".
   If necessary, resize the form's width to 8".
5. Remove the Stacked Layout from both layout tables.
6. Resize the photo control to 1.5" tall.
   The Picture control is set to Zoom mode by default so the photos will resize to fit the space without losing their proportions.
7. If necessary, change the property Picture Alignment to Center.
8. Change the CanGrow property to Yes for the Name and Description controls.
9. Check the Form View for record #2.
   The Description field is obviously cut off.
   The controls did not enlarge to show the missing text.
   
10. Switch to Print Preview.
    Aha! The controls grow only in Print Preview, not in Form View. That property applies only to printing. Too bad!
    The data is all there in the form. But the scrollbars have to be turned on to see it.
11. If necessary, for the Name and Description controls set the Scrollbars property to Vertical.
    
12. In Form View, to find out how much space is needed for the 50 characters allowed, in record 1 in the Name field type upper case W's at the end of the current name until Access starts dinging at you and quits accepting new characters. You have reached 50 characters. W is the widest character in the normal English alphabet. Of course the original text for the field was not all W's!
13. Switch to Layout View and resize the Name and Description controls to show two lines of text. Move other controls down to make room. None of the current records need more than this to show completely in the form.
14. Switch back to Form View and remove the extra characters that you typed.
15. Drag the bottom edge of the Detail section up to the bottom of the Picture control.
16. Add your name to the left of the Page Footer.
    
17. Add the page number and number of pages to the center of the Page Footer.
    (There is no button to do this for a form. You must add a control and type in the expression yourself.)
    
18. Save the form as Ornaments Form.
    

Insert Images

1. Open the form to record #12, Mrs. Snowman.
   
2. Add the image mrssnowman.jpg from your Resources files to the photo control with the Attachment menu.
   You cannot drag and drop or copy and paste the image.
   
3. Similarly, add the image mrsnowman.jpg to the next record, #13 Mr. Snowman.

Print Preview

1. 

   Open the Print Preview of the form and view all of the pages.
   Make sure that the names and descriptions are completely showing.
   (Your computer may pause after the first few pages to format the next batch of pages. Do not be impatient!)

   Hmm. Are some records are split across two pages? You really There is a way to guarantee that this cannot happen.
   

2. In Form Design View, change the Keep Together property of the Detail section to "Yes".
   
3. In Print Preview, inspect the records again.
   There should be only two records per page.
   Make any corrections needed.
4. Print page 6, which has one of the two new images.
   
5. Save and close the form.
   

Create Detailed Report

AutoReport and Report Wizard both do a bad job with the data in the Ornaments table. But you can start with the Report Wizard's output and rearrange it to make a useful report. For this report you want to find out which ornaments were bought in which year and how much they cost, plus a total for all 37 ornaments in the database. (My actual database has many more than this!)

1. Use the Report Wizard to create a new report based on the table Christmas Ornaments.
   1. Include all fields in the table including the field Picture but not including the extra ones about the picture.
   2. Group on yearbought.
   3. Sort Ascending on name, series, series#.
   4. In Summary Options, choose to Sum the Retail Cost and Actual Cost, showing both Detail and Summary.
   5. Use the Stepped layout in Portrait orientation.
   6. Access 2007: Use style Access 2007.
   7. Name the report Ornaments-Total Cost 
2. Inspect the report.
   This is bad! There is a lot of wasted space, the picture in each record is small but the control is far too tall. In Access 2007 a record stretches onto a second page. In Access 2010, 2013, and 2016, the column headings and controls overlap and are not readable!
   

Revise Report

You can move the controls around and create a 2 column report that is actually readable! Just rearrange the controls so that they will fit in half the available page width.

1. If necessary, remove the Stacked layout.
2. Page Header:
   • Delete all of the controls in the Page Header section.
   • Hide the Page Header section by dragging its bottom edge up to meet the top.
     
3. Group Header:
   • Add a label for the control yearbought and associate it with the control. Position both.
   • Copy the formatting from the title and apply it to the label and the control in the group header.
   • Reduce the font size to 16 and resize the label to Best Fit and the control to at least 1".
   • In the Group, Sort and Total pane, choose 'keep header and first record together on one page'.
   • Add a 4" line at the top of the group header and a duplicate line at the bottom of the group header.
   • Change the section's Back Color to Access Theme 2.
   • Change the section's Alternate Back Color to No Color.
   • If necessary, change the Back Color for the yearbought control to Access Theme 2 or the Back Style to Transparent.
     
4. Detail:
   • Rearrange and resize the controls in the Detail section as in the illustration below.
   • Change the Alternate Back Color for the section to No Color.
   • Set CanGrow to Yes for Name, Series, and Description controls.
   • To the left of the Retail Cost control, add a label with text Retail:
     
   • Similarly, beside the Actual Cost control, add a label with text Actual:
     
   • Add a label for the check box with text Mini:
   • Add a 4" line at the top of the Detail section.
     
5. Photo Control:
   • Resize the photo control to about 1.4" x 1.2".
   • Move it to the right of the other controls but keep the right edge to the left of the 4" grid line.
   • If necessary, make its border transparent. 
     
6. Group Footer:
   • Move the Sum controls to be under the matching Detail controls. Add labels.
   • Resize the controls to match the width of the Retail price and Actual Cost controls.
   • Edit the Summary control to produce text like "Total for 1998". Make its border Transparent.
   • Change the Alternate Back Color for the Group Footer to No Color.
     

   

7. Page Footer:
   • Add a label with your name in the center.
     
8. Page Setup:
   • 2 columns
   • Column Spacing = 0"
   • 4" wide
   • Down, then Across
     
9. Report Footer:
   • Enlarge the two SUM controls to 1" wide.
   • Reposition so that they do not overlap.
     The labels were originally placed directly on top of each other.
   • Edit one label to read "Grand Total Retail".
   • Edit the label for "Grand Total Actual" and position it beside that total.
   • Apply a larger font size and a color to the labels and controls for the report totals.
     
10. Switch to Print Preview. There are 5 to 7 pages.
    This is much better than the wizard created. But, there are some controls without labels and it is not obvious what they represent. It is easy to add some labels. Concatenating some values with some text would help, too.

Calculated Controls

By concatenating values, you can avoid blank areas or having to guess at how much space a control really needs. The Series name and number can be combined into a single control, for example, like "Woodland Babies #3". To get the hash mark, #, and a space is easy enough, but you need to use an IIf statement to keep the # from showing when the ornament does not have a series #, like the Gentle Giraffes for the Noah's Ark series.

1. Change the Name property of the series control. (Your choice)
   Otherwise the expression you create in the next step will cause an error because of the circular reference.
   
2. Change the source for the series control to concatenate the series field name with the series number only when the series# field is null.
    (No, I am not going to tell you what to write this time!)
   
3. Delete the control series#.
4. Resize the series control to the same width as the name control.
   

5. Switch to Print Preview.
   Check records like Gentle Giraffes and Star Wars Ewoks to be sure they do not show the hash mark, #, by itself at the end of the series name. Check the first two records to be sure that they DO show a # and a number.
   

6. Print the first and last pages of the report.

   Your report may page differently since it depends on the exact height for the sections.
   

7. Save and close the report.
   [Ornaments-Total Cost]
   

AutoExec Macro

A macro that has the name AutoExec is automatically run whenever the database is opened. Such a macro is useful for opening the object that you usually want to use right away. For this database, we will assume that the most common task is entering a new ornament. You will create a macro that opens the form as a data entry form.

The downside of a data entry form is that you need a separate form for viewing the existing records. If you often need to look at those records before you can create a new record, you do not want to use a data entry form.

1. Click the Macro button on the Create ribbon tab to start a new macro.
   
2. For the first action, select OpenForm.
   
3. In the arguments for the action, select:
   • Form Name = Ornaments Form
   • View = Form
   • Data Mode = Add
4. Save the macro with the name AutoExec
   
5. Close the macro window.
   
6. Close the database and reopen it.
   The form opens automatically and does not show the existing records. It is ready for a new record.
   
7. Use the Documenter to create a report about your new macro.
   (Database Documenter on the Database Tools ribbon tab.)
   
8. Print the one-page Documenter report.
9. Close the database.