Database Strings

A string is text made of a readable letter, a symbol, a word or a group of words. When creating a table in Datasheet View, to apply a Text field:

If you create a new field by replacing the Add New Field column header with a name, the new field automatically receives the Text data type To change the data type of a field, click its column header or a cell under its column. In the Data Type & Formatting section of the Datasheet tab of the ribbon, click the arrow of the Data Type combo box and select Text You can also use the Field Templates window: Under Basic Fields, you can drag Single Line of Text and drop it on the table

Alternatively, you can drag one of the following fields and drop it on the table: Under Assets: Item, Manufacturer, or Model Under Contacts: Company, Last Name, First Name, E-mail Address, Job Title, Business Phone, Home Phone, Mobile Phone, Fax Number, City, State/Province, ZIP/Postal Code, or Country/Region Under Events: Title or Location Under Issues: Title Under Projects: Project Name Under Tasks: Title If creating a table in Design View, to configure a field that can hold any type of string, specify its Data Type as Text: A Text data type allows the user to type any kind of characters or group of characters. This field can hold up to 255 characters. Practical Learning: Introducing Strings

1. Start Microsoft Access and, to create a new database, click Blank Database
2. Set the File Name to Real Estate2 and click Create
3. Double-click ID, type PropertyID, and press Enter
4. Double-click Add New Item and type Property #
5. Right-click the Table1 tab and click Save
6. Set the name to Properties and click OK
7. If the Field Templates window is not available, on the ribbon, click Datasheet if necessary and, in the Fields & Columns section, click New Field.
   From the Field Templates, under Assets
8. Drag Category and drop it on the table on the right side of Property #
9. On the table, double-click Category and type Property Type
10. Click the arrow under the Property Type column header and click the button that appears
     
11. Press Ctrl + A to select everything in the list and press Delete
12. Replace the list with the following:
     

    Single Family

    Townhouse

    Condominium

    Unknown

13. In the Default Value combo box, select Unknown
     
    
14. Click OK
15. From the Field Templates, under Assets
16. Drag Condition and drop it on the table on the right side of Property Type
17. On the table, click the arrow under the Condition column header and click the button that appears
     
18. Press Ctrl + A to select everything in the list and press Delete
19. Replace the list with:
     

    Unknown

    Excellent

    Good Condition

    Needs Some Repair

    Bad Shape

20. In the Default Value combo box, select Unknown
21. Click OK
22. Right-click the Properties tab and click Design View
23. Complete the list of fields with the following
     

    Field Name	Default Value
    Property #
    Property Type	"Unknown"
    Condition	"Unknown"
    Address
    City
    State
    ZIP Code
    Bedrooms
    Bathrooms
    Year Built
    Market Value

24. Save the table

Field Size

The size of a string is usually considered as the number of characters it contains or may hold. If you are adding the field by dragging it from the Field Templates, its default size is automatically set to 255 characters. In the same way, if you are creating the field in the Design View of the table, after specifying its Data Type as Text, its default size is set to 255 characters. This default value is too long in most cases (in previous versions of Microsoft Access, the default size was 50). In some circumstances, you may need to set a different value. You can therefore decrease or increase this size for any number between 1 and 255.

Before changing the field size, you must open the table in Design View. You cannot change the field size in the Datasheet View. The column width is the number of characters that the field can show, not the number of characters that the field can contain. To specify the maximum number of characters that a Text column can contain, in the lower section of the table, click Field Size and type the desired number.

When setting the Field Size property by itself, the database would make sure that the user can only type so many characters. For example, it is very unlikely to have a first name of a person that is 50 characters. In this case, you can safely reduce the number of characters to 20 or 30. On the other hand, if you are creating a field whose length is hard to predict, you can set the length to a reasonable value. There are other ways you can decrease the number of characters that are allowed in a field. Over all, you should be able to predict the types of values that would be entered in the field.

If a control has been created already, when performing an operation on it, you may want to know how many characters it has. To support this operation, Microsoft Access provides the Len() function. Its syntax is:

Number Len(String)

