Properties of Database Fields

We have seen so far that, in order to use anything in your database, you must first create it, but the process of creating something depends on its type. For example, tables are created in Datasheet View or designed in Design View. Queries are created by adding fields from existing tables or other queries. Forms and reports are created by adding existing fields or adding independent fields to them. Fields are created and added to these objects.

Like every thing else in your computer, every object or field you use in a database must have a name. In fact, when creating an object, the first property that must be set is its name.

When you start a brand new table in Datasheet View, Microsoft Access assigns various names to its fields (or columns). These are Field1, Field2, etc. We saw how to change these fields. If you start a brand new table in Design View, you must specify a Field Name for each column that will be used.

When you create a simple query, as you select fields from a table or query and add them to the new query, they keep their names and, in this case, the names are held in the originating object (the original table or query). You can still create new fields by combining existing fields from the original table or query. When you create such a new field, you must give it a name. To create a new field in a query, you can select an empty column and, in the field box, assign an expression to it. After doing this, the query would provide a suggested name like Expr1. You can then change the name to something else than Expr1.

If you drag an existing field from a Field List to a form or report, the field is added and it keeps it name. You can check this in the Properties window. If you click a control on the Toolbox and click a form or a report, Microsoft Access would assign in a name based on the control. For example, if you add a Text Box control to a form or report, it would named Text0. If you add a second Text Box, it would be named Text2. Whether you drag a field from the Field list or add a control from the Toolbox, after placing it on a form or report, you can change the name it received.

In the Table Datasheet View, we saw that, in order to change the name of a field, you can double-click it; you can also right-click it and click Rename Column. This puts the field in edit mode and selects it. You can then type the new name and press Enter. In the table Design View, to change the name of a field, you can double-click its name, which would put it in edit mode. Then you can either edit the name or change it completely. In a form or report, you can change the name of a field only while in Design View. To do this, you can access the Properties window of the object. Then, edit or change the string of its Name value.

1. The Rockville Techno database should still be opened
   On the Database window, click the Reports button
2. Double-click the ListOfAssets report to open it in Print Preview
3. After viewing it, to switch it to Design View, on the Print Preview toolbar, click the View button
4. In the Detail section of the report, you should have a text box and its accompanying label. If you do, click the Unbound text box and press Delete
   On the Toolbox, click the Text Box control and, on the report, click anywhere under Detail bar
5. Since the text box was accompanied by a label, click its label and press Delete
6. On the report, click an unoccupied area to make sure nothing is selected.
   On the report, double-click the newly added text box to select it and display its Properties window
7. In the All tab of the Properties window, click Name, type txtCategory and press Enter
8. In the same way, add a new Text Box in the Detail section. Delete its label and change its Name to txtManufacturer
9. Add another Text Box control in the Detail section. Delete its label and change its Name to txtModelName
10. Add another Text Box control in the Detail section. Delete its label and change its Name to txtDatePurchased
11. Add another Text Box control in the Detail section. Delete its label and change its Name to txtUnitPrice
12. Arrange the fields as follows:
     
13. Save the report

Record and Control Source

We had previously mentioned that there are fields created from tables and used on the objects, and that these objects also use fields that either existed already or fields that are independent of any other. When adding or creating a field on a form or report, if data for that field originates from a previously created column on a table, the field is said to be “bound” because its data is bound to an existing field. If a field is either created as an expression that combines other fields or if the field doesn't depend on any existing field, the field is said to be unbound.

During the design of a form or report, if you drag a field from the Field List to the object, the new control is bound to the field from the table or query on the Field List. If you click a control on the Toolbox and click a section on the form or report, you can then decide whether the field is bound or not. If it is, you can access its Control Source in the Properties window and from the combo box, select a field from the list. Even if you had dragged a field from the Field List, you can change the control it its bound to by selecting a different one on the Control Source combo box. In future lessons, we will see how to create expressions that use a combination of existing such as creating a full name from the first and last names.

Practical Learning: Setting Control Sources of Objects

1. The Rockville Techno database should still be opened
   Click the button at the intersection of both rulers on the report. Then, in the Properties window, click Record Source and click its ellipsis button
2. From the list of fields, drag DateAcquired and drop it on PurchasePrice in the lower section of the query
3. Close the query. When asked to save it, click Yes and press Enter
4. On the report, click the first or left Unbound text box to select it. On the Properties window, click the Control Source field. Then click the arrow of its combo box and select AssetType
    
5. In the same way, set the Control Source of the second Unbound text box to Make
6. Set the Control Source of the third Unbound text box to Model
7. Set the Control Source of the last two Unbound text boxes to DateAcquired and PurchasePrice
8. Save the report and close it

Visibility

After adding a field to a form or report, it becomes visible and can be used. An object is referred to as visible if it can visually be located on the screen. A user can use an object only if he can see it. Most of the time, you will not be concerned with this aspect. On the other hand, there are situations that will lead you to create a control but hide it from the user. For example, since you cannot declare a (VBA) variable in Microsoft Access, you can instead create a control such as a text box, make it hold a value that you can use in an expression but hide it from the user who would never need to use such an object.

