Chapter 35

Creating and Entering Data with Basic Access Forms

IN THIS CHAPTER

Creating different types of forms

Adding controls to a form

Working with the Property Sheet

Viewing and modifying data in Form view

Printing a form

Adding calculated controls to a form

Converting a form to a report

Forms provide the most flexible way for viewing, adding, editing, and deleting your data. They’re also used for switchboards (forms with buttons that provide navigation), for dialog boxes that control the flow of the system, and for messages. Controls are the objects on forms such as labels, text boxes, buttons, and many others. In this chapter, you learn how to create different types of forms and learn about the types of controls that are used on a form. This chapter also discusses form and control properties and how you determine the appearance and behavior of an Access interface through setting or changing property values.

The forms you add to an Access database are a critical aspect of the application you create. In most situations, users should not be permitted direct access to tables or query datasheets. It’s far too easy for a user to delete valuable information or incorrectly input data into the table. Forms provide a useful tool for managing the integrity of a database’s data. Because forms can contain VBA code or macros, a form can verify data entry or confirm deletions before they occur. Also, a properly designed form can reduce training requirements by helping the user understand what kind of data is required by displaying a message as the user tabs into a control. A form can provide default values or perform calculations based on data input by the user or retrieved from a database table.

Formulating Forms

Use the Forms group on the Create tab of the Ribbon to add forms to your database. The commands in the Forms group — shown in Figure 35.1 — let you create the following different types of forms and ways to work with Access forms:

FIGURE 35.1 Use the Forms group on the Create tab of the Ribbon to add new forms to your database.

• Form: Creates a new form that lets you enter information for one record at a time. You must have a table, query, form, or report open or selected to use this command. When you click the Form button with a table or query highlighted in the Navigation pane, Access binds the new form to the data source and opens the form in Layout view.
• Form Design: Creates a new blank form and displays it in Design view. The form isn’t bound to any data source. You must specify a data source (table or query) and build the form by adding controls from the data source’s Field List.
• Blank Form: Instantly creates a blank form with no controls. Like Form Design, the new form is not bound to a data source, but it opens in Layout view.
• Form Wizard: Access features a simple wizard to help you get started building forms. The wizard asks for the data source, provides a screen for selecting fields to include on the form, and lets you choose from a number of very basic layouts for the new form.
• Navigation Form: The Access navigation form is a specialized form intended to provide user navigation through an application. Navigation forms are discussed in detail later in this chapter.
• More Forms: The More Forms button in the Forms group drops down a gallery containing a number of other form types.
• Multiple Items: This is a simple tabular form that shows multiple records bound to the selected data source.
• Datasheet: Creates a form that is displayed as a datasheet.
• Split Form: Creates a split form, which shows a datasheet in the upper, lower, left, or right area of the form, and a traditional form in the opposite section for entering information on the record selected in the datasheet.
• Modal Dialog: Provides a template for a modal dialog form. A modal dialog form (often called a dialog box) stays on the screen until the user provides information requested by the dialog or is dismissed by the user.

If any of the terminology in the preceding bullets is new to you, don’t worry — each of these terms is discussed in detail in this chapter. Keep in mind that the Access Ribbon and its contents are very context dependent, so every item may not be available when you select the Create tab.

Creating a new form

Like many other aspects of Access development, Access provides multiple ways of adding new forms to your application. The easiest is to select a data source, such as a table, and click the Form command on the Create tab of the Ribbon. Another is to use the Form Wizard and allow the wizard to guide you through the process of specifying a data source and other details of the new form.

Using the Form command

Use the Form command in the Forms group of the Ribbon to automatically create a new form based on a table or query selected in the Navigation pane.

To create a form based on a table, follow these steps:

1. Select the table in the Navigation pane.

2. Select the Create tab of the Ribbon.

3. Click the Form command in the Forms group. Access creates a new form containing all the fields from the table displayed in Layout view, shown in Figure 35.2. Layout view lets you see the form’s data while changing the layout of controls on the form.

FIGURE 35.2 Use the Form command to quickly create a new form with all the fields from a table or query.

The new form is opened in Layout view, which is populated with controls, each of which is bound to a field in the underlying data source. Layout view gives you a good idea how the controls appear relative to one another, but it provides only limited ability to resize controls or move controls on the form. Right-click the form’s title bar and select Design View to rearrange controls on the form.

The Form Design button in the Forms group does essentially the same thing as the Form button, except that no controls are added to the form’s design surface and the form is opened in Design view. Form Design is most useful when you’re creating a new form that might not use all the fields in the underlying data source, and you want more control over control placement from the start.

Similarly, the Blank Form option opens a new empty form, but this time in Layout view. You add controls to the form’s surface from the Field List, but you have little control over control placement. The Blank Form option is most useful for quickly building a form with bound controls with little need for precise placement. A new blank form can be produced in less than a minute.

Using the Form Wizard

Use the Form Wizard command in the Forms group to create a form using a wizard. The Form Wizard visually walks you through a series of questions about the form that you want to create and then creates it for you automatically. The Form Wizard lets you select which fields you want on the form, the form layout (Columnar, Tabular, Datasheet, Justified), and the form title.

To start the Form Wizard and use it create a form, follow these steps:

1. Select the table in the Navigation pane.

2. Click the Create tab of the Ribbon.

3. Click the Form Wizard button in the Forms group. Access starts the Form Wizard, shown in Figure 35.3.

FIGURE 35.3 Use the Form Wizard to create a form with the fields you choose.

4. Use the buttons in the middle of the form to add and remove fields to the Available Fields and Selected Fields list boxes. The wizard is initially populated with fields from the selected table, but you can choose another table or query with the Tables/Queries drop-down list above the field selection area.

---

Note

You can also double-click any field in the Available Fields list box to add it to the Selected Fields list box.

---

---

Caution

If you click Next or Finish without selecting any fields, Access tells you that you must select fields for the form before you can continue.

---

5. Click Next. The second wizard dialog box (shown in Figure 35.4) appears so you can specify the overall layout and appearance of the new form. The Columnar layout is the wizard default, but you can also choose the Tabular, Datasheet, or Justified options.