When calling this function, pass a string in between double-quotes or the name of a control. The function then produces the number of characters in it

Practical Learning: Setting Field Sizes

1. In the upper section of the view, click Property # and press F6 to move the caret to the lower section of the view
2. As the caret is on the Field Size field, type 10
3. In the top section of the table, click Property Type
4. In the lower section of the table, click Field Size and type
5. Set the other field sizes as follows:
    

   Field Name	Field Size
   Property #	10
   Property Type	40
   Condition	40
   Address	100
   City	50
   State	2
   ZIP Code	16
   Bedrooms	3
   Bathrooms	5
   Year Built	5
   Market Value	10

6. Save the table
7. To change the view, in the Design tab of the ribbon, click the View button
8. Enter the following records:
    

   Address	City	State
   1640 Lombardo Ave	Silver Spring	MD
   10315 Hacht Rd	College Park	Ctrl + '
   6366 Lolita Drive	Laurel	Ctrl + '
   9002 Palasko Hwy	Falls Church	VA

9. Close the table
10. Open the Red Oak High School database you created in Lesson 5 and continued in Lesson 11
11. In the Navigation Pane, right-click Students: Table and click Design View
12. Change fields sizes of the following fields as follows:
     

    Field Name	Field Size
    StudentNumber	20
    FirstName	50
    MI	2
    LastName	50
    Gender	30
    Address	80
    City	50
    State	40
    HomePhone	32
    EmailAddress	80
    ParentsNames	80
    EmergencyName	80
    EmergencyPhone	32

13. Change captions of the fields as follows:
     

    Field Name	Caption
    StudentID	Student ID
    StudentNumber	Student #
    FirstName	First Name
    LastName	Last Name
    ZIPCode	ZIP Code
    HomePhone	Home Phone
    EmailAddress	Email Address
    ParentsNames	Parents Names
    SingleParentHome	Lives in Single Parent Home?
    EmergencyName	Emergency Name
    EmergencyPhone	Emergency Phone

14. Save and close the table
15. In the Navigation Pane, under Students: Table, right-click the Students form and click Design View
16. Complete the design of the form as you see fit. Here is an example but do not strive to exactly match this screen shot (design the form anyway you want, as long as you complete it because you will need it in future lessons):
     
    
17. Save and close the Students form

Double Quotes ""

A string can be treated as a constant value. As such, it can be passed to a function. However you use a string, if you already know its value, you must include it in double-quotes. Examples are "@", "Q", "Validating", "Cross Over". You can also assign a string to a control. To do this, access the Properties window of the control and, in its Control Source, type = followed by the string inside double-quotes.

Text Input Masks

Because users can be tempted or distracted to enter just anything in a Text field, Microsoft Access provides techniques to control what goes in a field and what must be prevented. This is the role of a mask. A mask is a technique of creating sections, also called placeholders, in a field. A section can be configured to accept only a letter, only a digit, a character or a digit, any symbol, nothing, or to display a particular symbol that the user cannot change. When creating the mask, you will use some predefined characters and create a combination of your choice.

Before creating a mask for a field, Microsoft Access comes with various masks you can apply to a field to control the user's input such as dates, times, US Social Security Number, Currency values, etc. To apply one of these masks, you can use the Input Mask Wizard. To do this, set the Data Type of the desired field to Text. Then, in the lower section of the table, click the Input Mask property and click its ellipsis button . This would start the wizard.

The first page of the wizard displays the most regularly used masks, including those for US/Canada telephone number, US Social Security #, US ZIP code, date, time, etc:

If you see a mask that suits you, you can check it by clicking the Try It text box. This would show the placeholders for the sections of characters that could be entered during data entry:

If you see a mask you want to use, you can click it and click Next. If none of the masks suits your need, you can create a new one and add it to the list. To do this, you would click the Edit List button. This would bring the Customize Input Mask Wizard dialog box:

By default, the Customize Input Mask Wizard offers four already created masks, including US/Canada phone number, US Social Security Number, US ZIP Code, and telephone extension number. You can check them by click the Next Record button in the bottom section of the dialog box.

