If you know when you create a form that you are going to add a subform, you can do the whole job with the Form Wizard, like this:
To create the form in your database, on the Objects bar, click Forms, and then click the New button on the database window’s toolbar.
Click Form Wizard, select the form’s base table from the list at the bottom of the page, and then click OK.
Verify that the table you selected is shown in the Table/Queries list, and then click the >> button to include all the fields in the new form.
To create the subform, display the Tables/Queries list, and click the name of the subform’s base table.
Double-click the desired fields to add them to the list of selected fields, and then click Next.
Accept the default options, and click Next.
7
Accept the default Datasheet option, and click Next.8
Click Finish to create the form and subform.You can then clean up the form to suit your needs, just as you did in the previous exercise.
Key Points
■ A form is an organized and formatted view of some or all of the fields from one or more tables or queries. Forms work interactively with the tables in a database. You use controls in the form to enter new information, to edit or remove existing information, or to locate information.
■ When you know what table to base your form on, and have an idea of how the form will be used, you can use the Form Wizard to quickly create a form. You can make modifications to the form in Design view.
■ The two most common views to use in forms are Form view, in which you view or enter data, and Design view, in which you add controls, change form properties, and change the form layout.
■ In Design view, you can resize any of the three basic sections of a form: the Form Header, Detail, and Form Footer. You can customize any section of your form’s layout by adding and deleting labels, moving labels and text controls, and adding logos and other graphics. The most popular controls are stored in the Toolbox.
■ The objects in your form can recognize and respond to events, which are essen tially actions. But without a macro or VBA procedure attached to it, an event doesn’t actually do anything. Knowing how to handle events can greatly increase the efficiency of objects, such as forms. For example, as you enter the first and last names of a new customer, your form could respond to one (or more) events to create an ID based on the customer’s first and last name.
■ The quickest way to create a form that includes all the fields from one table is to use an AutoForm, which can easily be customized later in Design view. ■ If you want to display fields from several tables or queries in one form, you have
to give some thought to the relationships that must exist between those objects. In Access, a relationship is an association between common fields in two tables, and you can relate the information in one table to the information in another table. There are three types of relationships that Access recognizes: one-to-one, one-to-many, and many-to-many.
■ After you define a relationship between tables, you can add subforms to your forms. For example, for each category displayed in your main form, you might have a subform that displays all the products in that category.
Perform calculations in a query, page 140 Create a query in Design view,
page 129
page 118
Filter information in a table, page 121
Create a query with a wizard, page 138
Locate information that matches multiple criteria, page 126 Filter by form, page 123
5
Locating Specific
Information
In this chapter you will learn to:Ž ✔ Sort information.
✔ Filter information in a table. ✔ Filter by form.
✔ Locate information that matches multiple criteria. ✔ Create a query in Design view.
✔ Create a query with a Wizard. ✔ Perform calculations in a query.
A database is a repository for information. It might hold a few records in one table or thousands of records in many related tables. No matter how much information is stored in a database, it is useful only if you can locate the information you need when you need it. In a small database you can find information simply by scrolling through a table until you spot what you are looking for. But as a database grows in size and complexity, locating specific information becomes more difficult.
Microsoft Office Access 2003 provides a variety of tools you can use to organize the display of information in a database and to locate specific items of information. Using these tools, you can focus on just part of the information by quickly sorting a table based on any field (or combination of fields), or you can filter the table so that infor mation containing some combination of characters is displayed (or excluded from the display). With a little more effort, you can create queries to display specific fields from specific records from one or more tables. You can even save these queries so that you can use them over and over again, as the information in the database changes. A query can do more than simply return a list of records from a table. You can use functions in a query that perform calculations on the information in the table to produce the sum, average, count, and other mathematical values.
Microsoft
Office
Specialis
Working with the GardenCo database, in this chapter you will learn how to pinpoint precisely the information you need in a database using sorting and filtering tools, and queries. Note that you cannot continue with the database from the last chapter; you must use the practice files on the companion CD-ROM.
See Also Do you need only a quick refresher on the topics in this chapter? See the Quick Reference entries on pages xxxv–xxxvii.