Chapter 2: Using Forms to Work with Data

In the previous chapter, we got information from the client about the business processes to be handled in the application, and set up tables to hold the application’s data. The next step is to create forms for entering and editing data, and queries for filtering and sorting. You may need to create queries as form data sources or combobox row sources, so there will be some discussion of queries in this chapter, but the main focus in this chapter is on forms. Chapter 4, Sorting and Filtering Data with Queries, covers queries in more detail. First I’ll briefly discuss the types of forms you can create with Access and their suitability for different purposes, and then we’ll create forms for the Toy Workshop application, using my Design Schemes add-in to give them a consistent and attractive appearance.

Form Types

As far as database objects are concerned there is only one type of Access form, but Access forms have always had three views available (Single Form, Continuous Forms, and Datasheet)—with PivotTable and PivotChart views being added in Access 2002. The first three Form views are commonly referred to as form types, and they will be discussed in this chapter (see Chapter 5, Using PivotTables and PivotCharts to Interact with Data, for information on the PivotTable and PivotChart views of forms).

When you create an Access form, the Single Form view is selected by default. Often this is the most appropriate view, but the table below describes the other views and their uses.

Form View	Record Display	Comments
Single Form	Displays data one record at a time.	Can use all control types; has header and footer areas. Best when you need to display a full page of data per record. Suitable for use as either a main form or a subform. Can be nested up to 7 levels.
Continuous Forms	Displays several data records at once (usually 3 or 4).	Can use all control types except subforms; has header and footer areas. Best when you need to display less than a full page, but more than a single line of data per record, and/or you need to use command buttons or option groups. Can’t be a main form with its own subforms, but can be used as a subform on a Single Form–type main form.
Datasheet	Displays records in a datasheet listformat (one record per row)	Can’t use command buttons, boxes, option groups, or subforms; has no header or footer areas. Best when you need only textboxes and comboboxes, and just one row of data per record. Can’t be a main form with its own subforms, but can be used as a subform on a Single Form–type main form. Because of the lack of header and footer areas, datasheet forms are most suitable for use as subforms.

In addition to these three Form views, forms can also be divided into several categories depending on how they are used:

Form Type	Description	Usage
Standard	Single form, with header and footer.	Forms for reviewing, editing, and entering data.
Data entry	Single form, with footer only.	Forms for entering new data records only.
Datasheet	Datasheet form, to be used as a subform.	Datasheet display of linked data. Typically, the “many” side of the relationship is displayed in the datasheet subform, and the “one” side in the main form.
Dialog	Single form, without either header or footer.	A small form for user response or selection from an option group, combobox or listbox; the choices are used for filtering elsewhere in the database. May be bound to tblInfo to preserve the user’s choices, or data may be written to global variables.
Subform	Single Form–type form designed to be used as a subform on another form. No header or footer.	Use when a Single Form–type subform is needed on another form.
Continuous	Continuous Forms–type form, designed for use either as a standalone form, or a subform on a Single Form–type form. No header or footer.	Use when you need to display one or a few lines of data per record, and may need controls other than textboxes and comboboxes.

Apart from the Form view indicated in the preceding table, each of these form types needs different settings for a number of Format and Data properties. While you could create a default form and then change its view and five or six properties, every time you create a form designed for (say) use as a datasheet subform, it would save a lot of time to be able to select your form type from a list, and have all its properties set correctly at the time it is created. This is one of the purposes of my Design Schemes add-in, along with giving you a choice of several conservative color schemes for forms (with an option for creating your own custom color scheme).

Let’s start by looking at the Relationships diagram for the Toy Workshop, which is shown in Figure 2.1.

Figure 2.1

Let’s analyze the tables in the database, and decide what kind of forms they need, before creating them with the Design Schemes add-in.

The diagram shows all the relationships in the database; all but one are one-to-many relationships. Some of the one-to-many relationships are suitable for display in a main form with one or more subforms (for example, a main form bound to tblCustomers with several subforms bound to tblCustomerPhones, tblCustomerEMails, and tblShippingAddresses). The primary table (tblCustomers) contains data about customers, one record per customer, while the linked tables have (at least potentially) multiple records per customer. There is a similar relationship between tblVendors (the primary table) and tblVendorPhones and tblVendorEMails (linked tables).

