|
Properties of Database Fields |
|
Common Characteristics of Database Fields
Name
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.
Practical Learning: Naming Objects
|
- The Rockville Techno database should still be opened
On the Database window, click the Reports button
- Double-click the ListOfAssets report to open it in Print Preview
- After viewing it, to switch it to Design View, on the Print Preview toolbar, click the View button
- 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
- Since the text box was accompanied by a label, click its label and press Delete
- 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
- In the All tab of the Properties window, click Name, type txtCategory and press Enter
- In the same way, add a new Text Box in the Detail section. Delete its label and change its Name to txtManufacturer
- Add another Text Box control in the Detail section. Delete its label and change its Name to txtModelName
- Add another Text Box control in the Detail section. Delete its label and change its Name to txtDatePurchased
- Add another Text Box control in the Detail section. Delete its label and change its Name to txtUnitPrice
- Arrange the fields as follows:
- 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
|
- 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
- From the list of fields, drag DateAcquired and drop it on PurchasePrice in the lower section of the query
- Close the query. When asked to save it, click Yes and press Enter
- 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
- In the same way, set the Control Source of the second Unbound text box to
Make
- Set the Control Source of the third Unbound text box to Model
- Set the Control Source of the last two Unbound text boxes to
DateAcquired and PurchasePrice
- Save the report and close it
|
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.
|
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
|
- The Rockville Techno database should still be opened
From the Forms section, double-click CompanyAssets
- 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
- To switch the form to Design View, on the Form View toolbar, click the View button
- 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
- Notice that its aspect and its label have changed
- To switch to Form View, on the Design View toolbar, click the View button
- Click the Asset ID text box and notice that, this time, it cannot even receive focus
- Save and close the form
|
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
|
- The Rockville Techno database should still be opened
In the Forms section, right-click CompanyAssets and click Design View
- Click the AssetID text box. In the Properties window, double-click the Locked field to set it to Yes
- Switch the form to Form View. Then save and close the form
|
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
|
- 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
- After viewing the Project form, switch it to Design View
- 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
- Type Client company of this project and press Enter
- 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 |
- Switch the form to Form View and position the mouse for a few sections on Project Manager:
- Do the same on various controls
After viewing the tool tips, save and close the form
|
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
|
- Open the Bethesda Car Rental1 database you created and click Tables
in the Database window
- Right-click the Employees table and click Design View
- Click EmployeeID and press Tab twice
- In the Description field for the EmployeeID field, type
Automatic number set by the database engine
- Press the down arrow key twice. That puts the cursor in the Description field for the
DateHired field. Type Date the employee was hired
- For the description of the EmployeeNumber, type Number set by Management
- In the Description of the FirstName field, type Employee’s
first name
- In the Description of the MI field, type Middle Initial made of one letter
- Complete the descriptions of fields
- Save the table
- To switch to Datasheet view, on the Table Design toolbar, click the View button
- Click a cell under the Date Hired column and observe the sentence on the Status
bar
- Click under EmailAddress and observe the Status bar
- Close the Employees table
- Open the Rockville Techno database
- From the Forms section of the Database window, open the CompanyAssets form and switch it to Design View
- On the form, click the Asset Type text box to select it. In the
Other tab of the Properties window, click Status Bar Text
- Type Category of asset and press Enter
- 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 |
- Save the form and switch it to Form View
- Click different field and observe the message on the status bar
- 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:
|
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
|
- Open the Clarksville Ice Cream database you started in Lesson5 and
click Tables in the Database window
- Double-click the Employees table to open it in Datasheet View. Observe the captions on various columns such as DateHired, EmployeeNumber, EmailAddress, etc
- Switch the table to Design View
- In the upper section of the table, click EmployeeID
- In the lower section of the view, click Caption. Type Empl ID and press Enter
- 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? |
- To save the table, press Ctrl + S and switch the table to Datasheet View
- Notice that the captions on columns are different (make sure you
don't type anything in the fields)
- Open the Rockville Techno database and, in the Database window, click
Tables
- Double-click the Employees tables to open it and, after viewing it, switch it to Design View
- In the upper section, click EmployeeNumber
- In the lower section, click Caption. Type Empl # and press Enter
- In the same way, set the Caption of MiddleName to Middle Name
- Change the Caption of Extension to Ext
- Change the Caption of EmailAddress to Email Address
- Change the Caption of StateOrProvince to State
- Change the Caption of PostalCode to ZIP Code
- Save and close the table
- On the Database window, click the Forms button
- Open the Employees form in Design View
- Click State/Province and click it again to put into edit mode. Double-click Province to select it and press Backspace twice
- Click PostalCode to select the label. Double-click Postal, type ZIP and press the space bar
- Click the Billing Rate label and, in the Properties window, click
the Format tab
- Click Caption. Type Salary and press enter
- Switch the form to Form View to see the result
- Save and close the form
- Double-click the EmployeesOfficeContact form to open it to Form View
- Notice the caption on the title bar and switch it to Design View
- 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
- Preview the form
- Save and close the form
- In the Formst section of the Database window, right-click the EmployeesOfficeContact
form and click Copy
- Right-click a white area in the Database window and click Paste
- In the Paste As dialog box, type EmployeesContact in the Form
Name and press Enter
- In the Database window, click the Reports button
- Double-click the ListOfAssets report to view it and then switch it to Design View
- 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
- Preview the report and switch it back to Design View
- On the Toolbox, click Label and, on the report, click anywhere under the Report Header bar
- Type Company Assets and press Enter
- 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
- Type Category
- In the same section and to the right of the previous label, add another label and set its caption to Make
- Add another label to the right of the previous label and set its caption to Model
- Add another label to the right of the previous label and set its caption to
Date Acquired
- Add another label to the right of the previous label and set its caption to Price
- 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:
- Save the report
|
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
|
- The Rockville Techno database should still be opened and displaying the ListOfAssets report
On the report, click the Date Acquired label to select it.
- On the Formatting toolbar, click the Center button
- To select another control on the report, on Formatting toolbar,
click the arrow of the most left combo box and select txtDatePurchased
- In the Properties window, click the Format tab and click Text
Align
- Click the arrow of the Text Align combo box and select Center
- On the report, click the Price label and, on the Formatting toolbar,
click the Align Right button
- Save the report
|
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:
Style |
Example |
Regular |
Regular Text |
Bold |
This text is bold |
Italic |
Italicized section |
Underline |
The words are underlined |
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
|
- 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
- On the Formatting toolbar, click the arrow of the Font combo box and click
Garamond
- With the label still selected, click the arrow of the Font Size combo box and select
24
- Still on the Formatting toolbar, click the Bold button
- To enlarge the selected label, double-click one of its 8 handles
- Select all labels in the Page Header section
- In the Properties window, click the Format tab and click Font
Name
- Click the arrow of the Font Name combo box and select Times New Roman
- Still in the Properties window, click Font Size. From its combo box, select
10
- From the Font Weight property, select Bold
- 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
- Save the report
|
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:
|
Black |
Brown |
Olive Green |
Dark Green |
Dark Teal |
Dark Blue |
Indigo |
Gray- 80% |
Dark Red |
Orange |
Dark Yellow |
Green |
Teal |
Blue |
Blue-Gray |
Gray-50% |
Red |
Light Orange |
Lime |
Sea Green |
Aqua |
Light Blue |
Violet |
Gray-40% |
Pink |
Gold |
Yellow |
Bright Green |
Turquoise |
Sky Blue |
Plum |
Gray-25% |
Rose |
Tan |
Light Yellow |
Light Green |
Light Turquoise |
Pale Blue |
Lavender |
White |
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
|
- 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
- On the Formatting toolbar, click the arrow of the Font/Fore Color button and select Red
- In the Page Header section, select all labels
- 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)
- In the Detail section, select all text boxes
- In the Properties window, click the Format tab and click Fore Color.
Then click its ellipsis button
- In the Color dialog box, click the Blue colors in the 5th column 4th row in the Basic Colors section
- Click OK
- Preview the report and switch it back to Design View
- Save the report
|
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
|
- 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
- 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)
- Preview the report and switch it back to Design View
- While the Page Header section is still selected, in the Format tab
of the Properties window, click Back Color and click its ellipsis button
- In the Color dialog box, click the Define Custom Colors >> button. Set the colors to Red=185, Green=220, Blue=255 and click OK
- Preview the report and save it
- Close the report
|
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
|
- 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
- After viewing the form, switch it to Design View
- In the Detail section, select all labels (and only the labels)
- On the Formatting toolbar, click the arrow of the Special Effect button and click
Special Effect: Sunken
- Preview the form and switch it back to Design View
- In the Detail section, select all text boxes
- In the Format tab of the Properties window, click Special Effect and click the arrow of its combo box. Select
Shadowed
- 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
- Switch it to Form View and, on the main menu, click Window -> Size To Fit Form
- Save and close the form
|
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.
|
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
|
- The Rockville Techno database should still be opened
In the Database window, click the Reports button and double-click ListOfAssets
- After viewing the report, switch it to Design View
- In the Report Header section, click the Company Assets label
- In the Format tab of the Properties window, click Top and type
0.417
- Click Left, type 0.125 and press Enter
- In the Page Header section, select all labels
- In the Properties window, click Top. Type 0.04 and click an area in the Report Header
- Still in the Page Header section, click Category and, in the Properties window, set its
Left property to 0.0833
- In the Detail section, select all text boxes and, using the Properties window, set their common
Top property to 0.0417
- 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 |
- Save the report
- Preview the report and return it to Design View
|
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
|
- The Rockville Techno database should still be opened and displaying the ListOfAssets report in Design View
- Click the Report Header bar. In the Format tab of the Properties window, click
Height
- Type 1 and press Enter
- In the Report, click the Page Header bar. In the Properties window, click
Height, type .28 and press Enter
- Click the Detail bar. In the Properties window, click Height, type
.25 and press Enter
- Click the Price label. In the Properties window, click Width. Type
.3646 and press Enter
- Click the PurchasePrice text box. In the Properties window, click
Width. Type 0.5833 and press Enter
- Click the button at the intersection of the rulers of the report . In the Properties window, click
Width. Type 5 and press Enter
- Save the report and preview it
- Close the report
- In the Database window, click the Forms button and double-click the Employees OfficeContact then switch it to Design View
- Select all text boxes. In the Properties window, click Height. Type 0.2 and press Enter
- Preview and save the form
- Close the form
|
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.
|
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 |
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
|
- 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
- On the main menu, click Format -> AutoFormat...
- In the Form AutoFormats list box, click International and click the
Options button
- Click OK
|
S22 |
Switch between object views |
S37 |
Move and resize a control |
S38 |
Modify Format Properties (font, style, font size, color, caption, etc.) |
S39 |
Use the Control Toolbox to add controls |
Yugo National Bank
|
- 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
- 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
- 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
-
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
- 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
-
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
-
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
-
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
|
- Open the Tenley Associates database.
- 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
- Redesign the Employees form as follows:
Save and close the form
|
Watts A Loan
|
- 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
- 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
- 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
- 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
- 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
- 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
|