Availability

In order to use a control on a form or a report, the object that is hosting the control must allow it. For example, the user cannot type text or change the value of a combo box if this action was not made possible. This aspect is controlled in two different ways.

If the value of a text box is set by an expression, the user cannot change it. The other technique that controls the availability of an object is from the Enabled property. If you set it to Yes, which is its default value, the user can type a value into it or change it, provided the control’s value does not originate from an expression. If you set its Enabled property to No, the user will not be able to change the value of the control.

Practical Learning: Disabling a Field

1. The Rockville Techno database should still be opened
   From the Forms section, double-click CompanyAssets
2. Click the Asset ID text box and type 12. Notice that it does not allow any value from the user. For this reason, we will disable it
3. To switch the form to Design View, on the Form View toolbar, click the View button
4. Double-click the AssetID text box to access its Properties window. In the Properties window, click the Data tab and double-click Enabled to set it to Yes
5. Notice that its aspect and its label have changed
6. To switch to Form View, on the Design View toolbar, click the View button
7. Click the Asset ID text box and notice that, this time, it cannot even receive focus
8. Save and close the form

Field Locking

During data entry, a user usually clicks a field to give it focus and starts typing. As we will learn, some fields get an automatic number set and controlled by Microsoft Access. Some other fields use data created from an expression. These types of fields do not support data entry and their content cannot be changed directly by the user. In some other circumstances, you will create fields but would not like the user to be able to change their value. For this reason, you can lock such a field.

When a field is locked, the user cannot change its content. This characteristic is directly related to data entry and is therefore available on controls positioned on a form only. To lock a control, after selecting it in Design View, access its Locked property in the properties window and set it to Yes. If the control was previous locked, you can unlock it by setting this property to No.

Practical Learning: Locking a Field

1. The Rockville Techno database should still be opened
   In the Forms section, right-click CompanyAssets and click Design View
2. Click the AssetID text box. In the Properties window, double-click the Locked field to set it to Yes
3. Switch the form to Form View. Then save and close the form

Tool tip

A tool tip is a small yellow border-less box that appears when the mouse is positioned on top of a control for a few seconds. It is a fast means of providing help to the user. Because only the form displays controls in a manner suitable for them, a field can display a tool tip only on a form.

To create a tool tip, while in Design View of the form, access the ControlTip Text property of the field and type anything you want in it.

Practical Learning: Setting Tool Tips

1. The Rockville Techno database should still be opened
   In the forms section of the Database window, double-click the Clients form and, on the Clients form, click the Projects button
2. After viewing the Project form, switch it to Design View
3. Click the Client Name text box (the text box on the right side of the Client Name label). In the Properties window, click ControlTip Text
4. Type Client company of this project and press Enter
5. In the same way, click the following controls and change their ControlTip Text property as follows (in the following table, the Control column only refers to text box and the combo box on the form; the name in the Control column is the control on the right side of the label indicated):
    

   Control	ControlTip Text
   Project Name	Identifier set by the person who created the project
   Project Manager	Employee in charge of this project
   Client PO #	Purchase Order Number
   Project Description	Text that describes this project
   Est. Total Billings	Project total billing estimate
   Project Begin Date	Date this project must start or started
   Project End Date	Date this project should end
   Total Hours	Number of hours worked on this project so far
   Total Expense	Expenses performed on this project so far
   Amount Paid	Amount the client has paid so far
   Hours Billing	Total amount earned so far
   Total Billing	Total amount billed to date
   Amount Due	Total amount due to date

6. Switch the form to Form View and position the mouse for a few sections on Project Manager:
    
7. Do the same on various controls
   After viewing the tool tips, save and close the form

Status Bar Text

When a field receives focus, you can display a sentence on the status bar to provide some guidance or suggestion to the user. The text that displays on the status bar should be explicit enough but not too long. When creating it, make sure it can fit in the left section of the status bar, which should be long enough to explain anything.

The process of creating a status bar text depends on the control. To create a status bar text for a table, in the Description section for the field, type the string you want. Such a string would appear when the field receives focus in the table or query Datasheet View. If the field participates in an expression for a field of a query, the status bar text would not appear. 

To create a status bar text for a field on a form, while in Design View, access the Status Bar Text property of the control and type the desired string. Only the controls that can receive focus have a Status Bar Text property. For the same reason, because no field can receive focus on a report, this property is not available for fields on a report.

Practical Learning: Setting Status Bar Text for Fields

1. Open the Bethesda Car Rental1 database you created and click Tables in the Database window
2. Right-click the Employees table and click Design View
3. Click EmployeeID and press Tab twice
4. In the Description field for the EmployeeID field, type
   Automatic number set by the database engine
