Changing Field Names


Perhaps you misspelled a field name when you first created one of your tables. Or perhaps you’ve decided that one of the field names isn’t descriptive enough. As you learned in Chapter 4, you can change the displayed name for a field by setting its Caption property. But you won’t necessarily want the hassle of giving the field a caption every time it appears in a query, a form, or a report. Fortunately, Access 2007 makes it easy to change a field name in a table-even if you already have data in the table.

Note 

The next several examples in this chapter show you how to change the Contacts table that you created in the previous chapter to more closely match the tblContacts table in the Conrad Systems Contacts sample database.

You created the first draft of the Contacts table by using a table template. Now you need to make a few changes so that it will hold all the data fields that you need for your application. The table template does not give you the option to rename the fields before creating them, but now you decide to rename one of the fields before beginning work on the rest of your application.

Renaming a field is easy. For example, the table template created a field called Address, but you’ve decided that you want to have two address fields because a contact could have a work address and a home address in this database. It makes sense to change the field name to reflect the actual data you intend to store in the field, so let’s change Address to WorkAddress. Open the Contacts table in the Contact Tracking database in Design view, use the mouse to move the insertion point to the beginning of the Address field name, and then type Work. You can also click in the field name, use the arrow keys to position the insertion point just before the letter A, and type Work. As you learned in the previous chapter, we recommend that you not have any spaces in your field names, so do not put a space between the words Work and Address. Your field should now be called WorkAddress. The table template also chose the Memo data type for this field, which is not necessary. Tab to the Data Type column, click the small arrow in the column (or press Alt+Down Arrow) to open the list, and select Text from the list of data types. While you’re at it, press F6 to move to the Field Properties section of the window, tab to the Field Size property, and change the field size to 255. Then, tab down to the Caption property, and change the field caption to Work Address. Your result should look like Figure 5–10.

image from book
Figure 5–10: You can change a field name, a field data type, and a field caption in Design view.

image from book
Comparing the Two Contacts Tables

As you follow along with the examples in this chapter, it might be useful to compare the structure of the Contacts table you built in Chapter 4, and the actual tblContacts table in the Conrad Systems Contacts sample database. If you exactly followed the instructions in Chapter 4, your Contacts table in the Contact Tracking database should look like Table 5–1. You can see the actual design of tblContacts in Table 5–2.

Table 5–1: Contacts
Open table as spreadsheet

Field Name

Type

Length

ContactID

AutoNumber

 

Company

Text

255

Last Name

Text

255

First Name

Text

255

E-mail Address

Text

255

Job Title

Text

255

Business Phone

Text

255

Home Phone

Text

255

Mobile Phone

Text

255

Fax Number

Text

255

Address

Text

255

City

Text

255

State/Province

Text

255

Zip/Postal Code

Text

255

Country/Region

Text

255

Web Page

Hyperlink

 

Notes

Memo

 

Attachments

Attachment

 
Table 5–2: tblContacts
Open table as spreadsheet

Field Name

Type

Length

ContactID

Auto Number

 

LastName

Text

50

FirstName

Text

50

MiddleInit

Text

1.

Title

Text

10

Suffix

Text

10

ContactType

Text

50

BirthDate

Date/Time

 

DefaultAddress

Integer

 

WorkAddress

Text

255

WorkCity

Text

50

WorkStateOrProvince

Text

20

WorkPostalCode

Text

20

WorkCountry

Text

50

WorkPhone

Text

30

WorkExtension

Text

20

WorkFaxNumber

Text

30

HomeAddress

Text

255

HomeCity

Text

50

HomeStateOrProvince

Text

20

HomePostalCode

Text

20

HomeCountry

Text

50

HomePhone

Text

30

MobilePhone

Text

30

EmailName

Hyperlink

 

Website

Hyperlink

 

Photo

Attachment

 

SpouseName

Text

75

SpouseBirthDate

Date/Time

 

Notes

Memo

 

CommissionPercent

Number

Double

Inactive

Yes/No

 

As you can see, we have a lot of work to do to make the two tables identical: rename fields, move fields, insert fields, add new fields, and change data types and lengths.

image from book

Before we go any further, you should rename the remaining fields and add captions so that they more closely match the fields in the tblContacts table in the Conrad Systems Contacts sample database. Following the preceding steps for renaming fields and changing the Caption property, go through each of the fields and change them as follows:

Open table as spreadsheet

Old Name

New Name

Caption

Last Name

LastName

Last Name

First Name

FirstName

First Name

E-mail Address

EmailName

Email Name

Job Title

Title

 

Business Phone

WorkPhone

Work Phone

Home Phone

HomePhone

Home Phone

Mobile Phone

MobilePhone

Mobile Phone

Fax Number

WorkFaxNumber

Fax Number

City

WorkCity

Work City

State/Province

WorkStateOrProvince

State/Province

Zip/Postal Code

WorkPostalCode

Postal Code

Country/Region

WorkCountry

Work Country

Web Page

Website

 

Attachments

Photo

 

Your table should now look like Figure 5–11. Click the Save button on the Quick Access Toolbar to save the changes to the table.

image from book
Figure 5–11: After renaming the fields in the Contacts table created from the template, it is beginning to look more like the table in the Conrad Systems Contacts sample database.

Inside Out-Why the Field Names Have Spaces in the Table Templates 

Office Access 2007 provides enhanced capabilities to move not only your data but also your entire application to a Microsoft Windows SharePoint server. You’ll learn more about these features in Chapter 22, “Working with Windows SharePoint Services.” When you move or copy an Access table to a Windows SharePoint server, Windows SharePoint Services stores the data in an object called a list. One shortcoming of Windows SharePoint Services lists is the fields do not have a Caption property, so a Windows SharePoint Services list always displays the field name for a column of data. Because Microsoft is emphasizing the new Windows SharePoint Services features in Access 2007, all the table templates use spaces in the field names to make them more readable. As you’ll learn later in Part 7, “Designing an Access Project,” the Conrad Systems Contacts sample database is designed for you to easily migrate to SQL Server, not Windows SharePoint Services. If your application is more likely to upsize to SQL Server, you should consider not using spaces in your field names so that you won’t be forced to include bracket or quote delimiters in your references to these field names.




Microsoft Office Access 2007 Inside Out
MicrosoftВ® Office Access(TM) 2007 Inside Out (Microsoft Office Access Inside Out)
ISBN: 0735623252
EAN: 2147483647
Year: 2007
Pages: 234

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net