Jan's Working with Databases

Step-by-Step: Use Table Template (2007)

---

What you will learn:

to create a table using a template to edit a field's name and a property to create a form and enter records to attach a photo to use the Lookup Wizard to see Lookup properties of a field to use a Lookup field to delete a field to repair errors in form and report caused by deleting field to view subdatasheet in related table

---

Start with: mytrips-Lastname-Firstname.accdb from a previous lesson

Access 2007: Create a Table with a Template

The Trips table has a memo field TravelAgent for information about the travel agent. You must enter this data for every trip, even if you used that travel agent previously. It would be better to have that information in its own table instead. If you use the same travel agent again, all the information will be there, waiting for you. If you need to update an email address or phone number, you can change it in one place instead of having to find all of the trip records that show that agent and edit each one.

Access has a template that will create a table with fields for standard contact information, like a mailing address, phone numbers, email address, as well as the parts of the agent's name.

You will be making changes that will remove data. Be sure to make a backup first!

1. Back up this database now so you will have a good copy to use if you need to start over. This works well while the database is small.
   Access 2007: Office button > Manage > Back Up Database
   
2. Switch to the Create ribbon tab.  

3. Click the button Table Templates to open the list of templates.

   Both Access 2007 and 2010 have just a few templates: Contacts, Tasks, Issues, Events, and Assets. In Access 2007 these templates create a table with some standard fields. In Access 2010, some of these also create related forms and reports.

4. Click on Contacts.
   

   A new table, Table1, opens in Datasheet View with fields already created.

   Scroll horizontally to see all 18 fields. These are the basic fields that you need for contact information. BUT... the properties of these fields may not be quite right for you.

   New Table Name: When you save your changes to a table created this way, you will be offered the chance to rename it. Table1 is NOT a good name!

5. Close the table.
   You are prompted to change the name.

6. Name the table Contacts.
   This is not my favorite name for this table, but it will match what Access 2010 and 2013 do when you choose the Application Part named Contacts. Access 2010 and 2013 create a whole set of queries, forms, and reports. Happily, we will not be duplicating all of those in this lesson. We are just trying to learn what the 'automatic' features of Access actually do.

---

Access 2007: Edit Property

You can change the properties of the table that the template created. Some changes can be done in Datasheet View. Other changes need to be done in Design View.

Let's start by changing the name of a field. The field ID has a name that might cause trouble later if you create another table with the template. It is a good idea for the ID fields in each table to have different names, like ClientID, OrderID, ScoreID, TaskID.

1. Double click on the field ID.
   The whole name is highlighted.

2. Type ContactID as the new name and press the ENTER key.
   The field name is accepted.
   Notice, the other column headings include a space, like First Name.

   Other changes you can make in Datasheet View:

   • Add a field using the special column at the far right.
   • Delete a field.
   • Rearrange the order of fields in the datasheet.

3. Switch to the Design View of the Contacts table.
   The new name ContactID shows here.

   The template created field names with spaces.(Access 2010 handles this a bit differently.) That looks great in the Datasheet View and in forms and reports that use the field name as a label, but it makes things more complicated for writing SQL queries and doing programming for the database. Most database designers use field names which do not have spaces.

   Happily, you can have it both ways. You can use field names without spaces and still see column headings with spaces. The Caption property is part of the solution. You will change one field's properties to demonstrate how it works.
   

4. Click on the field First Name and look at the value for the Caption property (at the bottom of the window).
   It's blank. When the Caption property is blank, the actual field name shows as the column heading in a datasheet or as the field name label in a form or report.

5. Change the field name at the top to FirstName, with no space.
   

6. Switch to Datasheet View.
   (You are prompted to save the table.)
   The column heading now shows FirstName with no space.
   
7. Switch back to Design View.

8. Change the Caption value to First Name, with a space.

9. Switch to Datasheet View.
   The column heading now shows First Name, with a space.
   It's nice when you can arrange things to look good and work well at the same time!