The relationship between tblCategories and tblToys, however, is not suitable for a main form with a subform; instead, tblCategories should be the row source of a combobox or listbox on a form where you need to select a category (the main Toys form, and possibly other forms). The relationship between tblShippingMethods and tblOrders is similar; the shipping methods are displayed for selection in a combobox or option group on the Orders form.

The single one-to-one relationship (tblEmployees—tblEmployeesConfidential) isn’t suitable for either a main form with subform or a combobox; instead, these two tables should be displayed in separate forms, to preserve confidentiality of the SSN and salary data.

Some of the relationships between tables in the database require that data from one table be displayed in several forms: For example, tblVendors needs a form for entering and editing vendor data, but it is also the row source of a combobox on several forms. As described in the previous chapter, there are two many-to-many relationships in the database, tblToys—tblToyMaterials—tblMaterials and tblMaterials—tblRepairMaterials—tblRepairs. The linking (middle) table in each of these relationships is displayed only in a datasheet subform on one of the primary forms, for selection of a record from the other primary form (this will become clearer when the form is set up near the end of this chapter).

Some things to consider:

• Each foreign key needs a combobox where the value for this field can be selected from another table (in the Relationships diagram, the foreign key fields are the ones ending with ID other than the bolded primary key fields). This preserves referential integrity—you can only select values that have already been entered into the combobox’s row source table.

• A one-to-many relationship is generally best displayed by selecting the primary form as the record source of a main form, and the linked tables as the record sources of subforms embedded on the main form.

• Lookup tables should be used as the row source of a combobox or listbox control, for selecting values in other tables.

• As a reference when creating your forms, you can print out the Relationships diagram by selecting File|Print Relationships with the diagram open. This creates an Access report in portrait mode; you can manually change the orientation to landscape so as not to cut off portions of the diagram.

The table below lists the type of form display needed for each table in the Toy Workshop database.

Table Name	Display in Form(s)	Display in Control(s)
tblCategories	Continuous Forms–type form for entering and editing data	Category selection combobox on frmToys
tblCustomerEMails	Datasheet subform for use on frmCustomers	Subform control on frmCustomers
tblCustomerPhones	Datasheet subform for use on frmCustomers	Subform control on frmCustomers
tblCustomers	Single Form–type form for entering and editing data	Customer selection combobox on frmOrders
tblEmployees	Single Form–type form for entering and editing data	Employee selection combobox on frmOrders and frmRepairs, and Supervisor selection combobox on frmEmployees
tblEmployeesConfidential	Single Form–type form for entering and editing data
tblMailingList	Single Form–type form for entering and editing data
tblMailingListCompanies	Single Form–type form for entering and editing data; also a form intended for use as a subform on frmMailingList	Subform control on frmMailingList
tblMaterials	Single Form–type form for entering and editing data
tblOrders	Single Form–type form for entering and editing data
tblRepairMaterials	Datasheet form intended for use as a subform on frmRepairs	Subform control on frmRepairs
tblRepairs	Single Form–type form for entering and editing data
tblShippingAddresses	Continuous Forms–type form comintended for use as a subform on frmCustomers and frmOrders	Shipping address selection bobox on frmOrders
tblShippingMethods	Continuous Forms–type form for entering and editing data	Shipping method selection option group on frmOrders
tblToyMaterials	Datasheet form intended for use as a subform on frmToys	Subform control on frmToys
tblToys	Single Form–type form for entering and editing data	Toy selection combobox on frmOrders and frmRepairs
tblVendorEMails	Datasheet subform for use on frmVendors	Subform control on frmVendors
tblVendorPhones	Datasheet subform for use on frmVendors	Subform control on frmVendors
tblVendors	Single Form–type form for entering and editing data	Vendor selection combobox on frmMaterials and frmToys