5. Press the down arrow key twice. That puts the cursor in the Description field for the DateHired field. Type Date the employee was hired
6. For the description of the EmployeeNumber, type Number set by Management 
7. In the Description of the FirstName field, type Employee’s first name 
8. In the Description of the MI field, type Middle Initial made of one letter
9. Complete the descriptions of fields
    
   
10. Save the table
11. To switch to Datasheet view, on the Table Design toolbar, click the View button 
12. Click a cell under the Date Hired column and observe the sentence on the Status bar
13. Click under EmailAddress and observe the Status bar
14. Close the Employees table
15. Open the Rockville Techno database
16. From the Forms section of the Database window, open the CompanyAssets form and switch it to Design View
17. On the form, click the Asset Type text box to select it. In the Other tab of the Properties window, click Status Bar Text
18. Type Category of asset and press Enter
19. In the same way, on the form, click the following text boxes and change their Status Bar Text value in the Properties window
     

    Text Box	Status Bar Text
    Make	The company that manufactures this item
    Model	The model displayed on the item
    Serial Number	The serial number set by the manufacture
    Date Acquired	The date this item was purchased
    Purchase Price	The price paid for this item, even if it is being financed
    Notes	A description or a few comments about the item

20. Save the form and switch it to Form View
21. Click different field and observe the message on the status bar
     
    
22. Close the form

Database Objects Captions

A caption is text that guides the user to identify a window or another object. As it is the most important property of a label, it may appear as a significant guide to the user. For a table, a query, a form, or a report, the caption is text that displays on the title bar. For a label, the caption is text the user can read on the control. After creating an object such as a table, a query, a form, or a report, Microsoft Access uses its name as its caption.

The captions of tables and queries cannot be changed (easily). Microsoft uses a combination of the name followed by a colon and space, followed by the category of object. For example, if a table is named Addresses, its title bar would display Addresses: Table

The caption of a query depends on the type of query. If you create the types of queries we have used so far, which consisted of selecting fields, these are called Select Queries. The title bar of such a query would use the same technique as the table but the category of object would be Select Query:

If you create a form using the Form Wizard or if you create a report using the Report Wizard, Microsoft Access would use the name you provided as its caption. If you design a brand new form or report by adding fields to it, it would have a caption as Form1 or Report1 followed by the object category as the Form or the Report. To display its caption, Microsoft Access would use the same convention as seen above:

If you create a report or a form using a wizard, after you have saved the object, its name would be used as its caption:

Controls Captions

So far, we have used two techniques to create fields on a table: using the Datasheet View or using the Design View. In Datasheet View, the name you give to a column also becomes its caption. This is one of the limitations of the Datasheet View. Most programming or development environments, including Microsoft Access, separate the name of a field from its caption. The name is used by you and the operating system to identify an object. The caption is most used to guide the user. To help with this, the Design View of a table allows you to make a distinction on these two aspects of a field.

In the Design View of a table, to create a caption for a field, after selecting it in the upper section of the table, in the lower section, click the Caption field and type the desired string. The caption and the name have very little in common, as far as programming is concerned. Although you can provide as caption any string of your choice, you should create it to reflect its corresponding field. For example, you can set the caption of a field named EmployeeNumber as Two Letters Followed by Three Digits as follows:

The caption would display as follows:

Although this works, you should set a (short) caption that indicates what the field is used for. For this example, an appropriate caption could be Empl # or Employee # or Empl. Nbr.

When you select a new field for a query, Microsoft Access applies the same caption that was used in the table for that field. If the field did not receive an explicit caption (when it was created in the table), then, like the table, the query would use the name of the field as caption. You can change the caption of a query’s field to display anything other than the name set in the originating table. To do this, in the box that receives the name of the field, type the desired caption, followed by a colon, followed by the name of the field that will be displayed in the column. Here is an example:

When you run the query, it would display the caption you had set:

To set or change the caption of a form, a report, or a label, access its Properties window. Then, click the Caption field and edit or replace it as necessary. If you delete the Caption of a form or a report, Microsoft Access would use the ObjectName: Category formula to create its caption. 

If you add a new label from the Toolbox to a form or report, the caret would be placed in it so you can create its caption. If you click somewhere else without typing, the label would be cancelled. Therefore, the first action to take when creating a label is to give it a caption, which is done by simply typing the desired string. If the label already exists and you want to edit it, first click it to select it. Then click it again. This would put the label into edit mode. You can then use your knowledge of text editing to change its caption. If you completely delete the caption of a label, the label would be completely deleted.

Practical Learning: Setting Objects Captions

1. Open the Clarksville Ice Cream database you started in Lesson5 and click Tables in the Database window
2. Double-click the Employees table to open it in Datasheet View. Observe the captions on various columns such as DateHired, EmployeeNumber, EmailAddress, etc
3. Switch the table to Design View
4. In the upper section of the table, click EmployeeID
5. In the lower section of the view, click Caption. Type Empl ID and press Enter
6. In the same way, set the captions of fields as follows
    

   Field Name	Caption
   DateHired	Date Hired
   EmployeeNumber	Empl #
   FirstName	First Name
   MiddleInitial	MI
   LastName	Last Name
   WorkPhone	Work Phone
   EmailAddress	Email Address
   ZIPCode	ZIP Code
   MaritalStatus	Married?