To create a new mask, you can click the New (Blank) Record button .

After creating a new mask, you can click Close. Once you have located the desired mask, you can click it and click Next to continue. The second page of the wizard allows you to accept or customize the mask you had selected. Once you are ready, you can click Next. The third page presents an option that allows you to specify how the value in the field would be stored in the database. It presents two radio buttons and you can either accept the suggestion or select the other and click Next. The fourth page of the wizard doesn't do much. It simply lets you know that the wizard is ready to create the mask. Therefore, you can click Finish.

If none of the masks provided by the wizard suits you, you can create your own. To do that, click the Input Mask property for the desired fields and uses the following characters to create the mask:

You can use any of these symbols to create a mask. If you want to include a word or sentence as part of the mask, type it in any section as desired. Here are examples of Custom Masks:

Practical Learning: Using Input Masks

1. Re-open the Real Estate2 database you started earlier
2. In the Navigation Pane, right-click Properties: Table and click Design View
3. In the top section, click State
4. In the lower section, click Input Mask, type >LL and press Enter
5. In the upper section, click ZIP Code and press F6
6. In the lower section, click the Input Mask field and click its ellipsis button .
   If you receive a Microsoft Office Access Security Notice, read it and click Open
7. When asked whether you want to save the table, click Yes
8. In the first page of the wizard, click ZIP Code
    
   
9. Click Next
10. Click Next again 
11. Click the first radio button
     
    
12. Click Next and click Finish
13. Save the table
14. To switch its view, right-click the Properties tab and click Datasheet View
15. Enter the following records:
     

    Address	City	State	ZIP Code
    1640 Lombardo Ave	Silver Spring	MD	20904-1136
    10315 Hacht Rd	College Park	Ctrl + '	20747
    6366 Lolita Drive	Laurel	Ctrl + '	20707-1014
    9002 Palasko Hwy	Tysons Corner	VA
    		DC	20011
    	Alexandria	VA	22024-2006

String Concatenation

The & operator is used to add one string to another. It can also be used to append the contents of two controls, or expressions. This is considered as concatenating them. For example, it could allow you to concatenate a first name and a last name, producing a full name. The general syntax of the concatenation operator is:

Value1 & Value2

The values on both sides can be constant values. Here is an example:

"Eating" & " Food"

One value can be the name of a field and the other a constant value or both values can be the names of field. Just as you can involve two values in a concatenation, you can also use more than one. All of them be can constant strings. The operation can involve only the names of fields, or the expression can be a combination of constant values and names of fields.

To concatenate more than two strings, you can use as many & operators between any two expressions as necessary. After concatenating the strings or values, you can assign the result to another value or expression using the assignment operator. The formula to use is:

=Field1 & Field2 & Field_n

Here is an example:

= LastName & ", " & FirstName

Once again, you should include the name of a field in square brackets:

=[LastName] & ", " & [FirstName]

Here is another example:

=[Address] & " " & [City] & " " & [State] & " " & [ZIPCode] & " " & [Country]

This would display a complete address in a field.

Practical Learning: Concatenating two Strings

1. Re-open the Red Oak High School database
2. In the Navigation Pane, under Students: Table, right-click the Students form and click Design View
3. In the Control section of the Ribbon, click Text Box and click the middle of the Form Footer section
4. Change the accompanying caption to Full Name: and double-click the text box itself
5. In the Properties window, change the following characteristics:
   Name: txtFullName
   Control Source: =[LastName] & ", " & [FirstName]
6. Save and close the Students form

Trimming a String

Trimming is an operation that gets rid of leading or ending spaces in a string. To remove any (empty) space on the left side of a string, you can use the LTrim() function. Its syntax is:

LTrim(Expression As String) As String

The original string is passed as argument to the function. The function then removes any empty space from the left of the string and then the function returns the resulting string (without any space from the left section). If the original string does not have any leading space, the function would return the same string.

To remove any space on the right side of a string, you can use the RTrim() function. Its syntax is:

RTrim(Expression As String) As String