FIGURE 35.4 Select the overall layout for the new form.

6. After selecting a form layout option, click Next. The last wizard dialog box shown in Figure 35.5 appears, where you provide a name for the new form.

FIGURE 35.5 Saving the new form

7. Edit the form name in the top text box if needed, and click Finish.

Looking at special types of forms

When working with Access, the word form can mean any of several different things, depending on context. This section discusses several different ways that “forms” are used in Access and presents an example of each usage.

Navigation forms

Access 2010 introduced an entirely new form intended specifically as a navigation tool for users. Navigation forms include a number of tabs that provide instant access to any number of other forms in a form/subform arrangement. The Navigation button on the Ribbon offers a number of button placement options (shown in Figure 35.6). Horizontal Tabs is the default.

FIGURE 35.6 The Navigation button provides a number of tab placement options.

Selecting a tab placement in the Navigation drop-down list opens the new navigation form in Design view (see Figure 35.7). The new form includes a row of tabs along the top and a large area under the tabs for embedding subforms. You type the tab’s label (like Products) directly into the tab, or add it through the tab’s Caption property. As you complete the tab’s label, Access adds a new, blank tab to the right of the current tab.

FIGURE 35.7 The Navigation form features a large area for embedding subforms.

In Figure 35.7, the Horizontal Tabs option was selected when choosing a navigation form template and a tab was named Products, which generates a new Add New tab. The alternatives to Horizontal Tabs (Vertical Tabs, Left, Vertical Tabs, Right, and so on) are shown in Figure 35.6.

The example Product tab’s Property Sheet (shown in Figure 35.8) includes the Navigation Target Name property for specifying the Access form to use as the tab’s subform. Select a form from the drop-down list in the Navigation Target Name property, and Access creates the association to the subform for you.

FIGURE 35.8 Use the Navigation Target Name property to specify the tab’s subform.

The completed navigation form is shown in Figure 35.9. The auto-generated navigation form makes extravagant use of screen space. There are a number of things that could be done to enhance this form, such as removing the navigation form’s header section and reducing the empty space surrounding the subform.

FIGURE 35.9 A navigation form is a quick and easy way to provide basic navigation features.

Multiple-items forms

Click the More Forms button in the Forms group of the Ribbon and then click the Multiple Items button to create a tabular form based on a table or query selected in the Navigation pane. A tabular form is much like a datasheet, but it’s much more attractive than a plain datasheet.

Because the tabular form is truly an Access form, you can convert the default text box controls on the form to combo boxes, list boxes, and other advanced controls. Tabular forms display multiple records at one time, which makes them very useful when you’re reviewing or updating multiple records. To create a multiple-items form based on a table, follow these steps:

1. Select the table in the Navigation pane.

2. Select the Create tab on the Ribbon.

3. Click the More Forms button and click Multiple Items. Access creates a new multiple-items form based on the table and displays it in Layout view (as shown in Figure 35.10).

FIGURE 35.10 Create a multiple-items form when you want to see data similar to Datasheet view.

Split forms

Click the More Forms button in the Form group of the Ribbon and then click the Split Form button to create a split form based on a table or query selected in the Navigation pane. The split-form feature gives you two views of the data at the same time, letting you select a record from a datasheet in the lower section and edit the information in a form in the upper section.

To create a split form based on a table, follow these steps:

1. Select the table in the Navigation pane.

2. Select the Create tab of the Ribbon.

3. Click the More Forms button and click Split Form. Access creates a new split form based on the table and displays it in Layout view (shown in Figure 35.11). Resize the form and use the splitter bar in the middle to make the lower section completely visible.

FIGURE 35.11 Create a split form when you want to select records from a list and edit them in a form. Use the splitter bar to resize the upper and lower sections of the form.

The Split Form Orientation property (on the Format tab of the form’s Property Sheet) determines whether the datasheet is on the top, bottom, left, or right of the form area. The default is as shown in Figure 35.11, with the datasheet area on the bottom.

Datasheet forms

Click the More Forms button in the Forms group of the Ribbon and then click the Datasheet button to create a form that looks like a table or query’s datasheet. A datasheet form is useful when you want to see the data in a row and column format, but you want to limit which fields are displayed and editable.

To create a datasheet form based on a table, follow these steps:

Resizing the form area

The area with gridlines in the form is where you work. This is the size of the form when it’s displayed. Resize the grid area of the form by dragging any of the area borders to make it larger or smaller. Figure 35.12 shows a blank form in Design view being resized.

FIGURE 35.12 Design view of a blank form. Resize the form area by dragging the bottom-right corner.

Saving your form

You can save the form at any time by clicking the Save button in the Quick Access Toolbar. When you’re asked for a name for the form, give it a meaningful name (for example, frmProducts, frmCustomers, or frmProductList.) Once you’ve given the form a name, you won’t be prompted the next time you click Save.

When you close a form after making changes, Access asks you to save it. If you don’t save a form, all changes since you opened the form (or since you last clicked Save) are lost. You should frequently save the form while you work if you’re satisfied with the results.

Working with Controls

Controls and properties form the basis of forms and reports. It’s critical to understand the fundamental concepts of controls and properties before you begin to apply them to custom forms and reports.

The term control has many definitions in Access. Generally, a control is any object on a form or report, such as a label or text box. These are the same sort of controls used in any Windows application, such as Access, Excel, web-based HTML forms, or those that are used in any language, such as .NET, Visual Basic, C++, or C#. Although each language or product has different file formats and different properties, a text box in Access is similar to a text box in any other Windows product.

You enter data into controls and display data using controls. A control can be bound to a field in a table (when the value is entered in the control, it’s also saved in some underlying table field), or data can be unbound and displayed in the form but not saved when the form is closed. A control can also be an object, such as a line or rectangle.

Some controls that aren’t built into Access are developed separately — these are ActiveX controls. ActiveX controls extend the basic feature set of Access and are available from a variety of vendors.

Whether you’re working with forms or reports, essentially the same process is followed to create and use controls. In this chapter, I explain controls from the perspective of a form.

Categorizing controls

