The Characteristics of a Table
In our introductions to Windows controls, we learned how to position some objects on a form or a report and how to specify their sizes. These two aspects, the location and the size, are referred to as characteristics of an object. In computer programming or in application development, a property is a piece of information that describes an object.
Therefore, a property can be the location (position) of an object, its size, its color, its text, or anything that characterizes it. Some properties of an object are visible and can be set by you the database developer. Some properties can be set or changed by the user when interacting with the computer (but you should be able to predict or control what the user can or should do). Some other properties can be changed by either you or the user.
As seen in the previous lessons, there are various ways you can change the properties of a control but the table, the form, or the report must be displaying in Design View. The properties of an object can be accessed from the Properties window. The table on one hand has a different way of dealing with properties than the form or report on the other hand.
In order to change a property, first open the table, the form, or the report in Design View, click the object whose property you want to change. In the Properties window, locate the property you want to change and click it. Each field in the Properties window is divided in two sections: its name and its value:
The left column of the a tab in the Properties window
displays the name of a property. Although you can click it to select it, you
cannot change it. The property name can be made of one word such as Width.
It can also be made of a combination of words, such as Border Style.
Regardless, in our lessons, each property will be called by what displays on
that left column. This means that, if a property displays "Width",
we will call it "The Width Property". If it displays "Allow
PivotTable View", we will call it "The Allow PivotTable View
String Properties: Some values of properties can be made of one or more characters or words. Here is an example:
To change the value of a string property:
Numeric Properties: A property is called numeric
if it must hold an integral or decimal value. An integer is a natural number
that does not take a decimal portion. Such a number can be made of digits
only. For such a field, make sure you provide an integer of appropriate
range, as you will be directed to do. A decimal number, also called a
floating-point number, can be made of digits or a combination of digits and
one period (or the symbol used as the decimal separator in your language;
you can find this out in the Regional Settings of the Control Panel) in
between. When setting such a value, make sure that either you type only
digits, or you type digits and one decimal separator. The decimal separator
can be anywhere in the value, Microsoft Access would take care of formatting
it if it judges it necessary.
Some numeric properties, such as the color properties, allow you to either type a number (provided you know what number you want to use) or to use an intermediary approach (namely a dialog box) to select an appropriate value.
Expressions Properties: Some properties are made of a combination of specific characters and digits. Examples are the format of a date or time, the concatenation of strings to produce another string. To specify the expression, you can use the same approach we described for a string. After entering the expression, Microsoft Access would analyze it. If you respect the rules of the type of expression you are supposed to create, it would be used. If you enter a wrong expression or Microsoft Access cannot identify what the expression would produce, you may get either an error or an unpredictable result.
Boolean Properties: A property is referred to as Boolean if it can have only either a Yes or a No value, an On or an Off value, a 0 or no 0 value. Both values of the property are stored in a combo box. To change the value of Boolean property:
Enumerated Properties: Some properties provide a list of options as the possible values of the property. The list, which cannot be changed, comes as a combo box from where you can select one item. To change the value of an enumerated property:
When we studied the creation of tables in Lesson 2, we saw how to create columns in the Datasheet View. Like the form or the report, the table can be presented in Design View. In fact, as you will see from now on, that's the best place to create the columns of a table. Instead of the Datasheet View, you can start a new table in Design View or you can modify an existing table in the Design View:
Mostly you, the database developer, have access to the Design View of a table. The user will hardly, if ever, use that view.
A table in Design View is divided in two sections: one in the upper area and another in the bottom:
The top area is made of columns and rows. The columns are named Field Name, Data Type, and Description. When necessary, you will be directed to type or select something in one of the cells; the name of the column under which you must type or select will be specified in the lessons.
The lower portion of the window is made of two sections. The left section is made of two tabs labeled General and Lookup. Each tab contains two columns and various rows. The number of rows and the contents of cells depend on what is selected in the upper section. In our instructions, you will be directed when to do something in a tab or in a cell. As mentioned for the Properties window, the left column of the tabs in the lower section lists the names of characteristics. You cannot change the name of a property. The right column contains the values of the characteristics. When prompted to so so, you will create a new value, modify an existing value, or select one from a combo box. In some cases, a wizard will assist you with creating an expression as the value of a property.
The right side of the bottom section of the table is made of a read-only area that describes what is selected in one of the tabs of the lower-left section.
When given an instruction, you will be dealing with items in the top or the bottom section. To work on an item, sometimes you will be asked to click it to give it focus. You can then click the desired item. Another technique you can use to move from one section to the other is to press F6.
In Lessons 2 and 3, we had various introductions on how to create tables, forms, and reports. We also learned how to name them. We learned how to populate tables with columns and how to add fields to forms or reports with Windows controls but we did not go into their details as they relate to the operation system. One of the rules to observe in application or database development is that every object must have a name.
In our introduction to tables, we saw how to create a column in the Datasheet View. In the Design View:
An object such as a column is referred to as visible if it can visually be located on the screen. A user can use a column only if it is visible. When reviewing tables, we saw that, to hide a column, you could right-click it and click Hide Column. To hide a group of columns, you can right-click any column and click Unhide Column... Then, in the Unhide Column dialog box, clear the check box of any column you want to hide.
If you hide a column and save the table, the column is still available although it is not visible at a particular time. Also, if you hide a column, it has no influence on the form or report that is based on the table. This means that a column can be hidden from a table but its corresponding control on a form or report would still show it. In fact, if you display the Field List of a form or report that has the Record Source specified, all of the columns of the table would display in the Field List, even if they are hidden on the table.
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 field when designing 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 in 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.
When we studied tables creation, we saw how to create columns and we saw how to set the string on the header portion of a column. Here is an example of a column that displays Order Date on its header:
On a column of a table, a caption is the string that the user sees on the column header. In this example, the caption of the column is Order Date. The caption that a column displays is not necessarily the name of the column.
If you create a column of a table in the Datasheet View, the string you specify for the column will represent both its name and its caption. In some cases, you will need or want to display a multi-word string as the caption of a column. This is because the caption should be as explicit and simple as possible. For example, it is better for the user to read Date of Birth than DateOfBirth, or Full Name instead of FullName.
We already saw how to specify the name of a field in the Design View of a table:
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. 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. 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:
Microsoft Access uses some default fonts to display columns and fields on tables, forms, and reports. If you want to use a different font to display text on labels and fields of your database, you can changes these defaults. 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.
Most of the time, you will not need to let your users access your tables. Still, if you do, you can make it friendlier with colors and fonts. When designing a table, Microsoft Office Access 2007 applies a default font (named Calibri) to the headers of the columns. If you want, you can change it. To assist you with setting the fonts of columns, the ribbon is equipped with a Font section in the Home tab.
To change the font of columns and records of a table, display the table in Datasheet View. On the ribbon, click Home. In the Font section, click the Font combo box and select the desired font. The font you select will be applied to all columns and all values (the records) of the table. The font you apply to a table does not have any influence on the form(s) or report(s) that are, or will be, based on that table.
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 specify the font size of the columns, open the table in Datasheet View. On the ribbon, click Home. In the Font section, click the arrow of the Font Size combo box and select the desired size. The font size you apply to a table does not have any influence on the controls on the form(s) or report(s) that are, or will be, based on that table.
When a font is installed, a set of font sizes is created in the Font Size combo box. You can use those numbers but you can also set a new one. To do this, instead of selecting a value in the Font Size combo box, type the desired number and press Enter or Tab. The operating system would calculate the size and apply it.
Another aspect of the appearance of a font is its style, which a technique of drawing the characters of the text, depending on the font. This characteristic comes in four options:
The font style applies only to a control that can display text. When you add a text-based control to a form or a report, by default, Microsoft Access applies the Regular font style to it. you can change this characteristic if you want. To change the font style of a control, switch the form or report to Design View. Select the control or the group of controls. On the ribbon, click either Home or Design. In the Font section, click the button that represents the desired style: Bold , Italic , or Underline .
You can specify more than one style on a control. To do this, click the button of the desired style. When a style is valid for a control, when you click that control, the style button is highlighted: , , or . To remove a style, click the undesired button. To add a style to another style, simply click the desired button. Based on this, you can one, two or three buttons highlighted in the combination of your choice.
Instead of the buttons on the ribbon, you can apply a style or a combination of styles using the Properties window. To do this, access the Properties window for the control. If you want to apply the style to many controls, select them and access the Properties window for the group of selected controls. In the Properties window, click either the Format or the All tab:
As reviewed for the style buttons on the ribbon, you can apply various styles using the Properties window. To do this, access the Properties window for the control or the group of controls and use the Font Weight, the Font Italic, or the Font Underline properties to set the styles as desired.
The color is a non-spatial characteristic applied on a Windows control or a field to define its visual density of darkness or whiteness. This density itself is a numeric value created by combining three numbers that each ranges from 0 to 255 included. The first number is 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 possible values (0 to 255), if you multiply them as 256 * 256 * 256, you get 16777216. This means that a combination of variances of red, green, and blue produces more than 16 millions of 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 on a table. When displaying a table in Datasheet View, on the ribbon, you can click Home. In the Home section, click the arrow of the Font Color button. This would display a table of colors:
By default, this window displays 2 sections of colors. The top section is labeled Access Theme Colors. The main section is titled Standard Colors. This is where you will mostly select common colors. This section is made of 10 columns and 7 rows. The intersection of a column and a row shows a colored button. Each color has a name. To see the name of a color, position the mouse on its button and a tool tip would appear.
When the list of colors displays, if you see a color you like, you can click its button. If none of the displayed colors suits your need, you can click the More Colors... button. This would display another dialog box that also allows you either to select a color or to "compose" one by combining the variances of red, green, and blue.
After a color has been selected, it displays on top of the Font Color button. Next time, if you need the same color, you can just click the Font Color button and the already selected color would be applied. In the same way, you can keep changing colors as you see fit. Whenever a color has been used in your database, it is added to the section under Recent Colors. This allows you to keep track of the colors on your objects and to be able to re-apply an already used color.
A table in Microsoft Access appears as a classic spreadsheet with a white background and gray grid lines. If this display is not appealing, you can change it. To do this, display the table in Datasheet View. On the ribbon, you can click Home. In the Font section, click the Gridlines button . A window would appear where you can select an option by clicking its button. If you click:
The tables in Microsoft Office Access 2007 display their records with alternate colors. This gives them an attractive appeal. If you do not like these default colors, you can change them.
To change one of the background colors of a table, first display it in Datasheet View and click the Home button on the ribbon. In the Font section, you can click the arrow of the Fill/Back Color button. This would display a window of colors that has the same functionality as the one for the Font Color button.
To change the alternate background color of a table, while it is displaying in Datasheet View, click the arrow of the Alternate Fill/Back Color button. A window of color similar to the one we reviewed for the Font Color would appear. Its functionality is also the same. You can then select or compose the desired color.
Most values of a database are made of regular text. Some others are meant to display numeric values, currency, dates, and times. In some cases, Microsoft Access is configured to align the values to the left or the right. In some other cases, you may want to force or customize this alignment of text. On a table, you can align the values of individual columns to the left, the center, or the right.
By default, the alignment of text-based fields, including any caption on a label, is set to the left (following US English). To apply an alignment on a table, first display it in the Datasheet View. To specify the alignment of the values of a column, click any cell under that column. On the ribbon, click Home. In the Font section, you can click:
So far, to get acquainted with database fields, we have used two techniques of creating a table. One of the techniques we used, the Datasheet View, provided a very simplistic way. The Datasheet View is mainly used to perform data entry or to test some fields. The other technique we used, with the Table Templates, allowed us to use some already created and customized fields. For one thing, you would not know what exactly those fields are configured to do and whether they can control data entered in them. For another thing, if you do not like the way they behave, you would have to modify the configuration that was already done. This is why you should know how to design your tables.
To better control the columns of a table, we also learned to create the fields in the Design View of a table. After the columns have been created, you can perform some maintenance operations on them, such as deleting or moving them.
One of the jobs involved with database maintenance is to review fields and make sure they are explicit enough for the user. There are three issues related with a field's name. Some fields, as you will find out are involved in relationships, and you will have to be careful when deciding to change their name. The other issues are related to a field's actual name and its caption.
A field's name is mainly made of two parts from the developer's standpoint: the actual name and the caption. A field's name is stored as part of the table and is involved with any business dealing the table has to cope with. The caption simply guides the user in identifying the use of a particular field. These two items are not strictly related. For example, you could have a field whose name is SocSecNbr while its caption displays Nat Number From Gvt and this will not affect the functionality of the table. Overall, as a developer, you should pay attention to your fields names because these are the names you will refer to when performing calculations or when creating expressions.
To rename a field in Datasheet View, use any of the techniques we learned to change a column's name. After you have typed the name, the new name will replace the old one. To rename a field in Design View, click it and type the new name.
In a table's Design View, you can insert a new field at any position. You can also add a new field to the end of the table:
In the Design View of the table, there are various types of operations you can perform for database maintenance. Operations include naming or renaming fields, moving or deleting fields, and changing various other properties. In order to perform some of the operations on a field, you must first select it. If the desired operation involves a group of fields, you must first select them:
The columns of a table are displayed in the sequence in which they were created. In Lesson 2, we learned how to move columns and change their sequence in Datasheet View. You can also change the sequence of fields in the Design View of a table.
When in Design View, you can delete a field you don't need anymore or if it was added by mistake. To delete a column:
To delete a group of columns, first select them, then:
|Previous||Copyright © 2008-2012 FunctionX||Next|