The string to trim is passed as argument to the function. If that string has space on its right, this function would remove it and return the same string without any trailing space. Otherwise, the original string would be returned.

To remove empty spaces on both the left and the right sides of a string, call the Trim() function. Its syntax is:

Trim(Expression As String) As String

The string is passed to the function. The function then removes empty spaces, if any on each side, and the function returns the new string.

Character Case Conversion

There are various techniques you can use to convert a string from lowercase to uppercase and vice-versa. An alphabetical character is recognized as being in lowercase if it is one of the following characters: a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p, q, r, s, t, u, v, w, x, y, z. On the other hand, a character qualifies as uppercase if it is one of A, B, C, D, E, F, G, H, I, J, K, L, M, N, O, P, Q, R, S, T, U, V, W, X, Y, Z. All the other symbols are ignored even if on the keyboard you would press Shift to type them.

To convert a lowercase character or string to uppercase, you can use the UCase() function. Its syntax is:

UCase(Expression As String) As String

This function takes a string as argument and examines each one of its characters. If a character is an alphabetic character in lowercase, it would be converted to uppercase. If the character is either an alphabetical character in uppercase or it is not an alphabetic character, it would be kept "as is". 

To convert a character or a string from uppercase to lowercase, you can call the LCase() function. Its syntax is:

LCase(Expression As String) As String

This function takes a string as argument and examines each one of its characters. If a character is an alphabetic character in uppercase, it would be converted to lowercase. The case of all the other characters would be ignored.

String Comparisons

String comparison allows you to find out which one of two strings is longer or whether both strings are equal. When comparing two strings, the database engine checks lowercase and uppercase characters. To compare two strings, you can use the StrComp() function. Its syntax is:

StrComp(Expression1 As String, Expression2 As String, Option) As Number

The function takes two strings and an option as arguments. It then compares the strings:

• If the strings are the same, the function returns the number 0
• If the first string is shorter than the second, the function returns the number -1
• If the first string is longer than the second, the function returns 1
• If one of the strings is null, the function returns null

The third argument is optional, which means you don't have to provide it. If you want to influence the way the comparison is made, you can pass the third argument with one of the following values: -1, 0, 1, 0r 2.

Sub-Strings

There are many operations you can perform on individual characters of a string. These include creating a sub-string, checking for a character, finding the position of a character, or deleting a character or characters. These operations can be valuable when creating objects such as login dialog boxes.

To create a string that is made of one or more characters from the left side of a string, you can call the Left() function. Its syntax is:

Left(Expression As String, Length As Number) As String

This function takes two arguments. The first argument is a string. The second argument specifies the number of characters to be considered from the first argument. The function would then create a new string that is made of the number of characters from the left side of the first argument. To illustrate, consider a string such Republic passed as the first argument and consider that you pass 3 as the second argument:

Left("Republic", 3)

The function would consider the left 3 characters of the Republic argument and it would produce that sub-string. The resulting string would be Rep.

To create a string that is made of one or more characters from the right side of a string, you can call the Right() function. Its syntax is:

Right(Expression As String, Length As Number) As String

This function also takes a string and a number as arguments. The function then creates a new string that is made of the number of characters from the left side of the first argument. Consider the following example:

Right("Midnight", 5)

The function would consider the last 5 characters of the Midnight argument and it would produce that sub-string. The resulting string would be night.

To create a string that is made of one or more characters from anywhere in a string, you can call the Mid() function. Its syntax is:

Mid(Expression As String, start As Number, Optional Length) As String

This function also takes 3 arguments with required: a string, a starting position and an optional number. The function creates a new string using the first argument, from the start number. If you don't pass the third argument, the function returns a string from that position to the end of the string. Consider the following example:

Mid("Ideal", 2)

This function would start creating a new function from the second position of Ideal to the end. The resulting string would be "deal".

If you pass the third argument, the function would create a new string from the start position. Then it would count the Length number of characters from the start position and it would create that new string. Consider the following example:

Mid("Elizabeth", 2, 4)

This would produce "liza".

Locating a Character or a Sub String