Forms and reports contain many different types of controls. You can add these controls to forms using the Controls group on the Form Design Tools ⇒ Design tab, or Form Layout Tools ⇒ Arrange tab shown in Figure 35.13. Hovering the mouse over the control displays a ScreenTip telling you what the control is.

FIGURE 35.13 The Design tab lets you add and customize controls in a form’s Design view.

Table 35.1 briefly describes the basic Access controls.

TABLE 35.1 Controls in Access Forms

The Use Control Wizards button, revealed by expanding the Controls group by clicking on the More button in the lower-right corner of the group, doesn’t add a control to a form. Instead, the Use Control Wizards button determines whether a wizard is automatically activated when you add certain controls. The Option Group, Combo Box, List Box, Subform/Subreport, Bound and Unbound Object Frame, and Command Button controls all have wizards to help you when you add a new control. You can also use the ActiveX Controls button (also found at the bottom of the expanded Controls group) to display a list of ActiveX controls, which you can add to Access.

There are three basic categories of controls:

• Bound controls: These are controls that are bound to a field in the data source underlying the form. When you enter a value in a bound control, Access automatically updates the field in the current record. Most of the controls used for data entry can be bound. Controls can be bound to most data types, including Text, Date/Time, Number, Yes/No, OLE Object, and Memo fields.
• Unbound controls: Unbound controls retain the entered value, but they don’t update any table fields. You can use these controls for text label display, for controls such as lines and rectangles, or for holding unbound OLE objects (such as bitmap pictures or your logo) that are stored not in a table but on the form itself. Very often, VBA code is used to work with data in unbound controls and directly update Access data sources.
• Calculated controls: Calculated controls are based on expressions, such as functions or calculations. Calculated controls are unbound because they don’t directly update table fields. An example of a calculated control is =[SalePrice]–[Cost]. This control calculates the total of two table fields for display on a form but is not bound to any table field. The value of an unbound calculated control may be referenced by other controls on the form, or used in an expression in another control on the form or in VBA in the form’s module.

Adding a control

You add a control to a form in a number ways:

• By clicking a button in the Controls group on the Design tab of the Ribbon and drawing a new unbound control on the form: Use the control’s ControlSource property to bind the new control to a field in the form’s data source.
• By dragging a field from the Field List to add a bound control to the form: Access automatically chooses a control appropriate for the field’s data type and binds the control to the selected field.
• By double-clicking a field in the Field List to add a bound control to the form: Double-clicking works just like dragging a field from the Field List to the form. The only difference is that, when you add a control by double-clicking a field, Access decides where to add the new control to the form. Usually the new control is added to the right of the most recently added control, and sometimes below it.
• By right-clicking a field in the Field List and choosing Add Field to View: Right-clicking places a bound control in the same location as if it were double-clicked.
• By copying an existing control and pasting it to another location on the form: Copying a control can be done in all the familiar ways: Click Copy on the Home tab of the Ribbon, right-click the control and choose Copy, or press Ctrl+C. Pasted controls are bound to the same field as the control that was copied.

Using the Controls group

When you use the buttons in the Controls group to add a control, you decide which type of control to use for each field. The control you add is unbound (not attached to the data in a table field) and has a default name such as Text21 or Combo11. After you create the control, you decide what table field to bind the control to, enter text for the label, and set any properties. You’ll learn more about setting properties later in this chapter.

You can add one control at a time using the Controls group. To create three different unbound controls, for example, you would perform these steps:

1. With the form created earlier open in Design view, click the Text Box button in the Controls group.

2. Move the mouse pointer to the Form Design window and drag on the form’s surface to specify the Text Box control’s initial size and position.

3. Click the Option button in the Controls group, and drag on the form to specify the Option button control’s initial size and position.

4. Click the Check Box button in the Controls group and add it to the form as you added the other controls. When you’re done, your result would resemble Figure 35.14.

FIGURE 35.14 Unbound controls added from the Controls group

Using the Field List

The Field List displays a list of fields from the table or query the form is based on. Open the Field List by clicking the Add Existing Fields button in the Tools group on the Form Design Tools ⇒ Design tab of the Ribbon (refer to Figure 35.13).

If you created a form using a method that automatically binds the form to a table or query, the field list for that table or query will be displayed. For this example, we created a form using the Blank Form button, which does not automatically bind the form to a datasource. In this case, the Field List only shows a Show All Tables link. Click the Show All Tables link to get a list of tables. Then click the plus sign next to the desired table to show the fields in that table.

Drag a field from the Field List and drop it onto the form to create a control bound to that field on the form. You can select and drag fields one at a time or select multiple fields by using the Ctrl key or Shift key:

• To select multiple contiguous fields, hold down the Shift key and click the first and last fields that you want.
• To select multiple noncontiguous fields, hold down the Ctrl key and click each field that you want.

By default, the Field List appears docked on the right of the Access window. The Field List window is movable and resizable and displays a vertical scroll bar if it contains more fields than can fit in the window. Figure 35.15 shows the Field List undocked and moved on top of the form.

FIGURE 35.15 Click Add Existing Fields in the Tools group to show the Field List.

Most often, dragging a field from the Field List adds a bound text box to the Design window. If you drag a Yes/No field from the Field List window, Access adds a check box. Optionally, you can select the type of control by selecting a control from the Controls group and dragging the field to the Design window.

You gain several distinct advantages by dragging a field from the Field List window:

• The control is automatically bound to the field.
• Field properties inherit table-level formats, status bar text, and data validation rules and messages.
• The label control and label text are created with the field name as the caption.
• The label control is attached to the field control, so they move together.

In the example in Figure 35.16, the Description, Category, RetailPrice, and Cost fields were selected in the Field List window and dragged to the form. Double-clicking a field also adds it to the form.

FIGURE 35.16 Drag fields from the Field List to add bound controls to the form.

You can see four new pairs of controls in the form’s Design view — each pair consists of a Label control and a Text Box control (Access attaches the Label control to the Text Box automatically). You can work with these controls as a group or independently, and you can select, move, resize, or delete them. Notice that each control has a label with a caption matching the field name, and the Text Box control displays the bound field name used in the text box. If you want to resize just the control and not the label, you must work with the two controls (label and associated text box) separately. You’ll learn about working with labels attached to controls later in this chapter.