10. Close the Contacts table and save the changes.

---

Access 2007: Use Form to Add Records

Before we can use the Contacts table, it needs some records. So your next job is to create an autoform for this table to use to enter the records. Why not just type directly in the datasheet? There are 18 fields but you don't have data for most of those fields. It would take a lot of side-ways scrolling to create records in the datasheet.

1. Select the table Contacts in the Navigation Pane, but don't open it.
2. On the Create ribbon tab, click on the Form button .
   Access creates an autoform named Contacts for the Contacts table.

3. Click the Save button on the Quick Access Toolbar and name the new form ContactDetails.

   The title in the form header does not change to match the tab title. That's fine.
   

4. Enter the following records. Many fields will be blank for now.
   (Remember that these are not real people, the companies are not a real companies, and the email addresses are not real either!)

   You can use the TAB key to move from field to field or just click on the field you want to edit.
   

   FirstName	Jorge
   LastName	Martinez
   Country/Region	USA
   CompanyName	World Travel Inc.
   Title	Agent
   WorkPhone	615-555-1234
   EmailName	jmartinez@worldtravelinc.net

---

Access 2007: Attach Photo

To attach a photo: The Attachments field can hold one or more attached files. A photo of this person would be the obvious first choice. But you could also attach files, like the job offer letter, contract, pay stub, or other documents.

1. Click on the field Attachments.
   A partly transparent mini toolbar appears at the top left of the control.
   The arrow buttons move you through the various attached files, when there are any. The paper clip button opens a dialog for managing attachments.
   
2. Click on the paper clip icon.
   (You can also just double click the blank field.)
   The Attachments dialog opens. This dialog shows the names of all attachments in this field. When a file is listed, you can double-click it to open it - if you have a program that can view that file type.
3. Click on the Add... button.
   The Choose File dialog opens. It looks amazingly like a Windows Explorer window.

4. Navigate to the resource files that are part of your local copy of these lessons or where you downloaded those resources, and find the file martinez.jpg.

   Be careful to select the correct version of this file. The resource files contain a much larger BMP version for lessons that use older versions of Access. You want the JPG version.

5. Click on the file and then click on the Open button.
   The file name shows in the dialog now.
   

6. Click on OK.
   The dialog closes and the photo appears in the record.
   This form does not show off the picture very well!

   How can you fix that?

7. Switch to Design View.

8. Drag the bottom edge of the Attachments field down to make it as tall as you can without making the form too tall to fit in your screen.
9. Switch back to Form View.
   The photo is automatically sized to fit the new, larger size.
    

---

Access 2007: Add More Records

Now you can add more records to practice your navigating and typing skills.

1. Click the navigation arrow or at the bottom of the form to advance to a blank record.
2. Enter the records below. The photos are in your resource files.

   Copy previous value: You can copy the value "World Travel Inc." into later records by using the key comb CTRL +  ' (the apostrophe) when your cursor is in the field CompanyName. This key combo copies the value of the field in the previous record into the current record. Of course, once you enter a record that has a different name in the Company field, this tip will not be helpful!

   FirstName	Hector
   LastName	Chavez
   Country/Region	Argentina
   CompanyName	World Travel Inc.
   Title	Agent
   WorkPhone	555-9876
   EmailName	hchavez@worldtravelinc.net
   Attachments	chavez.jpg

   
   

   FirstName	Sheila
   LastName	Landers
   Country/Region	Australia
   CompanyName	World Travel Inc.
   Title	Branch Manager
   WorkPhone	5554-8890
   EmailName	slanders@worldtravelinc.net
   Attachments	landers.jpg

   
   

   FirstName	William
   LastName	Gardner
   Country/Region	Argentina
   CompanyName	World Travel Inc.
   Title	Agent
   EmailName	bgardner@worldtravelinc.net
   WorkPhone	555-9876
   Notes	Nickname = Bill
   Attachments	gardner.jpg