You can analyze a string and find out whether it contains a certain character or a sub string. If it does, you can get the position of the character or the substring and use that information as you see fit. To support this operation, you can use the InStr() function. Its syntax is:

InStr(Optional start As Number,
      string1 As String,
      string2 As String) As Number

This function takes 3 arguments with 2 required. The second argument is the string to be examined. In it, the function would look for the character or string of the second argument. If you don't pass the first argument, the function would start from the beginning string of the second argument. If it finds it, it returns the position, a number from where the string2 string was found. If it doesn't find it, it returns 0. 

Character or Sub-String Replacement

When performing some operations on strings, you may want to find out whether a certain character or group of characters has been provided in a string. If so, you may want to replace it with a different character or with a new sub string. To perform this operation, you can call the Replace() function. Its syntax is:

Replace(Expression As String,
	LookFor As String,
	ReplaceWith As String,
	start As Number,
	count As Number,
	Option)

The Replace() function will look for the LookFor character or sub-string in the Expression string. If it finds it, then it will replace the LookFor character or sub string with the ReplaceWith character or sub-string. These first three arguments are required. Here is an example:

Replace("Elisabeth", "s", "z")

In this case, the function would look for s in Elisabeth. If it finds it, then it replaces it with z. If its doesn't find, it would not do anything.

By default, or in the absence of the other arguments, the Replace() function examines the Expression argument from start to end. If you don't want to consider all characters, you can pass the 4th argument to specify from what position of the Expression to work on. Then, the function would consider characters from that position to the end. If you want, you can ask the function to consider only a certain number of characters. To do this, pass a 5th argument as a number.

String-Based Data Types: The Memo

The Memo data type functions like the Text except that it can hold a longer text up to 64000 characters. The text is mostly provided as ASCII. This means that there is no formatting. To support a field that can hold text longer than the Text type can afford, Microsoft Access provides the Memo data type. You can apply this type in either the Datasheet View or the Design View.

To apply the Memo data type in the Datasheet View of a table:

• After specifying the column name or clicking a cell under it, in the Data Type & Formatting section of the Datasheet tab of the ribbon, click the arrow of the Data Type combo box and select Memo
• In the Field Templates window, you can drag one of the following fields and drop it on the table:
  • From Basic Fields: Multiple Lines of Text or Rich Text
  • From Assets: Description, Comments, or Model
  • From Contacts: Notes
  • From Events: Description
  • From Issues: Description and Comments
  • From Projects: Notes
  • Under Tasks: Description

If creating a table in Design View, to configure a field that can hold any type of string, specify its Data Type as Memo.

Introduction

Object Linking and Embedding (OLE) is a technique that consists of adding in your database (not just database, but our discussion will stop to Microsoft Access databases as if OLE had to do only with Microsoft Access) an object that was created using another application. The technique used to include such an object is particularly easy. Over all, Microsoft Access does not care much what type of object you want to add to, or include in, your database. It is left to you to decide why you want to include the object and what type of object this would be. Some objects (can tremendously) increase the size of your database.

To use an OLE object in a field, you can work from the Datasheet View or the Design View of a table.

To apply the OLE type in the Datasheet View of a table, after specifying the column name or clicking a cell under it, in the Data Type & Formatting section of the Datasheet tab of the ribbon, click the arrow of the Data Type combo box and select OLE Object. If creating a table in Design View, to configure a field that can hold any type of string, specify its Data Type as OLE Object.

Objects and Data Fields

After setting the Data Type of a field to OLE Object, since the object is external, there are not too many options or properties provided by the table. The application in which you created or will create the object should control it. To actually include an external object into the field, whether using the table Datasheet View or the form in Form View, the user can right-click the field and click Insert Object. This would open the Insert Object dialog box that presents two options to create or select the object:

• If you want to use an application installed in your application to create the document, you can click the Create New radio button. Then, in the Object Type list, click the application and click OK
• If the object has been created and resides on a portable media (floppy disk, CD, DVD, etc), on your hard drive or on the network your computer is connected to, you can click the Create From File radio button and click Browse. This would bring the Browse dialog box that allows you to locate and select the document

