Read an Excerpt
Lesson 2: Using Tables and Subforms
Behind every good form there stands a good table. This paraphrased adage is probably not applicable in all situations, but it is true of any well-constructed database. A table is where your data is stored, and a form is the most common vehicle for viewing and changing that data. The View Customers form that you used in Lesson 1, for example, is a convenient way of viewing and manipulating data in the underlying Customers table, the table that the View Customers form is based on. There are situations, however, where working with data directly in a table is the better option.
A typical Microsoft Access 2000 database contains a number of tables. The rows and columns of each table house the data for one topic; the Bonbons table, for example, contains the specifics about each bonbon in the Sweet Lil's product line. Each row of the table is a separate record, and each column of the table is a field in the records. In each record of the Bonbons table, there are eight fields: Bonbon Name, Bonbon ID, Bonbon Description, Chocolate Type, Filling Type,Nut Type, Bonbon Cost, and Picture.
To generate interest in Sweet Lil's new Bonbon Baskets line, Impact Public Relations has set up a promotional campaign in which sample baskets of bonbons are mailed to Sweet Lil's customers four times during the next calendar year.
In this lesson, you make use of tables, forms, and subforms to present information about the different sample baskets to the marketing department at Sweet Lil's. In the process, you open an existing database table and arrange the table to make it easier to use. You also use a main form and subform to examinerecords from multiple tables at one time.
Start Microsoft Access 2000 and reopen the database
If Access 2000 isn't started yet, start it. Open the Sweet Lil's database. If the Microsoft Access window doesn't fill your screen, maximize the window.
If you need help opening the database, see Lesson 1, "Using Forms."
Viewing Data
In Access 2000, data in a table, form, report, or query can be presented in several ways, called views.
For a table or a form, Datasheet view displays all the data in a tabular format, each row a record and each column a field. When you open a table, it always appears first in Datasheet view, because Datasheet view is the default view of a table. Both tables and forms in Datasheet view are usually called datasheets.
If a form has already been created based on a table, Form view for either the table or the form displays the table data within the associated form. Form view presents only the fields required for a given task, arranged to show each individual record to its best advantage. (When you open a form, it always appears first in Form view, which is the default view of a form,)
In Table Design view the table is displayed as a list of fields and their associated properties, which can be changed as well as viewed. Form Design view lets you create a new form or revise the structure of an existing one. Design view is never a default view. Instead, you must switch to Design view from another view.
To switch between Design view and the default view, just click the View toolbar button. When you change views, the View toolbar button faceswitches to show the alternate view. With the View button yo alsoswitch almost as easily to another view: click the drop-down arrow nextto the View button, and then click the view you want from the list.
Form view is generally the easiest and safest view to use, because it has the most safeguards and fewest complications. There are times, however, when you need to see multiple records at the same time or view a table for which no form has been developed. Datasheet view lets you see the database as a table and work directly with the data it contains.
Viewing a Datasheet
Most of the tables in the Sweet Lil's database can be viewed through forms. For example, you never actually need to show the Sweet Lil's marketing department the entire Bonbons table when you demonstrate how to alter bonbon descriptions; instead, you work in the Bonbons form, modifying fields that show information about individual records in the underlying Bonbons table.A database table works much like a spreadsheet, so a table viewed in this row-and-column format is often referred to as a datasheet-a database spreadsheet. The datasheet displays every record and every field in a table. In large tables,some columns might contain a lot of text, and there are probably many fields, so you might not be able to view all the table data at once. There are therefore several strategies available for making large datasheets easier to work with.
In these exercises, you open the Bonbons table to become more familiar with the Sweet Lil's product line. You rearrange the fields for optimum viewing of fields, using the record and field selectors.
Open a table
- 1. In the Database window, click Tables on the Objects bar to open the tables list. A list of table is displayed in the Database window.
2. In the tables list, double-click Bonbons.The Bonbons table opens in Datasheet view.
When you first open a table, it is displayed with each row as a record and each column as a field. A triangular pointer appears to the left of the first record, indicating that it is the current record. The first field of the first record is selected, indicating that it is the current field.
Selecting Records Within a Table
The record indicator shows where you are in the table. You can click a record selector to select an entire row of fields, which is by definition a single record. You can click a field selector to select an entire column. You can also select several field or record selectors at once to make a change to multi le columns or rows. Only adjacent fields or rows can be selected.
Change the row height of a table
With the Bonbons table open in Datasheet view, only a partial view of the Bonbon Description field appears. Right now there's just enough height to see one line per row, which is fine for most fields, but not for the descriptions of bonbon in the Bonbon Description field. By increasing the row height, you're able to view all lines of the Bonbon Description field at once.
- 1. Verify that the Microsoft Access window and the Bonbons datasheet are maximized.
The datasheet for the Bonbons table expands to fill the Microsoft Access window.
2. Position the mouse pointer on the border between any two record selectors.
The mouse pointer becomes a double-headed arrow.
3. Drag the border down to make the row immediately above it grow taller.
All rows are resized.
4. Scroll to the right, adjust the row height approximately one inch (1 ") until you can read the entire text of the Bonbon Description field.
Change the column width of a table
Now you go on to make the Nut Type and Bonbon ID columns narrower so that you can see additional table fields.
- 1. Position the mouse pointer on the border between the field selectors for the Nut Type and Filling Type fields.
The mouse pointer becomes a double-headed arrow.
2. Double-click the border between the Nut Type and Filling Type field selectors.
The Nut Type column is resized to match the width of the widest entry in the field, including the complete field name at the top of the column.
3. Drag the right border of the Bonbon ID column to the left until it's just slightly larger than the three-character Bonbon ID entries.
4. On the Datasheet View toolbar, click the Save button. The Bonbons table will now retain its current layout in Datasheet view.
5. Close the Bonbons table.
Records are automatically saved as you move from record to record, but youmust manually save your changes to the layout of a table or form so that itappears this way every time you use it....