Linking Forms to Data

Access has always let you bind forms to data simply and easily. This is one major reason why it is a rapid application development environment.

Using the AutoForm Wizard

To bind a form to data, you can use the AutoForm wizard. Select a table or query in the database window and click the New Object: AutoForm button on the Database toolbar. The wizard opens a new form that binds directly to the selected data source. Figure 5-5 below shows a sample form based on the Order Details table in this chapter's sample database. You could use this form for browsing, editing, adding, and deleting records in the Order Details table.

Figure 5-5. A form created by the AutoForm wizard.

Because the data source for the form's Product field is a lookup field in the underlying Order Details table, the Product field automatically appears as a combo box that displays product names instead of the underlying ProductID values. All of this functionality was provided automatically by the wizard.

Conditional Formatting

Access 2000 lets you conditionally format the data displayed by a text box or combo box control without programming. You can selectively apply formatting to form controls for both bound and calculated fields.

Figure 5-6 shows three instances of the same form. I used conditional formatting to control the appearance of the Discount and Extended Price fields. The Discount field in the top form is disabled. The middle form highlights the value in the Extended Price field using bold and italic formatting. The bottom form enables the Discount field and highlights the value in the Extended Price field.

The Extended Price field is calculated; it does not derive its value directly from an underlying table. An expression in the text box's Control Source property setting ([UnitPrice]*[Quantity]*(1-[Discount])) computes the value when the user moves to a new record or updates the UnitPrice, Quantity, or Discount fields of the current record. (The terms in brackets reference controls, not field names for the underlying data source.)

NOTE

---

Beginners sometimes give fields and controls the same name. This practice can be confusing and lead to errors. (The AutoForm wizard is also guilty of this practice.) Consider adding prefixes to control names to distinguish them from their underlying field names. For example, a good name for a text box control that is bound to a field named UnitPrice is txtUnitPrice.

Figure 5-6. Conditional formatting controls the appearance of the Extended Price and Discount fields on this form.

To apply conditional formatting to a control, select the control and choose Conditional Formatting from the Format menu to open the Conditional Formatting dialog box, shown in Figure 5-7. Every control with conditional formatting has at least two formats—a default format and a special format when a specified condition is true. You can format based on a control's field value, its expression value, or when it gets the focus. When you work with the field value for a control, you can select from a list of comparison operators, such as equal to (=), greater than (>), and less than (<). The condition for the Discount field in Figure 5-7 is Field value is equal to 0. The formatting option for this condition disables the control when the discount is 0.

Figure 5-7. The Conditional Formatting dialog box.

If you apply conditional formatting to a calculated field, such as Extended Price, you must write an expression using standard VBA operators. The condition for the Extended Price field is Expression is text4.value>500. (Text4 is the control that displays the calculated value.) When the field is greater than 500, bold and italic formatting highlight the text box contents.

You can easily apply another condition and special format to a control by clicking the Add button in the Conditional Formatting dialog box and specifying the new condition and its formatting information.

Subforms

A subform, one of the most popular ways of displaying data in Access, is a form embedded within a main form. The main form holds general information about an object (such as an order or a patient name). One or more hierarchically related details (such as order line items or patient visits) appear in one or more subforms on the main form. At least one common field must tie the record source of the main form and each subform together. The common field enables the subform to show only records that match the current record in the main form. When the user moves to a new record on the main form, the subform displays a new set of records that tie uniquely to the new record in the main form.

Figure 5-8 shows a main form, MyOrders, which contains an embedded MyOrderDetails subform. The MyOrders form links the MyOrders query and the MyOrderDetails query based on a shared OrderID field. (These queries and their underlying tables are from the Northwind database.) When I created the main form and the subform, I did not create a relationship between the two queries in the Relationships window or by using subdatasheets. (See Chapter 4 for a discussion of subdatasheets.)

Figure 5-8. A form that contains a subform.

To create a subform, open the main form in Design view, make sure that the Control Wizards button on the Toolbox is depressed, and then drag a table, query, or form from the Database window and drop it on the main form. The subform appears as a control on the main form. To synchronize the main form and the subform, you must designate at least one common field. Select the subform container and set its Link Child and Link Master properties to the common field. For the MyOrders and MyOrderDetails queries, the common field is OrderID.

A main form can have multiple subforms. The only requirement is that the record source for each subform share at least one common field with the record source for the main form. For example, if the main form in Figure 5-8 contained an EmployeeID field, the form could have a second subform based on the Employees table.

If you define relationships between tables and queries in the Relationships window or by using the properties of a subdatasheet, you can create a main form with an embedded subform as easily as you create a simple bound form. In the Database window, select the table or query on which the main form will be based, and then click the New Object: AutoForm button. The AutoForm wizard will build a main form with an embedded subform. The subform uses the information in the Relationships windows or the subdatasheet. You can manually drag other tables, queries, or forms to the main form in Design view to create additional subforms.