1. From the resources that accompany these lessons, open the Bethesda Car Rental1 database
2. In the Navigation Pane, right-click Cars: Table and click Design View
3. Notice that the Picture field has its Data Type set to OLE Object.
   To switch the table to the another view, right-click its title bar and click Datasheet View
4. Right-click the Model column header and click Freeze Columns
5. Scroll to the right to make sure you can see both the Model and the Picture columns
6. Right-click the first empty cell under Picture and click Insert Object…
7. On the Insert Object dialog box, click the Create From File radio button and click the Browse button
8. In the Browse dialog box, using the tree list under the Directories label, locate the Cars folder that accompany these lessons and select it
9. In the File Name list box, click accent.bmp
10. In the Browse dialog box, click Open and, in the Insert Object dialog box, click OK
11. On the ribbon, click Home
12. In the Record section, click More -> Unfreeze
13. Close the table. When asked whether you want to save it (the question is stated because the structure of the table was modified when we froze the columns, not because we added the picture), click No
14. In the Navigation Pane, under Cars: Table, double-click the Cars form to open it
15. Click the Next Record button to get to the second car
16. Right-click the white empty area on the top-right section and click Insert Object
17. On the Insert Object dialog box, click the Create From File radio button and click Browse
18. Navigate to your Cars folder. From the list of File Names, click grandmarquis.bmp and click Open
19. On the Insert Object dialog box, click OK
20. In the same way, add a picture for each car included in the database. If the picture of a car does not appear in the list, skip it. This simply means that there is no picture
     
21. After using the form, close it

As mentioned already, an object created with an external application can be added to a database and the object would be included “as is”. When this is done, Microsoft Access uses an encryption technique to make the object become part of the database so you do not have to remember to include the object when distributing the database. Because of this, objects such as pictures can highly increase the size of a database. This can be convenient at times. Fortunately, the alternative to this scenario is to provide only a link to the document or file from the database to the external document.

Besides the ability to provide a link to a document that could be a picture, you can also create a link to a document that provides more information about a record. You can also create a record that would hold the email addresses of the records on a table.

All these can be taken care of through a hyperlink. As described in Lesson 11, a hyperlink a label that, when clicked, would open another document on the same computer, on a document on another connected computer, or on the Internet.

Practical Learning: Introducing Hyperlinks

1. Re-open the Bethesda Car Rental1 database
2. In the Navigation Pane, double-click Cars: Table to open the table

Creating a Hyperlink

To support links, Microsoft Access provides the Hyperlink data type. To create it, you can work in either the Datasheet or the Design View of a table. To apply the Hyperlink data type in the Datasheet View of a table:

• After specifying the column name or clicking a cell under it, in the Data Type & Formatting section of the Datasheet tab of the ribbon, click the arrow of the Data Type combo box and select Hyperlink
• In the Field Templates window, you can drag one of the following fields and drop it on the table:
  • From Basic Fields: Hyperlink
  • From Contacts: Web Page

If creating a table in Design View, to configure a field that can hold any type of string, specify its Data Type as Hyperlink.

Practical Learning: Creating Hyperlinks

1. On the Ribbon, click Datasheet
2. In the Fields & Columns section, click New Field
3. In the Field Templates window, expand the Basic Fields node if necessary.
   Drag Hyperlink and drop it between Condition and Notes
4. Double-click the Hyperlink column header and change its name to Car Information
5. Click the first box under Car Information and type http://www.hyundaiusa.com/accent
6. Close the table
7. In the Navigation Pane, right-click the Cars form and click Design View
8. Using the Field List, drag the Car Information and drop it on the form. Format or design it to appear like the other controls. Here is an example:
    
   
9. Save and close the Cars form

Introduction

The OLE Object provides a convenient means of adding an external file to a record. One of the problems with it is that it can add only one object. Sometimes you want to associate many documents to one record and you may have a different number of objects for each different record. Fortunately, Microsoft Office Access 2007 solves this problem through an attachment. An attachment is a technique of associating one or more files to a record.