3. Close the form.

4. Evaluate: Which is easier for this table - using a form or entering data directly in the datasheet?

   Did you make many mistakes in typing in the data?
   Was the data given to you in the best order? Not really! That was done to show you how useful it is for the order of fields on the form to match the order that you receive it.

   Sometimes you need to change the order of the controls on a form to match the order that the data comes to you. Sometimes you can adjust the data first so that the order you see it matches the order on the form.

---

Access 2007: Use Lookup Wizard

The reason we created a separate table for Contacts was to use those values in a Lookup field in the Trips table. A Lookup field lets you pick from a list instead of having to type in a value.

The advantage is that it guarantees that you use the same value for the same agent. For example, William Gardner has the nickname Bill. Without a Lookup list, you might put in Bill for his first name one time and William another time. Sorting and filtering would not treat those names as the same.

The awkward part is that you must first add a new person to the Contacts list before you can pick them in the Trips table.

Now that the Contacts table has some records, let's create a Lookup field in the Trips table. Then you can see how such a field might be helpful and how it might present problems.

1. Open the original Trips table in Design View.

2. Click in FieldName box in the first blank row, below the Diary field.
3. Type Travel Agent, with a space, and set its Data Type to Lookup Wizard...
   The Lookup Wizard dialog opens.
   

   You already had a Memo type field with a very similar name, TravelAgent, with no space in the name. That space makes it an entirely different field. The Memo field holds the travel agent's name and contact info. You will remove one of these fields shortly to avoid confusion.
   

4. In the first page of the dialog, keep the default selection, "I want the lookup column to look up the values in a table or query."

   If you had a small list of possible values that only make sense for this one table, you might want to choose "I will type in the values that I want".

5. Click the Next > button.
   
6. In the second page of the wizard, you have only one choice this time: Table: Contacts. Keep that choice.
7. Click the Next > button.
   
8. In the third page of the wizard, click on ContactID and then click on the > button in between the two columns.
   That field name moves to the right side column.
9. Repeat for the field Last Name.
   The two fields in the Selected Fields: list are all that you will see in your Lookup list.
10. Click the Next > button.
    
11. In page 4 of the Lookup Wizard, select Last Name and Ascending.
12. Click the Next > button.
    
    
13. In page 5 of the Lookup Wizard, leave the check mark marked for "Hide key column".
    The column is wide enough already for the current records and looks like it should work fairly well for future names, too.
14. Click the Next > button.
    
15. In page 6 of the Lookup Wizard, leave the name for the Lookup column as Travel Agent.
    Do not check the box to Allow Multiple Values. That's a good choice when several people might be involved in a task or project or when a product might come from several different suppliers.

16. Click the Finish button.
    A message box appears to remind you that the table must be saved before a relationship can be created. Did you know that you were trying to create a relationship??

    
    

17. Click on Yes.
    The wizard sets the data type to Number and fills in the properties on the Lookup tab for the new field.

    If you use the wizard, you don't have to know what all these properties are that are on the Lookup tab! That's sweet.
    

---

Access 2007: Use a Lookup Field

1. Switch back to Datasheet View.
   Now you will use the Lookup list feature to see what it is all about.
   
2. Click in the second record in the Travel Agent column, the one with a space in the name.
   This column is at the far right in Datasheet view.

3. Click the down arrow to open the Lookup list for this cell.
   All of the agents you entered in the Contacts table are showing.

   No more guessing about spelling! Once the data is in the Contacts table, you can just pick from a list for a record in the Trips table.

   Point of Confusion: Field Data Type does not match Lookup list entry
   Remember from the Design View, the new Travel Agent field has Number data type. But the drop list shows a name, not a number. How can that work?

   Explanation: The wizard created a simple Lookup list that shows only one value but stores the related primary key value, ContactID. Surprise! Nothing in the interface tells you that what you see is not what is stored!

   In fact, a Lookup list can display several fields, for example Lastname, Firstname, and Middlename, to help you choose the right entry, but the table can only store one value. The value that is stored does not have to show in the drop list at all! Now that's helpful but more than a little confusing!

   Warning: Lookup Fields on a Table
   Many database designers avoid using Lookup fields on tables. It is easy to forget that what you see in the table (like a name) may not be what is stored in that field (like an AutoNumber). Joining tables together using fields of different data types makes a mess when you try to create subforms and subreports. It can be hard to figure out where the problem is!

   Let's look at what the wizard created for the Lookup list for Travel Agent.