Close the Field List by clicking the Add Existing Fields command in the Tools group of the Design tab or by clicking the Close button on the Field List.

In the “Introducing Properties” section, later in this chapter, you learn how to change the control names, captions, and other properties. Using properties speeds the process of naming controls and binding them to specific fields. If you want to see the differences between bound and unbound controls, switch to Form view using the View command in the View group of the Ribbon. Bound controls display data from the table that they’re bound to. Unbound controls don’t display data because they aren’t bound to any data source.

Selecting and deselecting controls

After you add a control to a form, you can resize it, move it, or copy it. The first step is to select one or more controls. Depending on its size, a selected control might show from four to eight handles (small squares called moving and sizing handles) around the control — at the corners and midway along the sides. The move handle in the upper-left corner is larger than the other handles and you use it to move the control. You use the other handles to size the control. Figure 35.17 displays some selected controls and their moving and sizing handles.

FIGURE 35.17 Selected controls and their moving and sizing handles

The Select command (which looks like an arrow) in the Controls group of the Design tab must be chosen in order for you to select a control. If you use the Controls group to create a single control, Access automatically reselects the pointer as the default.

Selecting a single control

Select any individual control by clicking anywhere on the control. When you click a control, the sizing handles appear. If the control has an attached label, the move handle for the label also appears in the upper-left corner of the control. If you select a label control that is associated with another control, all the handles for the label control are displayed, and only the move handle appears in the associated control.

Selecting multiple controls

You select multiple controls in these ways:

• By clicking each control while holding down the Shift key
• By dragging the pointer through or around the controls that you want to select
• By clicking and dragging in the ruler to select a range of controls

Figure 35.17 shows the result of selecting the multiple bound controls graphically. When you select multiple controls by dragging the mouse, a rectangle appears as you drag the mouse. Be careful to drag the rectangle only through the controls you want to select. Any control you touch with the rectangle or enclose within it is selected. If you want to select labels only, make sure that the selection rectangle only touches the labels.

Deselecting controls

Deselect a control by clicking an unselected area of the form that doesn’t contain a control. When you do so, the handles disappear from any selected control. Selecting another control also deselects a selected control.

Manipulating controls

Creating a form is a multistep process. The next step is to make sure that your controls are properly sized and moved to their correct positions. The Form Design Tools ⇒ Arrange tab or Form Layout Tools ⇒ Arrange tab (shown in Figure 35.18) contains commands used to assist you in manipulating controls.

FIGURE 35.18 The Arrange tab lets you work with moving and sizing controls, as well as manipulate the overall layout of the form.

Resizing a control

You resize controls using any of the smaller handles in the upper, lower, and right edges of the control. The sizing handles in the control corners let you drag the control larger or smaller in both width and height — and at the same time. Use the handles in the middle of the control sides to size the control larger or smaller in one direction only. The top and bottom handles control the height of the control; the left and right handles change the control’s width.

When the mouse pointer touches a corner sizing handle of a selected control, the pointer becomes a diagonal double arrow. You can then drag the sizing handle until the control is the desired size. If the mouse pointer touches a side handle in a selected control, the pointer changes to a horizontal or vertical double-headed arrow. Figure 35.19 shows the Description control after being resized. Notice the double-headed arrow in the corner of the Description control.

FIGURE 35.19 Resizing a control

When you double-click on any of the sizing handles, Access resizes a control to best fit the text contained in the control. This feature is especially handy if you increase the font size and then notice that the text is cut off either at the bottom or to the right. For label controls, note that this best-fit sizing adjusts the size vertically and horizontally, though text controls are resized only vertically. This is because when Access is in Form Design view, it can’t predict how much of a field to display — the field name and field contents can be radically different. Sometimes, Access doesn’t correctly resize the label and you must manually change its size.

Sizing controls automatically

Clicking the Size/Space button in the Sizing & Ordering group of the Arrange tab opens a menu with several commands that help you arrange controls:

• To Fit: Adjusts control height and width for the font of the text they contain
• To Tallest: Makes selected controls the height of the tallest selected control
• To Shortest: Makes selected controls the height of the shortest selected control
• To Grid: Moves all sides of selected controls in or out to meet the nearest points on the grid
• To Widest: Makes selected controls the width of the widest selected control
• To Narrowest: Makes selected controls the height of the narrowest selected control

Moving a control

After you select a control, you can easily move it, using either of these methods:

• Click once to select the control and move the mouse over any of the highlighted edges; when the mouse pointer turns to a four-directional arrow, drag to move the control to a new location.
• Select the control and use the arrow keys on the keyboard to move the control. Using this technique, a control changes by only 1 pixel at a time (or moves to the nearest grid line if Snap to Grid is selected in the Size/Space menu in the Sizing & Ordering group of the Arrange tab).

Figure 35.20 shows a Label control that has been separately moved to above a Text Box control.

FIGURE 35.20 Moving a control

Press Esc before you release the mouse button to cancel a moving or a resizing operation. After a move or resizing operation is complete, click the Undo button on the Quick Access Toolbar to undo the changes, if needed.

Aligning controls

You might want to move several controls so that they’re all aligned. The Sizing & Ordering group’s Align menu choices on the Arrange tab contains the following alignment commands:

• To Grid: Aligns the top-left corners of the selected controls to the nearest grid point
• Left: Aligns the left edge of the selected controls with the leftmost selected control
• Right: Aligns the right edge of the selected controls with the rightmost selected control
• Top: Aligns the top edge of the selected controls with the topmost selected control
• Bottom: Aligns the bottom edge of the selected controls with the bottommost selected control

You can align any number of selected controls by selecting an align command. When you choose one of the align commands, Access uses the control that’s the closest to the desired selection as the model for the alignment. For example, suppose that you have three controls and you want to left-align them. They’re aligned on the basis of the control farthest to the left in the group of the three controls.

Figure 35.21 shows several sets of controls. The first set of controls is not aligned. The label controls in the middle set of controls have been left-aligned while the text box controls in the right-side set have been right-aligned.

FIGURE 35.21 An example of unaligned and aligned controls on the grid