Practical Learning: Introducing Attachment Fields

1. From the resources that accompany these lessons, open the Altair Realtors2 database
2. In the Navigation Pane, right-click Properties: Table and click Design View

Creating an Attachment Field

To start with an attachment field, you must create a field whose data type is an attachment. You can work in the Datasheet View or in the Design View of a table. To create an attachment in the Datasheet View:

• Click a cell under an existing column or click a cell under the Add New Field column header. In the Datasheet section of the Ribbon, click the arrow of the Data Type combo box and select Attachment
• Display the Field Templates window:
  • Under Basic Fields, drag Attachment and drop it on the table
  • Under any of the other sample tables, drag Attachments and drop it on the table

If creating a table in Design View and if you are creating a new column, to configure it to hold one or more attachments, specify its Data Type as Attachment. If a field has already been created and the records have been added to the table, you cannot change the data type of a field to Attachment. You would receive an error when you try to save the table:

1. In the top section of the table, under Field Name, right-click Description and click Insert Rows
2. Set the name to Pictures and the Data Type to Attachment
3. Save and close the table
4. In the Navigation Pane, under Properties: Table, right-click (the) Properties (form) and click Design View
5. In the Controls section of the Ribbon, click Attachment and click the empty right side of the form
6. Move the accompanying label above the newly added control and change its text to Picture(s)
7. If the Properties window is not showing, right-click the newly added control and click Properties.
   In the Properties window, change the following characteristics:
   Name: atcPictures
   Record Source: Pictures
8. Appropriately position and resize the attachment placeholder as you see fit
    
   
9. Save the form and switch it to Form View

Using an Attachment

Microsoft made a tremendous job with the attachment feature of Microsoft Office Access 2007. In other words, it is extremely easy to attach a series of objects to a record. You can work either in the Datasheet View of a table or form or in the Form View of a form. Once the object is displaying, you can either double-click the field or right-click the field and click Manage Attachments. This would open the Attachments dialog box:

In the Attachments dialog box, to create an attachment, you can click the Add button. This would open the Choose File dialog box. It behaves like the Open dialog box. When using it, select the file or object and click Open. The selected object would be added to the Attachments list view. In the same way, you can add the other objects one at a time. Alternatively, to add many objects, in the Choose File dialog box:

1. To select objects one at a time:
   1. Click one of the objects to add
   2. Press and hold Ctrl
   3. Click each object to add, one object at a time
   4. After selecting the desired objects, release Ctrl

   5. Click Open
2. To select objects in a range:
   1. Click one object at one end of the range
   2. Press and hold Shift
   3. Click the object at the other end of the range
   4. Release Shift
   5. Click Open
3. To select objects in a range:
   1. Click somewhere on the left (or the right) or the top (or the bottom) side of an object at one end of the range and hold the mouse down
   2. Drag in the opposite direction to draw a fake rectangle that would touch or include the objects to be added
   3. When the objects have been included in the range, release the mouse
   4. Click Open

To remove an item from the collection, click it in the Attachments list box and click Remove. Once you are ready with the list of attachments, click OK.

After the attachments have been created, to access those of a record, navigate to that record. Then, right-click the placeholder of the attachment. A menu would come up. Among other menu items, the menu has a Forward and a Back options:

• If the record has only one attachment, both menu items would be disabled
• If the record has only two items, when you access the record, the Forward menu item would be enabled and the Back option would be disabled. You can click Forward to access the next object. At this time, the Forward menu item would be disabled and the Back option would be enabled. You can click Back to access the previous object
• If the record has more than two items, when you access the record, the Forward menu item would be enabled and the Back option would be disabled. You can click Forward to access the next object. You can keep clicking Forward to advance in the collection. You can click Back to access the previous object. From the second object to the one before the last, both the Forward and the Back menu items would be enabled. When you get to the last object of the collection, the Forward menu item would be disabled and the Back item would be enabled

Practical Learning: Attaching  Objects to a Record