7. To save the table, press Ctrl + S and switch the table to Datasheet View
    
8. Notice that the captions on columns are different (make sure you don't type anything in the fields)
9. Open the Rockville Techno database and, in the Database window, click Tables
10. Double-click the Employees tables to open it and, after viewing it, switch it to Design View
11. In the upper section, click EmployeeNumber
12. In the lower section, click Caption. Type Empl # and press Enter
13. In the same way, set the Caption of MiddleName to Middle Name
14. Change the Caption of Extension to Ext
15. Change the Caption of EmailAddress to Email Address
16. Change the Caption of StateOrProvince to State
17. Change the Caption of PostalCode to ZIP Code
18. Save and close the table
19. On the Database window, click the Forms button
20. Open the Employees form in Design View
21. Click State/Province and click it again to put into edit mode. Double-click Province to select it and press Backspace twice
22. Click PostalCode to select the label. Double-click Postal, type ZIP and press the space bar
23. Click the Billing Rate label and, in the Properties window, click the Format tab
24. Click Caption. Type Salary and press enter
25. Switch the form to Form View to see the result
     
26. Save and close the form
27. Double-click the EmployeesOfficeContact form to open it to Form View
28. Notice the caption on the title bar and switch it to Design View
29. Click the button at the intersection of both rulers. In the Properties window, click Caption. Type Rockville Technologies, Inc. – Employees Contact Information and press Enter
30. Preview the form
     
31. Save and close the form
32. In the Formst section of the Database window, right-click the EmployeesOfficeContact form and click Copy
33. Right-click a white area in the Database window and click Paste
34. In the Paste As dialog box, type EmployeesContact in the Form Name and press Enter
35. In the Database window, click the Reports button
36. Double-click the ListOfAssets report to view it and then switch it to Design View
37. Click the button at the intersection of both rulers . In the Format tab of the Properties window, click Caption. Type Rockville Technologies – Company Assets Inventory and press Enter
38. Preview the report and switch it back to Design View
39. On the Toolbox, click Label and, on the report, click anywhere under the Report Header bar
40. Type Company Assets and press Enter
41. Once again, on the Toolbox, click the Label control and, on the report, click under and to the left of the Page Header bar above the AssetType text box
     
42. Type Category
43. In the same section and to the right of the previous label, add another label and set its caption to Make
44. Add another label to the right of the previous label and set its caption to Model
45. Add another label to the right of the previous label and set its caption to
    Date Acquired
46. Add another label to the right of the previous label and set its caption to Price
47. Using the techniques we reviewed for resizing and moving controls, enlarge each label to the same width as its corresponding text box and move it just above its label:
     
48. Save the report

Text Alignment

Most fields or controls of a database display text. This includes labels, text boxes, and combo boxes, etc. To enhance the appearance of the fields or controls, you can specify the alignment of text of a control. This property has three values: left, center, or right.

By default, the alignment of text-based fields, including any caption on a label, is set to the left (following US English). The alignment of fields that display numbers or dates is set to the right. If you don't like these suggestions or for any other reason, you can change the text alignment of a field or control.

There are various ways you can change text alignment. After selecting it and while in Design View, the Formatting toolbar provides the Align Left , the Center , and the Align Right buttons. You can click one of these to apply the desired alignment. Alternatively, while in Design View (in Microsoft Access 2000 and later versions, you can change text alignment of fields, excluding the label because it cannot receive focus, even if the form displays in Form View, as long as you display its Properties window first) you can access the Properties window for the field or the label. Then click the arrow of its Align Text combo box property and select one of the available values: Left, Center, or Right.

Practical Learning: Aligning Fields Text

1. The Rockville Techno database should still be opened and displaying the ListOfAssets report
   On the report, click the Date Acquired label to select it.
2. On the Formatting toolbar, click the Center button
3. To select another control on the report, on Formatting toolbar, click the arrow of the most left combo box and select txtDatePurchased
4. In the Properties window, click the Format tab and click Text Align
5. Click the arrow of the Text Align combo box and select Center
6. On the report, click the Price label and, on the Formatting toolbar, click the Align Right button
7. Save the report

Font

A font is an art effect made of designed symbols used to represent letters and other characters on a field, a control or a label. A font is designed by an artist but usually follows a specific pattern. For example a font designed to produce symbols readable in the US English language must be designed by a set of predetermined and agreed upon symbols. These symbols are grouped in an entity called the English alphabet. When designing such a font, the symbols created must conform to that language. Because a font is primarily an art, one font can be significantly different from another and a font is not necessarily a series of readable symbols.

Just like everything else in the computer, a font must have a name. A font is also designed to assume different sizes. Before using it on a data field or label, the font must have been installed (in reality, when working in Design View (even in any application like Microsoft Word), you can type the name of a font in the Font combo box; if the font doesn't exist, it will simply not be used but you may not receive an error). Microsoft Windows installs many fonts during setup. 

The font used on the Microsoft Access application to display its menu and the names of objects is controlled by the operating system. That font is called the System Font. Unless you have a good reason, you should not be concerned with it.

The names of fonts installed on your computer can be seen in the Fonts window accessible from Control Panel. Here is an example (from a Windows 98 installation):

By default, Microsoft Access uses a font called Arial to display columns and fields on tables, queries, and reports. The default font used on forms is called Tahoma. If you want to use a different font to display text on labels and fields of your database, you can changes these defaults.

To set or apply a font on a label or a field, after selecting it in Design View, you can use the Formatting toolbar which is equipped with the Font combo box. You can click its arrow and select a font from the list. Alternatively, to change a font, access the Font Name property of a label or a field in the Properties window, click the arrow of the property’s combo box and select from the list.

When changing the default font, in other words, if you decide to change the default font, you should use only the most popular fonts that are more likely to be found on your user’s computers. To set or apply a font on a group of labels and/or fields, select the group and either use the Formatting toolbar or, from the Properties window, click the Font Name combo box and select from the list.

Besides its name, a font is also known for its size. The size defines how much height and proportional width would be used to represent the characters of the selected font. To change or set the size of the font used to display the characters of a label or a field, after clicking it or selecting the group of controls, on the Formatting toolbar, you can click the arrow of the Font Size combo box and select a size from the list. You can also click the label or field, access its Properties window and select a size from the Font Size combo box. In both cases, if you want a size that is not in the list of the Font Size combo box, you can simply type the desired natural number and press Enter or Tab. As done for the font names, you can also select a group of labels and/or fields and apply the same size to them.

A font is also characterized by its style. The style controls how the font displays, in normal, bold, italicized, underlined, some of these characteristics or all of them. Here are examples of these characteristics:

To change or set the font style of a label or a field, after selecting it and while in Design View, on the Formatting toolbar, click the Bold , the Italic , the Underline buttons, only one of them, two of them, or all of them. You can also specify the font style using the Properties window. To do this, first select the label and/or fields. When using the Properties window, the bold style is provided in variations (this also is a setting controlled by the operating system) from the Font Weight combo box as Extra Light, Light, Normal, Medium, Semi Bold, Bold, Extra Bold, and Heavy. The italic style is provided from the Font Italic Boolean field. The underline style is specified using the Font Underline Boolean property. As mentioned for the other font characteristics, you can apply one or more styles on a group of labels and/or fields after selecting the group.

Practical Learning: Setting Fonts Characteristics

1. The Rockville Techno database should still be opened and displaying the ListOfAssets report in Design View
   In the Report Header section, click the Company Assets label to select it
2. On the Formatting toolbar, click the arrow of the Font combo box and click Garamond
3. With the label still selected, click the arrow of the Font Size combo box and select 24
4. Still on the Formatting toolbar, click the Bold button
5. To enlarge the selected label, double-click one of its 8 handles
6. Select all labels in the Page Header section
7. In the Properties window, click the Format tab and click Font Name
8. Click the arrow of the Font Name combo box and select Times New Roman
9. Still in the Properties window, click Font Size. From its combo box, select 10
10. From the Font Weight property, select Bold
11. In the Detail section, select all text boxes. Using the Properties window, set their Font Name to Times New Roman and the Font Size to 10
12. Save the report

Fore Color

The color is a non-spatial characteristic applied on a Windows control or a field to define its visual density of darkness or clarity. This density itself is a numeric value created by combining three numbers that each ranges from 0 to 255 included. The first number is also referred to as red. The second is referred to as green. The third is referred to as blue. A combination of red, green, and blue creates a color. Since each number has 256 values, if you multiply them as 256 * 256 * 256, you get 16777216. This means that various combinations of red, green, and blue produce more than 16 million possible colors.

There are two main aspects you will deal with as related to colors on a field or a control. The most usual way of setting a color on a field is on its characters or letters. This is also referred to as the fore color, which is simply the color of its font. There are various ways you can change the font color of a field or control. After selecting it and while in Design View, on the Formatting toolbar, you can click the arrow of the Font/Fore Color button. This would display a table of colors:

After clicking a color, it becomes selected on top of the button. The next time, if you need the same color, you can just click the Font/Fore Color button and the already selected color would be applied. If, during design, you will keep changing colors, you can permanently display the Font/Fore Color as a window on your screen. To do this, click the arrow of the Font/Fore Color button and drag its short title bar:

This would create a window based on it. 

Unlike Microsoft Excel and Microsoft Word, the Font/Fore Color table of Microsoft Access does not display the names of colors. Here are the names of colors we will use on this site as they are laid on the Font/Fore Color table:

Besides the Font/Fore Color button on the Formatting toolbar, to set the font color of a field or label, while in Design View, you can right-click it, position the mouse on Font/Fore Color and click one of the preset colors. Once again, you can use only one of the existing colors.

The Font/Fore Color table presents a good and extended list of colors but it does not allow you to customize colors through the Windows Color dialog box. The most extensive way to apply a color consists of creating it from your own combination of red, green, and blue. To do this, while in Design View, access the Properties window for the field or the label and click its Fore Color property. Then click the ellipsis button. This would open the Color dialog box from where you can either select one of the preset colors or create your own.

To provide the selection of colors on Microsoft Windows applications, the operating system provides a common dialog box appropriate for such tasks. The Color dialog box is used for various reasons to let you set or change a color of an object. When it displays, by default, the Color dialog box appears as follows:

This displays a constant list of colors. If none of the available colors is appropriate for the task at hand, you can click the Define Custom Colors >> button to expand the dialog box:

The expanded Color dialog box allows you to either select one of the preset colors or to custom create a color by specifying its red, green, and blue values.

You can change the color in four different areas:

• The top left section displays a list of 48 predefined colors
• If the desired color is not in that section, you can click and drag the mouse in the multi-colored palette
• You can also drag the right bar that displays a range based on the color of the palette. You can scroll up and down by dragging the arrow
• For more precision, you can type the Red, Green and Blue values in the corresponding text boxes. Each uses a integral value that ranges from 0 to 255

After selecting or specifying a color, you can click OK or press Enter.

Practical Learning: Setting Text Font on Fields

1. The Rockville Techno database should still be opened and displaying the ListOfAssets report in Design View
   In the Report Header section, click the Company Assets label to select it
2. On the Formatting toolbar, click the arrow of the Font/Fore Color button and select Red
3. In the Page Header section, select all labels
4. Right-click any of the selected labels, position the mouse on Font/Fore Color and, in the table of colors, click Indigo (7th column - 1st row)
5. In the Detail section, select all text boxes
6. In the Properties window, click the Format tab and click Fore Color. Then click its ellipsis button
7. In the Color dialog box, click the Blue colors in the 5th column 4th row in the Basic Colors section
    
8. Click OK
9. Preview the report and switch it back to Design View
10. Save the report

Background Color

You can enhance the appearance of an object by change its background color. This aspect can be applied only to the object that is selected. Based on this, you can paint the background of one section of a form or a report without changing the other sections. In the same way, you can paint the bounding rectangle of a field or a label independent of the other fields or labels.

To change the background of an object, first select it. To select a section of a form or a report, you can click the bar above it. After selecting a section, a label or a field, to change its background, on the Formatting toolbar, you can click the arrow of the Fill/Back Color button. You can also right-click the section, the label, the field or a group selected and position the mouse on Fill/Back Color. In both cases, a list of colors similar to that of Font/Fore Color would display. You can then select the desired color from the table. To get a color that is not in the list, access the Back Color property of the section or object and click its ellipsis button. This would display the Color dialog box. You can then use it as we described for the fore color.

If you do not want a label or field to be painted with a particular color, you can make it transparent by setting its Fill/Back Color property to Transparent. Using the Properties window, you can also make a control such as a text box transparent by using the Back Style property. Its default value is Normal. To be able to see through the control, you can set this property to Transparent.

Practical Learning: Setting Background Color

1. The Rockville Techno database should still be opened and displaying the ListOfAssets report in Design View
   On the report, click the Page Header bar to select its section
2. On the Formatting toolbar, click the arrow of the Fill/Back Color button and, from the table of colors, click Pale Blue (6th column - 5th row)
3. Preview the report and switch it back to Design View
4. While the Page Header section is still selected, in the Format tab of the Properties window, click Back Color and click its ellipsis button
5. In the Color dialog box, click the Define Custom Colors >> button. Set the colors to Red=185, Green=220, Blue=255 and click OK
6. Preview the report and save it
7. Close the report

Special Effects

Microsoft Access provides some special visual effects used to raise or sink, etc a section of a form or report, a label or a field. These effects can be controlled by using the Special Effect button on the Formatting toolbar, by right-clicking a label, a field or a group of labels and/or fields, positioning the mouse on Special Effect, and clicking from the list. You can also access the effects from the Special Effect property of the Properties window. The special effects appear as follows:

Practical Learning: Using Special Effects

1. The Rockville Techno database should still be opened
   In the Database window, click the Forms button and double-click the EmployeesOfficeContact form to open it in Form View
2. After viewing the form, switch it to Design View
3. In the Detail section, select all labels (and only the labels)
4. On the Formatting toolbar, click the arrow of the Special Effect button and click Special Effect: Sunken
    
5. Preview the form and switch it back to Design View
6. In the Detail section, select all text boxes
7. In the Format tab of the Properties window, click Special Effect and click the arrow of its combo box. Select Shadowed
8. As we learned when studying how to resize controls, position the mouse on one lower-center handle of one of the selected text boxes and drag it down by one unit to resize the text boxes
    
9. Switch it to Form View and, on the main menu, click Window -> Size To Fit Form
10. Save and close the form

Borders

A border is a line that defines the limit of an object. You can control how the border of a label or a field is drawn. The borders of a label or field can be drawn with a variant width of a line.

To specify the thickness of line applied on the borders of a label, a field, or a group of objects, click the arrow of the Line/Border Width button on the Formatting toolbar and select the desired width from the list.

While the Line\Border Width button on the Formatting toolbar is convenient, it does not provide as many variances as the Properties window. The Border Style is used to control how the borders of a control would be drawn. The possible values are Transparent, Solid, Dashes, Short Dashes, Dots, Sparse Dots, Dash Dot, and Dash Dot Dot. They may appear as follows:

The Border Style characteristic can be used in combination with the Special Effect property. Some of these effects depend on the Special Effect value and may not appear as expected.

The Border Color property can be used to control the color used to draw the border of a label or a field. It is used as we described for the Fore Color property.

The Border Width property is used as the Line/Border Width button of the Formatting toolbar.

Location

When you add or drag a control to a section of a form or report, it assumes a distance from the left border of the section and from the lower border of the bar of that section. These two dimensions are referred to as its location. The location of an object is determined by its Top and its Left properties:

The location of an object can be set by dragging its top-left handle or its body when the object is selected in Design View, as we learned already. Alternatively, the top and the left values are set as decimal numbers and they follow the measures on the rulers. To change the location of an object, after selecting it in Design View, change the values of either or both its Top and its Left properties.

Practical Learning: Setting the Location of Fields

1. The Rockville Techno database should still be opened
   In the Database window, click the Reports button and double-click ListOfAssets
2. After viewing the report, switch it to Design View
3. In the Report Header section, click the Company Assets label
4. In the Format tab of the Properties window, click Top and type 0.417
5. Click Left, type 0.125 and press Enter
6. In the Page Header section, select all labels
7. In the Properties window, click Top. Type 0.04 and click an area in the Report Header
8. Still in the Page Header section, click Category and, in the Properties window, set its Left property to 0.0833
9. In the Detail section, select all text boxes and, using the Properties window, set their common Top property to 0.0417
10. Using the Format tab of the Properties window, set the Left property of the objects on the report as follows:
     

    Label	Text Box	Left
    Make		1.125
    Model		2.1667
    Date Acquired		3.2451
    Price		4.375
    	AssetType	0.0833
    	Make	1.125
    	Model	2.1667
    	DateAcquired	3.2083
    	PurchasePrice	4.25

11. Save the report
12. Preview the report and return it to Design View

Dimensions

The width of an object is the distance from its left border to its right border. The height of an object is the distance from its top border to its bottom border. The width and the height of an object are referred to as its dimensions. The width of a form or report is the common width of each one of its sections. All sections use the same width. The height of a form or report is the total height of all of its sections. For this reason, the form and the report do not have a height because they let each section control this characteristic.

The dimensions of a control positioned on a section of a form or report can be illustrated as follows:

The dimensions of an object can be set by dragging one of its middle handles , , , or , when the object is selected, as we learned already. Alternatively, they can be controlled by its Width and its Height properties. Therefore, to change the dimensions of an object, after selecting it in Design View, change the values of either or both its Width and its Height properties.

Practical Learning: Setting Fields Dimensions

1. The Rockville Techno database should still be opened and displaying the ListOfAssets report in Design View
2. Click the Report Header bar. In the Format tab of the Properties window, click Height
3. Type 1 and press Enter
4. In the Report, click the Page Header bar. In the Properties window, click Height, type .28 and press Enter
5. Click the Detail bar. In the Properties window, click Height, type .25 and press Enter
6. Click the Price label. In the Properties window, click Width. Type .3646 and press Enter
7. Click the PurchasePrice text box. In the Properties window, click Width. Type 0.5833 and press Enter
8. Click the button at the intersection of the rulers of the report . In the Properties window, click Width. Type 5 and press Enter
9. Save the report and preview it
   
10. Close the report
11. In the Database window, click the Forms button and double-click the Employees OfficeContact then switch it to Design View
12. Select all text boxes. In the Properties window, click Height. Type 0.2 and press Enter
13. Preview and save the form
     
14. Close the form

Tab Ordering

When performing data entry, we saw that the user can press Enter or Tab to navigate from one field to another. Because a label cannot receive focus, it is not affected by this navigation. The controls that participate in this navigation are part of a group. Participation to this group is controlled by the Tab Stop Boolean property. In order for a control to receive focus when the user presses Tab, the control must have its Tab Stop property set to true. When you add a Windows control to a form, it is directly made part of that group because its Tab Stop property is set to Yes. If you want to remove a control from this group, you can set its Tab Stop property to No. Of course, an alternative is to set its Enabled property to No.

Once a control’s Tab Stop property is set to Yes, it must receive a number. Each control that is part of the Tab Stop group must have a unique number. Fortunately, Microsoft Access makes sure that this rule is followed. The number must be a natural number whose minimum must be 0 and whose maximum must be the total number of controls that can receive focus – 1. The number is set using the Tab Index property.

When you add controls to the form, Microsoft Access checks whether the control can receive focus. If the control can, its Tab Stop property is set to Yes and it receives an incremental number as its Tab Index. You are allowed to change this number following the rules stated above. If you change the number to a number that already exists, Microsoft would renumber the sequence to make sure a number is not repeated. The best way to deal with this aspect is through the Tab Order dialog box as we saw but if you know how you want to control this property, you can set it as you wish.

AutoFormat

Microsoft Access Design Templates

We saw that, when you create a database using the Database Wizard, you have the option of applying one of the designs provided by Microsoft Access and you can apply it to your forms and reports. In the same way, if you create a form using the Form wizard, as we have done in previous lessons, you can select one of the designs and apply it to the form. This concept also applies to reports.

If you create a form or report in Design View, you would have only the Standard design. You can still apply one of the designs supplied by Microsoft Access. To do that, open the form or report in Design View. Then, on the main menu, click Format -> AutoFormat... The AutoFormat would appear and allow you to select any of the available designs. After selecting a design, you can click OK

Creating a Form Template

If you design a good looking form or a report and want to use its design in other databases, you can add it to the list of designs of the AutoFormat dialog box. To do that, open the form or report in Design View, then, on the main menu, click Format -> AutoFormat... On the AutoFormat dialog box, click Customize. The Customize AutoFormat dialog box allows you to modify or delete one of the existing designs, or create a new one. 

Practical Learning: Using AutoFormat

1. The Rockville Techno database should still be opened.
   In the Forms section of the Database window, right-click the EmployeesContact form and click Design View
2. On the main menu, click Format -> AutoFormat...
3. In the Form AutoFormats list box, click International and click the Options button
    
4. Click OK

MOUS Topics

Exercises

Yugo National Bank

1. Open the Yugo National Bank database.
   Open the Customers form in Design View. Change the caption of the form to 
   Yugo National Bank - Account Setup and close it
2. Open the Customers table in Design View, change the Description property of the following Field Names and their Caption as follows:
    

   Field Name	Description	Caption
   CustomerID	Incremental number generated automatically
   DateCreated	Date the account was created	Date Created
   AccountNumber	Number created by this bank
   AccountTypeID	Type of account, as Checking, Saving, etc
   CustomerName	Name registered on this account. Can be a person or a business	Customer Name
   Address	Where the customer wants to receive statements. PO Box allowed
   ZIPCode		ZIP Code
   LastUpdate	Specifies the last time any information was changed on this account	Last Update

   
   Save and close the table
3. Open the Customers form in Design View and change the captions of the labels as follows:
    

   Change the Status Bar text of the following text boxes:

   Name	Status Bar Text
   DateCreated	Date the account was created
   AccountNumber	Number created by this bank
   CustomerName	Name registered on this account. Can be a person or a business
   Address	Where the customer wants to receive statements. PO Box allowed
   LastUpdate	Specifies the last time any information was changed on this account

   Save and close the form

4. Open the Employees form in Design View. Change the caption of the form to
   Yugo National Bank - Employees Records and change the captions of its labels as follows: 

   
   Save and close the form
5. On the AccountTypes form, since the user cannot change the value of the AccountTypeID text box, lock it, disable it, chisel it, and make it bold
6. On the Customers form, since the user cannot change the value of the CustomerID text box, lock it, disable it, chisel it, and make it bold
7. On the Employees form, since the user cannot change the value of the EmployeeID text box, lock it, disable it, chisel it, and make it bold
8. On the TransactionTypes form, since the user cannot change the value of the TransactionTypeID text box, lock it, disable it, chisel it, and make it bold

Tenley Associates

1. Open the Tenley Associates database.
2. Open the Departments form in Design View and, using the AutoFormat dialog box, apply the Stone design with all options. Since the user cannot change the value of the DepartmentID text box, lock and disable it. Change the font color of the text boxes to Dark Blue. Adjust the design of the form as follows:
    
   
   Save and close the form
3. Redesign the Employees form as follows:
    

   Save and close the form

Watts A Loan

1. Open the Watts A Loan database and open the Customers table in Design View. Change the descriptions and captions of fields as follows:
    

   Field Name	Description	Caption
   CustomerID	Automatic number generated by the database itself
   AccountNumber		Account #
   Address	Where the customer wants statements to be sent
   ZIPCode		ZIP Code

   Save and close the table

2. Use AutoForm to generate a form for the Customers table. Save the form as Customers and design it as follows:
    
   
   Save and close the form
3. Open the Employees table in Design View. Insert or add the following fields:
    

   Field Name	Caption
   EmployeeID	No Change
   FirstName	No Change
   LastName	No Change
   Title	No Change
   WorkPhone	No Change
   Address
   City
   State
   ZIPCode	ZIP Code
   Country
   HomePhone
   Salary
   Notes

   
   Save and close the table

4. Use AutoForm to generate a form for the Employees table. Save the form as Employees and design it as follows:
    
   
   Save and close the form
5. On the Customers form, since the user cannot change the value of the CustomerID text box, lock it, disable it, chisel it, and make it bold
6. On the Employees form, since the user cannot change the value of the EmployeeID text box, lock it, disable it, chisel it, and make it bold