Each type of alignment must be done separately. In this example, you can left-align all the labels or right-align all the text boxes at once.

By default, Access displays a series of small dots across the entire surface of a form while it’s in Design view. The grid can assist you in aligning controls. Hide or display the grid by selecting the Grid command from the Size/Space gallery under the Sizing & Ordering group on the Arrange tab of the Ribbon. You can also hide or display the ruler using the Ruler command in the same gallery.

Use the Snap to Grid command in the Size/Space menu to align controls to the grid as you draw or place them on a form. This also aligns existing controls to the grid when you move or resize them.

As you move or resize existing controls, Access lets you move only from grid point to grid point. When Snap to Grid is off, Access ignores the grid and lets you place a control anywhere on the form or report.

Click Size/Space in the Sizing & Ordering group on the Arrange tab to access commands to adjust spacing between controls. The spacing commands adjust the distance between controls on the basis of the space between the first two selected controls. If the controls are across the screen, use horizontal spacing; if they’re down the screen, use vertical spacing. The commands in the Spacing group of the Size/Space menu are

• Equal Horizontal: Makes the horizontal space between selected controls equal. You must select three or more controls in order for this command to work.
• Increase Horizontal: Increases the horizontal space between selected controls by one grid unit.
• Decrease Horizontal: Decreases the horizontal space between selected controls by one grid unit.
• Equal Vertical: Makes the vertical space between selected controls equal. You must select three or more controls in order for this command to work properly.
• Increase Vertical: Increases the vertical space between selected controls by one grid unit.
• Decrease Vertical: Decreases the vertical space between selected controls by one grid unit.

Modifying the appearance of a control

To modify the appearance of a control, select the control and click commands that modify that control, such as the options in the Font or Controls group. Follow these steps to change the text color and font of an example label:

To modify the appearance of multiple controls at once, select the controls and click commands to modify the controls, such as commands in the Font or Controls group. To change the text color and font for multiple labels and text boxes, for example, follow these steps:

1. Select multiple labels and three text boxes by dragging over them.

2. In the Font group on the Format tab of the Ribbon, change the Font Size to 14, click the Bold button, and change Font Color to blue.

3. Resize the labels and text boxes so the larger text fits. You can double-click any of the sizing handles to the controls automatically. As you click the commands, the controls’ appearances change to reflect the new selections (shown in Figure 35.22). The fonts in each control increase in size, become bold, and turn blue. Any changes you make apply to all selected controls.

FIGURE 35.22 Changing the appearance of multiple controls at the same time

When multiple controls are selected, you can also move the selected controls together. When the mouse pointer changes to the four-directional arrow, drag to move the selected controls. You can also change the size of all the controls at once by resizing one of the controls in the selection. All the selected controls increase or decrease by the same number of units.

Grouping controls

If you routinely change properties of multiple controls, you might want to group them together. To group controls together, select the controls by holding down the Shift key and clicking them or dragging over them. After the desired controls are selected, select Arrange ⇒ Sizing & Ordering ⇒ Size/Space ⇒ Group. Then, when one control in a group is selected, all controls in that group are automatically selected, as shown in Figure 35.23.

FIGURE 35.23 Grouping multiple controls together

Double-click a control to select just one control in a group. After a single control in the group is selected, you can click any other control to select it.

To resize the entire group, point to the side you want to resize. After the double arrow pointer appears, drag until you reach the desired size. Every control in the group changes in size. To move the entire group, drag the group to its new location. With grouped controls, you don’t have to select all the controls every time you change something about them.

To remove a group, select the group by clicking any field inside the group, and then select Arrange ⇒ Sizing & Ordering ⇒ Size/Space ⇒ Ungroup.

Changing a control’s type

Although there are times you may want to use a check box to display a Boolean (yes/no) data type, there are other ways to display the value, such as a toggle button, as shown in Figure 35.24. A toggle button is raised if it’s true and pressed if it’s false.

FIGURE 35.24 Turn a check box into a toggle button

Use these steps to turn a check box into a toggle button:

Copying a control

You can create copies of any control by copying it to the Clipboard and then pasting the copies where you want them. If you have a control for which you’ve entered many properties or specified a certain format, you can copy it and revise only the properties (such as the control’s name and bound field name) to make it a different control. This capability is useful with a multiple-page form when you want to display the same values on different pages and in different locations, or when copying a control from one form to another.

Deleting a control

You can delete a control by simply selecting it in the form’s Design view and pressing the Delete key on your keyboard. The control and any attached labels will disappear. You can bring them back by immediately selecting Undo from the Quick Access Toolbar. You can also select Cut from the Clipboard group on the Home tab of the Ribbon, or Delete from the Records group on the Home tab of the Ribbon.

You can delete more than one control at a time by selecting multiple controls and pressing Delete. You can delete an entire group of controls by selecting the group and pressing Delete. If you have a control with an attached label, you can delete only the label by clicking the label itself and then selecting one of the delete methods. If you select the control, both the control and the label are deleted.

Reattaching a label to a control

If you accidentally delete a label from a control, you can reattach it. To create and then reattach a label to a control, follow these steps: Later in this chapter, in the “Naming control labels and their captions” section, you’ll learn about the special relationship between a control and its label. By default, Access controls include a label when the control is added to a form; this label moves around with the control as you reposition the control on the form. The “Naming control labels and their captions” section describes these behaviors and how to work with control labels.

Another way to attach a label to a control is to select the label control and then click the information button next to the label, shown in Figure 35.25. This information button lets you know that the selected label is unassociated with a control. Select the Associate Label with a Control command from the menu, and then select the control you want to associate the label with.

FIGURE 35.25 Associating a label with a control

Introducing Properties

Properties are named attributes of controls, fields, or database objects that are used to modify the characteristics of a control, field, or object. Examples of these attributes are the size, color, appearance, or name of an object. A property can also modify the behavior of a control, determining, for example, whether the control is read-only or editable and visible or not visible.

Properties are used extensively in forms and reports to change the characteristics of controls. Each control on the form has properties. The form itself also has properties, as does each of its sections. The same is true for reports; the report itself has properties, as does each report section and individual control. The label control also has its own properties, even if it’s attached to another control.