4. Switch to Design View for the Trips table again and click on Travel Agent.

5. In the bottom pane of the window, click on the tab Lookup.
   Many properties are filled in now.

   Display Control shows how the field will display, as a Combo Box, List Box, or Text Box. A combo box can show several fields to help you pick.

   Row Source is a query that is written out in SQL. The results of this query form the Lookup list. The next properties control what columns in the query results will show in the Lookup list and what value will be stored.

   Bound Column tells which column from the Row Source query will be saved in the Trips table. It is usually the first column, but does not have to be.

   Column Count tells how many columns there are in the drop list.

   Column Widths sets the width for each column in the drop list. A value of 0" here means that the list will use the width of the column itself. If the query has several columns, you usually want to set each width separately, like 0",0.5", 0.33",1".

   List Rows sets the number of rows to show before using a scroll bar.

   List Width sets the overall width of the list. This needs to be larger than or equal to the total of the column widths.

   Later we will look more closely at the properties shown here.
   

6. Switch to Datasheet View.
7. Open the Lookup list for record 2, Alaska cruise.
8. Click on Martinez.
   The old TravelAgent field tells us that he was the agent for this trip.
   

9. Set the Travel Agent for the Ireland trip to Martinez.

   

10. Set the Travel Agent for the Italy trip to Chavez.

    As you edit a record, a pencil icon appears beside the record at the far left. The record is dirty, meaning that you have made a change that has not yet been saved.

    Point of Confusion: When is a change to a record saved?
    Changes to records are not saved automatically until you move on to a different record. Just changing to a different field in the same record does not save the change.

    To save changes without leaving the record, use the Save button on the Quick Access Toolbar or the Save Record button on the Home ribbon tab.

---

Access 2007: Delete Field

Now that you have selected travel agents in the new column Travel Agent, do you need the original field TravelAgent (no space)?

What will change if you delete this field?

• You lose the comments about the trips arranged through a company hosting the convention. Only the name and company and email address are in the Contacts table. Not much of a loss! You could even add an entry in the Contacts table, like No Agent, to cover the trips without a travel agent.

• Any forms, reports, or queries that included the field you deleted will show an error. Access will not automatically switch to the new field. You can manually adjust or re-create.

So removing the old column has some important consequences. It's better to get the fields right at the beginning!

1. Select the column for the Memo field TravelAgent by clicking the column header.
   
2. Press the DELETE key.
   A message box ask you to confirm that you want to delete the selected field or fields.
3. Click Yes.
   The column vanishes.
4. Close the Trips table.
   Your change was automatically saved.

---

Access 2007: Repair Form Errors

Because the TravelAgent field was deleted, one of the text boxes in your Trips AutoForm now shows an error, #NAME?. This code means that Access is looking for a named object that it cannot find. Either the object has been deleted or renamed or the name is misspelled. In this case, you deleted it!

Next you will fix the forms to refer to the new field. The same method applies to reports also. For this lesson the goal is to show you how awkward it can be to change tables after other objects have been created based on those tables. Plan well first!

In later lessons you will learn how to insert controls yourself and how to use a Control Wizard to set up the control.