1. In each record, right-click the placeholder under Picture(s) and click Manage Attachments...
2. In the Attachments dialog box, click Add...
3. From the resources that accompany these lessons, inside the Houses folder, locate a file whose 6 (first) characters are the same as the property number.
   If the file name does not end with a, such as 475974.bmp, click it:
    
   
   
   If the file name ends with a, such as 524880a.bmp, click it and click Open. Then click Add again and add additional files with the same 6 digits in the name and that ends with a letter such as b, c, etc
    
   
4. After adding the picture(s), click OK
5. Navigate to each record. Right-click the picture and review the pictures attached to each record

Exercises  

Yugo National Bank

1. Open the Yugo National Bank1 database
2. Open AccountTypes table in Design View and change its columns as follows:
    

   Field Name	Data Type	Field Size	Caption
   AccountTypeID	AutoNumber		Account Type ID
   AccountType	Text	50	Account Type
   Description	Memo

3. Save and close the table
4. Open TransactionTypes table in Design View and change its columns as follows:
    

   Field Name	Data Type	Field Size	Caption
   TransactionTypeID	AutoNumber	Long Integer	Transaction Type ID
   TransactionType	Text	50	Transaction Type
   Description	Memo

5. Save and close the table
6. Open Customers table in Design View and change its columns as follows:
    

   Field Name	Data Type	Field Size	Caption
   CustomerID	AutoNumber		Customer ID
   AccountNumber	Text	20	Account Number
   CustomerName	Text	50	Customer Name
   Address	Text	60
   City	Text	50
   State	Text	50
   ZIPCode	Text	20	ZIP Code
   Country	Text	50
   HomePhone	Text	40	Home Phone
   WorkPhone	Text	40	Work Phone
   WorkExtension	Text	20	Extension
   EmailAddress	Hyperlink		Email Address
   Notes	Memo

7. Save and close the table
8. Create a form for the AccountTypes table. Save the form as AccountTypes and design it as you see fit before closing it. Here is an example:
    
   
9. Create a form for the TransactionTypes table. Save the form as TransactionTypes and design it as you see fit before closing it. Here is an example:
    
   
10. Create a form for the Customers table. Save the form as Customers and design it as you see fit before closing it. Here is an example:
     
    

Watts A loan

1. Open the Watts A Loan1 database
2. Open the Employees table in Design View and change its columns as follows:
    

   Field Name	Data Type	Field Size	Caption
   EmployeeID	AutoNumber		Employee ID
   FirstName	Text	50	First Name
   LastName	Text	50	Last Name
   Title	Text	80
   WorkPhone	Text	40	Work Phone
   Address	Text	100
   City	Text	50
   State	Text	50
   ZIPCode	Text	20	ZIP Code
   Country	Text	50
   HomePhone	Text	40	Home Phone
   Notes	Memo

3. Save and close the table

World Statistics

1. Open the World Statistics1 database
2. Open the Countries table in to Design  and change it as follows:
    

   Field Name	Data Type	Field Size	Caption
   CountryID	AutoNumber		Country ID
   CommonName	Text	50	Common Name
   InternetCode	Text	10	Internet Code
   LongName	Text		Long Name
   GovernmentType	Text	60	Government Type
   WebSite	Hyperlink		More Information

3. Save and close the table

US Senate

1. Open the US Senate1 database 
2. Open the Senators table in Design View  and change it as follows:
    

   Field Name	Data Type	Field Size	Caption
   SenatorID	AutoNumber		Senator ID
   SenatorName	Text	50	Senator Name
   SeatingStatus	Text	50	Seating Status
   Gender	Text	40	Last Name
   State	Text	2
   Race	Text	40
   Religion	Text	50

3. Use the Lookup Wizard on the State to create a list of the US states using the abbreviation for each
4. Use the Lookup Wizard on the Race column to create a list of the races. The options will be Black, White, Native American, Hispanic, Asian, Other
5. Use the Lookup Wizard on the Religion to create a list of the religions. The options will be Catholic, Muslim, Jewish, Baptist, Presbyterian, Atheist, Other
6. Save and close the table