Everything that you do with the Ribbon commands — from moving and resizing controls to changing fonts and colors — can be done by setting properties. In fact, all these commands do is change properties of the selected controls.

Displaying the Property Sheet

Properties are displayed in a Property Sheet (sometimes called a Property window). To display the Property Sheet for a control in Form Design view, follow these steps:

1. Click the control to select it.

2. Click the Property Sheet command in the Tools group on the Design tab of the Ribbon, or press F4 to display the Property Sheet. The screen should look like the one shown in Figure 35.26. In Figure 35.26, the Description text box control has been selected and the Format tab in the Property Sheet is being scrolled to find the margin properties associated with a text box.

FIGURE 35.26 Change an object’s properties with the Property Sheet.

Because the Property Sheet is a window, it can be undocked, moved, and resized. It does not, however, have Maximize or Minimize buttons.

There are several ways to display a control’s Property Sheet if it isn’t visible:

• Select a control and click the Property Sheet command in the Tools group on the Design tab of the Ribbon.
• Double-click the edge of any control.
• Right-click any control and select Properties from the pop-up menu.
• Press F4 while any control is selected.

Getting acquainted with the Property Sheet

With the Property Sheet displayed, click any control in Design view to display the properties for that control. Select multiple controls to display similar properties for the selected controls. The vertical scroll bar lets you move among various properties.

The Property Sheet has an All tab that lets you see all the properties for a control. Or you can choose another tab to limit the view to a specific group of properties. The specific tabs and groups of properties are as follows:

• Format: These properties determine how a label or value looks: font, size, color, special effects, borders, and scroll bars.
• Data: These properties affect how a value is displayed and the data source it’s bound to: control source, input masks, validation, default value, and other data type properties.
• Event: Event properties are named events, such as clicking a mouse button, adding a record, pressing a key for which you can define a response (in the form of a call to a macro or a VBA procedure), and so on.
• Other: Other properties show additional characteristics of the control, such as the name of the control or the description that displays in the status bar.

Figure 35.26 shows the Property Sheet for a Description text box selected on the form. The first column lists the property names; the second column is where you enter or select property settings or options. You can use the drop-down list near the top of the Property Sheet (displaying Description in Figure 35.26) to change which control’s properties are shown. The drop-down list also enables you to select other objects on the form, like the Detail section, Form Header, or the Form itself.

Changing a control’s property setting

There are many different methods for changing property settings, including the following:

• Enter or select the desired value in a Property Sheet.
• For some properties, double-clicking the property name in the Property Sheet cycles through all the acceptable values for the property.
• Change a property directly by changing the control itself, such as changing its size.
• Use inherited properties from the bound field or the control’s default properties.
• Enter color selections for the control by using the Ribbon commands.
• Change label text style, size, color, and alignment by using the Ribbon commands.

You can change a control’s properties by clicking a property and typing the desired value.

In Figure 35.27, you see a down arrow and a button with three dots to the right of the Control Source property entry area. Some properties display a drop-down arrow in the property entry area when you click in the area. The drop-down arrow tells you that Access has a list of values from which you can choose. If you click the down arrow in the Control Source property, you find that the drop-down list displays a list of all fields in the example data source — tblProducts. Setting the Control Source property to a field in a table creates a bound control.

FIGURE 35.27 Setting a control’s Control Source property

Some properties have a list of standard values such as Yes or No; others display varying lists of fields, forms, reports, or macros. The properties of each object are determined by the control itself and what the control is used for.

A nice feature in Access is the ability to cycle through property choices by repeatedly double-clicking the choice. For example, double-clicking the Display When property alternately selects Always, Print Only, and Screen Only.

The Builder button contains an ellipsis (...) and opens one of the many builders in Access — including the Macro Builder, the Expression Builder, and the Code Builder. When you open a builder and make some selections, the property is filled in for you. You’ll learn about builders later in this book.

Each type of object has its own Property window and properties. These include the form itself, each of the form sections, and each of the form’s controls. You display each of the Property windows by clicking the object first or by selecting the object from the Property Sheet’s combo box. The Property window will instantly change to show the properties for the selected object.

Naming control labels and their captions

You might notice that each of the data fields has a label control and a text box control. Normally, the label’s Caption property is the same as the text box’s Name property. The text box’s Name property is usually the same as the table’s field name — shown in the Control Source property. Sometimes, the label’s Caption is different because a value was entered into the Caption property for each field in the table.

When creating controls on a form, it’s a good idea to use standard naming conventions when setting the control’s Name property. Name each control with a prefix followed by a meaningful name that you’ll recognize later (for example, txtTotalCost, cboState, lblTitle). Table 35.2 shows the naming conventions for form and report controls. You can find a very complete, well-established naming convention online at www.xoc.net/standards.

TABLE 35.2 Form/Report Control Naming Conventions

The properties displayed in Figure 35.27 are the specific properties for the Description text box. The first two properties, Name and Control Source, are set to Description.

The Name is simply the name of the field itself. When a control is bound to a field, Access automatically assigns the Name property to the bound field’s name. Unbound controls are given names such as Field11 or Button13. However, you can give the control any name you want.

With bound controls, the Control Source property is the name of the table field to which the control is bound. In this example, Description refers to the field with the same name in tblProducts. An unbound control has no control source, whereas the control source of a calculated control is the actual expression for the calculation, as in the example =[SalePrice]–[Cost].

Using Form View

Form view is where you actually view and modify data. The data in Form view is the same data shown in a table or query’s Datasheet view, just presented a little differently. Form view presents the data in a user-friendly format, which you create and design. Double-click a form object in the Navigation pane to open the form in Form view.

Figure 35.28 shows the Access window with a newly created form displayed in Form view. This view has many of the same elements as Datasheet view. At the top of the screen, you see the Access title bar, the Quick Access Toolbar, and the Ribbon. The form in the center of the screen displays your data, one record at a time.

FIGURE 35.28 A form in Form view