1. Open the Trips AutoForm.
   The TravelAgent text box shows an error code, #NAME?.
   In many cases you could just edit the Source property for the box.

   In the table datasheet, the new Travel Agent field used a combo box so you could pick a name instead of a ContactID. But if you edit the Source property for the text box in the existing form, you do not get a combo box. Unexpected.

   There is a simple work around. You will re-create the form to get the text box you need and then copy/paste it to the existing form. This lets Access do the heavy lifting for creating the combo box.

2. Select the table Trips in the Navigation Pane and click the AutoForm button to create a new form.
   The new form opens in Layout View.
3. Switch to Design View.
4. Select the text box at the top of the new form, Travel Agent, and Copy.
5. Switch to the original form Trips AutoForm in Design View.
6. Select the text box with the error and Delete.
7. Paste.
8. If necessary, drag the pasted text box to the location of the deleted text box.
9. Switch to Form View and click in the Travel Agent field for the first record
10. Click the down arrow for the box.
    A list of agents from the Contacts table appears. The control is a combo box. Success!
11. Similarly, repair the forms Trips Autoform - Your Name and Trips AutoForm for Print.
12. Close the new form without saving it.

To correct a report, you can use the same method. The AutoReport, however, uses a control without an attached label while the Report Wizard uses controls with attached labels. So you should use the Report Wizard again to get a control that you can copy and paste.

---

Access 2007: Repair Report Errors

To correct a report, you can use a similar method. Use the Report Wizard to create a temporary report, which will automatically include the new field. Then copy and paste the new control to other reports. Do not use the AutoReport for this. It does not include a label for the field.

Check for formatting issues: Be careful to check what pasting a control did to the way your form or report prints. A wide control can automatically widen the form or report so that it won't fit on one page any more. A tall control might change what fits vertically on a page.

1. Open Trips Report Wizard by double-clicking it in the Navigation pane.
   A Parament dialog opens. What is that?
   Access cannot find the field that you deleted. It is asking you to provide a value.

2. Open Trips Report Wizard in Design View.
   Again you have an error triangle and the problem is an invalid source for the control.
3. Select the Trips table and use the Report Wizard to create a temporary report.
   The new Trips report opens in Report View.
4. Switch to Design View.
5. Select the first control, Travel Agent and copy.
6. Switch to the Design View of Trips Report Wizard.
7. Delete the control with the error.
8. Paste.
9. Drag the new control down and position it where the deleted control was.
10. Switch to Report View.
    The new control has a border.
11. Close Trips Report Wizard and save changes.
12. Similarly, repair your other reports based on the report wizard.
    Do not bother to fix your other autoreports. Those are easy to recreate!

---

Access 2007: Subdatasheet

You now have two tables in a One-to-Many relationship. One record in Contacts is connected to Many records in Trips. The table on the One side (Contacts) will show a subdatasheet of the related records from the Many side (Trips). A relationship must be in place for subdatasheets to be available.

1. Open the Contacts table again.
   A tiny + in a box, , shows at the left of each record because this table is on the One side of a One-to-Many relationship.

   
   
   

2. Click on the beside the record for Martinez.

   

   A subdatasheet appears below the record and shows the records of the two trips that Martinez handled.

   The subdatasheet does not show the column Travel Agent because that is the field on which the tables are joined. Remember that this Lookup field in the Trips table actually stores the ContactID value, even though you see a LastName.

   Unfortunately, the subdatasheet does not show the name of the table that the record comes from. If you know your tables well, you will recognize the TripID field as the primary key for the Trips table.

3. Expand the subdatasheet for each of the other agents.

   

   Chavez and Gardner show one record in the subdatasheet.

   The record for Landers shows a blank record in the subdatasheet. There are no trips in the Trips table yet for which Landers was the travel agent.

   Having several subdatasheets showing can make a table very hard to read. It is not normally a good plan to work directly with the table. Forms are much easier to use for data entry and review.

4. Open Print Preview while the subdatasheets are showing.
   Whoops. The fields for the records will print (on multiple pages) but not the photos and not the subdatasheets. How sad!
5. Close the Contacts table.