Home > Articles > Home & Office Computing > Microsoft Applications

  • Print
  • + Share This
Like this article? We recommend

Forms are the first step along the road to customizing a database. You can enter or edit data in Table view, but a form allows more control over formatting and can combine fields from more than one table.


The example we create in the following steps can be used with either an imported or linked Excel worksheet:

  1. Click the Forms tab in your Access project window.

  2. Double-click Create Form By Using Wizard (see Figure 9).

  3. Figure 9Figure 9

  4. Click the double-arrow button to the right of the field names to add all fields. Click Next.

  5. Click Next to accept columnar layout.

  6. Select a style (or leave Standard selected). Click Next.

  7. Click the Modify the Form's Design radio button. Click Finish.

The wizard creates a default form with some fields much larger than they need to be (see Figure 10).

Figure 10Figure 10

Follow these steps to clean up the form and make it more attractive:

  1. Start by using the Select Objects tool on the toolbar, which should be active by default when you enter Form Design view. Select each field and drag the edges of the box to narrow the field and make it a single line.

  2. NOTE

    When you select a field, its label (at left) is also selected. This technique allows you to move the field and its label as a unit, but it doesn't prevent you from changing the size of the field by itself.

  3. Drag the sizing handle up and left until you have the size you want; then release the mouse button (see Figure 11). If you make a mistake, press Ctrl+Z to undo the last action.

  4. Figure 11Figure 11

  5. We're trying to save some room on this form, so continue resizing fields until each is small as possible while still displaying all the data in the field. If you accidentally make a field too small, you can resize it later. It doesn't matter if the field becomes too short to display the full field name.

  6. With the fields formatted shorter and on one line, they can be moved up and over to make the form more compact. You can click-and-drag the fields and labels, or select a field and use the arrow keys on the keyboard to nudge it up and over.

  7. TIP

    Using the keyboard gives you more control. Holding down the Ctrl key while nudging moves the field and label in smaller increments. You can also resize the field or label with the arrow keys by holding down the Shift key.

  8. To shrink the form to eliminate space that's no longer needed, drag up the divider labeled Form Footer, and drag the right side of the form to the left (see Figure 12).

  9. Figure 12Figure 12

  10. Drag the corner of the form design window up and to the left to shrink it to match the size of the form. You can preview the form at any time by selecting Form View from the main Access window (see Figure 13).

  11. Figure 13Figure 13

  12. To make the cell alignments match those of the original Excel worksheet, select a field and use the alignment buttons on the main Access toolbar. (In the inventory example, I would center the fields Asset Tag, Processor, Speed, RAM, and HD.)

  13. The date format should be abbreviated month and full year. Right-click the Installed field and select Properties (see Figure 14). Next to Format, replace the text with mmm-yyyy.

  14. Figure 14Figure 14

  15. Close the form window and save changes.

  16. Double-click the form name in the project window to open the form.

Using the Form

The finished form displays the records in the order in which they're displayed in the original Excel worksheet. The form window includes controls at the bottom for advancing one record at a time or moving to the first or last record. The button at far right inserts a new record at the end of the table (see Figure 15).

Figure 15Figure 15

To perform a simple sort on one field, click in the field; then use the menu to choose Records, Sort, Sort Ascending (or Sort Descending).

To filter out some of the records—for example, to view only CPU records—click through to the first record you want. Then use the menu to choose Records, Filter, Filter by Selection. Choose Filter Excluding Selection to do the reverse.

To sort or filter on multiple fields, select Filter, Advanced Filter/Sort.

In this example I want to view only records that are CPUs with a speed of less than 2 GHz. These two fields are filtered using the criteria selection. In addition, I want to sort on manufacturer and date installed (see Figure 16). Select Records, Apply Filter/Sort to view results; in this example, the result is 15 filtered records.

Select Records, Remove Filter/Sort to return to the default view.

Figure 16Figure 16

  • + Share This
  • 🔖 Save To Your Account