The status bar at the bottom of the Access window displays the active field’s Status Bar Text property that you defined when you created the table (or form). If no Status Bar Text exists for a field, Access displays “Form View” in the status bar. Generally, error messages and warnings appear in dialog boxes in the center of the screen (rather than in the status bar). The navigation controls and search box are found at the bottom of the form’s window and the view shortcuts are found in the status bar. These features let you move from record to record, quickly find data, or switch views.

Looking at the Home tab of the Ribbon

The Home tab of the Ribbon tab (shown in Figure 35.29) provides a way to work with the data. The Home tab has some familiar objects on it, as well as some new ones. This section provides an overview of the Home tab. The individual commands are described in more detail later in this chapter.

FIGURE 35.29 The Home tab of the Ribbon in Form Design view.

At the far left is the Views group, which enables you to switch among the following views, which you can see by clicking the button’s drop-down arrow.

• Form view: Allows you to manipulate data on the form
• Datasheet view: Shows the data in the row-and-column format
• Layout view: Allows you to change the form’s design while viewing data
• Design view: Allows you to make changes to the form’s design

The Clipboard group contains the Cut, Copy, Paste, and Format Paint commands. These commands work like the same commands in other applications (like Word and Excel). The Clipboard is a resource provided by Windows and shared by virtually all Windows applications. Items you copy or cut from Excel, for example, can be pasted into Access if the context is appropriate. For example, you could copy a VBA procedure from an Excel worksheet and paste it into an Access VBA code module because the contexts are the same. But you can’t copy an Excel spreadsheet and paste it into an Access form in Form view, because Form view has no way of working with an Excel spreadsheet.

After you cut or copy a selection in Access, the Paste command’s drop-down arrow gives you three choices:

• Paste: Inserts whatever item has been copied to the Windows Clipboard into the current location in Access. Depending on the task you’re working on, the pasted item might be plain text, a control, a table or form, or some other object.
• Paste Special: Gives you the option of pasting the contents of the Clipboard in different formats (text, CSV, records, and so on).
• Paste Append: Pastes the contents of the Clipboard as a new record — as long as a record with a similar structure was copied to the Clipboard. Obviously, Paste Append remains disabled for any operation that doesn’t involve copying and pasting a database table record.

The Sort & Filter group lets you change the order of the records, and, based on your criteria, limit the records shown on the form.

The Records group lets you save, delete, or add a new record to the form. It also contains commands to show totals, check spelling, freeze and hide columns, and change the row height and cell width while the form is displayed in Datasheet view.

The Find group lets you find and replace data and go to specific records in the datasheet. Use the Select command to select a record or all records.

The Window group contains two commands:

• Size to Fit Form: When you work with a form in Design view, Access “remembers” the size (height and width) of the form at the moment you save it. When working with the overlapping windows interface, a user may resize a form by dragging its borders to a new size and shape. The Size to Fit Form returns the form to the dimension set at design time.
• Switch Windows: Switch Windows provides a handy way to see all the objects (forms, reports, tables, and so on) that are currently open in the main Access windows. You can change to another object by selecting it from the drop-down list that appears when you click Switch Windows.

The Text Formatting group lets you change the look of the datasheet in Datasheet view or Design view. Use these commands to change the font, size, bold, italic, color, and so on. Use the Align Left, Align Right, and Align Center commands to justify the data in the selected column. Click the Gridlines option to toggle gridlines on and off. Use Alternate Row Color to change the colors of alternating rows, or make them all the same. When modifying text in a Long Text field with the Text Format property set to Rich Text, you can use these commands to change the fonts, colors, and so on.

Navigating among fields

Navigating a form is nearly identical to moving around a datasheet. You can easily move around the form by clicking the control that you want and making changes or additions to your data. Because the form window displays only as many fields as can fit onscreen, you need to use various navigational aids to move within your form or between records.

Table 35.3 displays the navigational keys used to move between fields within a form.

TABLE 35.3 Navigating in a Form

Moving among records in a form

Although you generally use a form to display one record at a time, you still need to move between records. The easiest way to do this is to use the Navigation buttons, shown in Figure 35.30.

FIGURE 35.30 The Navigation buttons of a form

The Navigation buttons are the six controls located at the bottom-left corner of the Form window. The two leftmost controls move you to the first record and the previous record in the form. The three rightmost controls position you on the next record, last record, or new record in the form. If you know the record number (the row number of a specific record), you can click the Current Record box, enter a record number, and press Enter to go directly to that record.

The record number displayed in the Navigation controls is just an indicator of the current record’s position in the recordset and may change when you filter or sort the records. To the right of the record number is the total number of records in the current view. The record count may not be the same as the number of records in the underlying table or query. The record count changes when you filter the data on the form.

Changing Values in a Form

Earlier in this book, you learned datasheet techniques to add, change, and delete data within a table. These techniques are the same ones you use on an Access form. Table 35.4 summarizes these techniques.

TABLE 35.4 Editing Techniques

Knowing which controls you can’t edit

Some controls, including the following, can’t be edited:

• Controls displaying AutoNumber fields: Access maintains AutoNumber fields automatically, calculating the values as you create each new record.
• Calculated controls: Access may use calculated controls in forms or queries. Calculated values are not actually stored in your table.
• Locked or disabled fields: You can set certain form and control properties to prevent changes to the data.
• Controls in multiuser locked records: If another user locks the record, you can’t edit any controls in that record.

Working with pictures and OLE objects

Object Linking and Embedding (OLE) objects are objects not part of an Access database. OLE objects commonly include pictures but may be any number of other data types, such as links to Word documents, Excel spreadsheets, and audio files. You can also include video files such as MPG or AVI files.

In Datasheet view, you can’t view a picture or an OLE object without accessing the OLE server (such as Word, Excel, or the Windows Media Player). In Design view, however, you can size the OLE control area to be large enough to display a picture, chart, or other OLE objects in Form view. You can also size text box controls on forms so that you can see the data within the field — you don’t have to zoom in on the value, as you do with a datasheet field.

The Access OLE control supports many types of objects. As with a datasheet, you have two ways to enter OLE fields into a form:

• Copy the object (such as an MP3 file) to the Clipboard and paste it from the controls in the Clipboard group of the Ribbon.
• Right-click the OLE control and click Insert Object from the shortcut menu to display the Insert Object dialog box, shown in Figure 35.31. Use the Insert Object dialog box to add a new object to the OLE field, or add an object from an existing file. The Create from File option button adds a picture or other OLE object from an existing file.

  FIGURE 35.31 The Insert Object dialog box

  

When displaying a picture in an OLE control, set the Size Mode property to control how the image representing the OLE object is displayed. The settings for this property are

• Clip: Keeps the image at its original size and cuts off parts of the picture that don’t fit in the control.
• Zoom: Fits the image in the control and keeps it in its original proportion, which may result in extra white space.
• Stretch: Sizes an image to fit exactly between the frame borders. The stretch setting may distort the picture.

Entering data in the Long Text field

The Features field in the form shown in Figure 35.28 is a Long Text data type. This type of field contains up to 1GB of characters. The first three lines of data are visible in the text box. When you click in this text box, a vertical scroll bar appears, allowing you to view all the data in the control.

Better yet, you can resize the control in the form’s Design view if you want to make it larger to show more data. Another method for viewing more text in a Long Text field’s text box is to press Shift+F2 with the text box selected. A Zoom dialog box is displayed, as shown in Figure 35.32, allowing you to see more data. The text in the Zoom dialog box is fully editable. You can add new text or change text already in the control.

FIGURE 35.32 The Zoom dialog box

Entering data in the Date field

The SaleDate field in the frmSales_Layout form shown next in Figure 35.33 is a Date/Time data type. This field is formatted to accept and show date values. When you click in a Date/Time field on a form, a Date Picker icon automatically appears next to it, as shown in Figure 35.33. Click the Date Picker to display a calendar from which you can choose a date.

FIGURE 35.33 Using the Date Picker control

If the Date Picker doesn’t appear, switch to Design view and change the control’s Show Date Picker property to For Dates. Set the Show Date Picker property to Never if you don’t want to use the Date Picker.

Using option groups

Option groups let you choose from a number of option buttons (sometimes called radio buttons). Option buttons let you select one value while deselecting all the other values. Option groups work best when you have a small number of mutually exclusive choices to select from. Figure 35.34 shows an option group labeled Contact Type next to the Follow-Up Date text box. Option groups also work with toggle buttons and check boxes.

FIGURE 35.34 Using an option group to select a mutually exclusive value

The easiest and most efficient way to create option groups is with the Option Group Wizard. You can use it to create option groups with multiple option buttons, toggle buttons, or check boxes. When you’re through, all your control’s property settings are correctly set. To create an option group, switch to Design view and select the Option Group button from the Design tab’s Controls group. Make sure the Use Control Wizards command is selected.

Using combo boxes and list boxes

Access has two types of controls — list boxes and combo boxes — for showing lists of data from which a user can select. The list box always displays as much of the list as possible, whereas the combo box has to be clicked to open the list. Also, the combo box enables you to enter a value that is not on the list and takes up less room on the form.

Because combo boxes are very efficient use of space on the surface of a form, you may want to use (for example) a combo box containing values from a table with customer or vendor names, as shown in Figure 35.35. The easiest way to do this is with the Combo Box Wizard. This wizard walks you through the steps of creating a combo box that looks up values in another table. To create a combo box, switch to Design view and select the Combo Box command from the Design tab’s Controls group. Make sure the Use Control Wizards command is selected.

FIGURE 35.35 Using a combo box to select a value from a list.

After you create the combo box, examine the Row Source Type, Row Source, Column Count, Column Heads, Column Widths, Bound Column, List Rows, and List Width properties. Once you become familiar with setting these properties, you can right-click a text box, choose Change To ⇒ Combo Box, and set the combo box’s properties manually.

Switching to Datasheet view

With a form open, switch to Datasheet view by using one of these methods:

• Click the Datasheet View command in the Home tab’s Views group.
• Click the Datasheet View button in the View Shortcuts section at the bottom-right of the Access window.
• Right-click the form’s title bar — or any blank area of the form — and choose Datasheet View from the pop-up menu.

The datasheet is displayed with the cursor on the same field and record that it occupied while in the form. Moving to another record and field and then redisplaying the form in Form view causes the form to appear with the cursor on the field occupied in Datasheet view.

To return to Form view — or any other view — select the desired view from the Views group, the View Shortcuts, or the pop-up menu.

Saving a record

Access automatically saves each record when you move off it. Pressing Shift+Enter or selecting Save from the Records group on the Ribbon saves a record without moving off it. Closing the form also saves a record.

Printing a Form

You can print one or more records in your form exactly as they appear onscreen. (You learn how to produce formatted reports in Chapter 37.) The simplest way to print is to use the keyboard shortcut Ctrl+P to show the Print dialog box. The Print dialog box has several options to customize your printout:

• Print Range: Prints the entire form or only selected pages or records
• Copies: Determines the number of copies to be printed
• Collate: Determines whether copies are collated

You can also click the Properties button and set options for the selected printer or select a different printer. The Setup button allows you to set margins and print headings.

Printing a form is like printing anything else. Windows is a WYSIWYG (“What You See Is What You Get”) environment, so what you see on the form is what you get in the printed hard copy. If you added page headers or page footers, they would be printed at the top or bottom of the page. The printout contains any formatting that you specified in the form (including lines, boxes, and shading) and converts colors to grayscale if you’re using a black-and-white printer.

The printout includes as many pages as necessary to print all the data. If your form is wider than a single printer page, you need multiple pages to print your form. Access breaks up the printout as necessary to fit on each page.

The Print command under the File menu provides additional printing options:

• Quick Print: Prints the active form using the default printer with no opportunity to change any options
• Print: Shows the Print dialog box
• Print Preview: Shows what the printout will look like based on the current settings

Summary

In this chapter, you learned how to add different types of forms to your database using the Create tab’s Form group. At this point, you should be able to:

• Select a table and create a form for it using either the Form button or the Form Wizard.
• Add different types of controls to the form.
• Move, resize, and format controls.
• Display the Property Sheet, and use it to change properties for controls and other parts of the form.
• Display Form view, enter data, and navigate among records.
• Print a form.