Access: Working with Forms

Lesson 6: Working with Forms

/en/access/working-with-tables/content/

Introduction

While you can always enter data directly into database tables, you might find it easier to use forms. Forms ensure you're entering the right data in the right location and format. This can help keep your database accurate and consistent.

This lesson will address the benefits of using forms in a database. You will review examples of different forms and form components. Finally, you will learn how to use forms to enter new records and view and edit existing ones.

Throughout this tutorial, we will be using a sample database. If you would like to follow along, you'll need to download our Access sample database. You will need to have Access installed on your computer in order to open the example.

Watch the video below to learn more about working with forms in Access.

Why use forms?

Many of us fill out forms so often that we hardly notice when we're asked to use them. Forms are so popular because they're useful to the person asking for the information and to the person providing it. They are a way of requiring information in a specific format, which means the person filling out the form knows exactly which information to include and where to put it.

Illustration of a paper form

This is just as true of forms in Access. When you enter information into a form in Access, the data goes exactly where it's supposed to go: into one or more related tables. While entering data into simple tables is fairly straightforward, data entry becomes more complicated as you start populating tables with records from elsewhere in the database. For instance, the Orders table in a bakery's database might link to information on customers, products, and prices drawn from related tables. For example, in the Orders table below the Customer ID field is linked to the Customers table.

The Customer ID field links to the Customers table

In fact, in order to see the entire order you would also have to look at the Order Items table, where the menu items that make up each order are recorded.

The Order ID field links to the Orders table, and the Menu Item ID field links to the Menu Items table

The records in these tables include ID numbers of records from other tables. You can't learn much just by glancing at these records because the ID numbers don't tell you much about the data they relate to. Plus, because you have to look at two tables just to view one order, you might have a difficult time even finding the right data. It's easy to see how viewing or entering several records this way could become a difficult and tedious task.

A form containing the same data might look like this:

The same order viewed in a form

As you can see, this record is much easier to understand when viewed in a form. Modifying the record also would be easier because you wouldn't have to know any ID numbers to enter new data. When you're using a form, you don't have to worry about entering data into the right tables or in the right format because the form can handle these things itself. There's no need to go back and forth between tables because forms bring all of the information you need together in one place.

Not only do forms make the data entry process easier for the user, but they also keep the database itself working smoothly. With forms, database designers can control exactly how users are able to interact with the database. They can even set restrictions on individual form components to ensure all of the needed data is entered and that it's all entered in a valid format. This is useful because keeping data consistent and organized is essential for an accurate and powerful database.

Working with forms

To open an existing form:

  1. Open your database and locate the Navigation pane.
  2. In the Navigation pane, locate the form you want to open.
  3. Double-click the desired form.
    Clicking a form in the Navigation Bar
  4. It will open and appear as a tab in the Document Tabs bar.
    Opening a form

Entering and modifying data

Depending on the database you're using, the forms you work with may include special tools and features that let you perform common tasks with one click of a button. You'll see examples of these tools in the interactives later in this lesson. However, no matter what type of form you're working with, you can follow the same procedures for carrying out certain basic tasks.

To add a new record:

There are two ways to add a new record to a form:

  • In the Records group on the Home tab of the Ribbon, click the New command.
    Adding a new record from the Ribbon
  • On the Record Navigation bar at the bottom of the window, click the New Record button.
    Creating a new record from the Record Navigation bar

To find an existing record to view or edit:

There are two ways to find and view an existing record using a form, and they both use the Navigation bar at the bottom of the screen:

  • To look through records one at a time, click the navigation arrows. The right arrow will take you to the next record, and the left arrow will take you to the previous one.
    Navigating through records
  • To search for a record, type a word you know is contained in that record in the navigation search box.
    Searching for a record

To save the current record:

  1. Select the Home tab and locate the Records group.
  2. Click the Save command. The current record will be saved.
    Saving a record

To delete the current record:

  1. Select the Home tab and locate the Records group.
  2. Click the Delete command.
    Clicking the Delete Command
  3. A dialog box will appear. Click Yes.
    Deleting a record
  4. The record will be permanently deleted.

Using form features

The exact procedure you use for filling out a form will vary depending on the content and design of the form you're using. The forms in your database might be similar to the examples in the two interactives below. Between them, they include most of the features you'll frequently encounter in forms.

Click the buttons in the interactive below to learn about a simple form.

doneedit hotspotsa simple form

Drop-Down Search Box

The drop-down search box allows you to search for existing records. Simply begin typing, and records that match your search will appear in the drop-down list. You can also browse existing records. Just click the drop-down arrow and scroll through the list of records.

a simple form

Text Box

Most data entry using forms is done with labeled text boxes. A text box is the Access equivalent of the blank space where you would write your information on a paper form. To enter data into a text box, simply click the text box and begin typing.

a simple form

New Record Button

Clicking the New Record button will create a new record with all fields cleared except for fields with a default value.

a simple form

Text Box with Validation Rule

If the database designer has created any rules specifying what type of data can be entered into each field, these rules will be reflected in the related forms. If you encounter a message like this one, click OK and re-enter the data following the instructions in the dialog box.

a simple form

Combo Box

In order to guarantee that users only enter valid responses, the database designer may use a combo box. A combo box allows you to enter data by choosing from a drop-down list of choices.

a simple form

More Form Buttons

Form buttons can provide quick and easy ways to carry out common tasks instead of using the commands on the Ribbon. From left to right, the buttons here allow a user to save, delete, and print the current record.

a simple form

Some forms may include additional options, like calendar buttons, drop-down lists, yes/no checkboxes, subforms, and embedded tables.

Click the buttons in the interactive below to learn about a more complex form.

edit hotspotsa more complex form

New Order Button

This customized button will create a new record with all fields cleared. Note that the subform is also totally cleared.

a more complex form

Drop-Down List

This allows you to select an existing customer from the Customers table. Simply begin typing, or select a name from the drop-down list. This form requires each order to be linked to an existing customer record, which helps to preserve the integrity of the database. If a customer is not in our database, we can add them with the Customers form.

a more complex form

Calendar Button

Because the Pickup Date field on the related form only accepts information formatted as a date, the Pickup Date text box includes a calendar button to ensure users can only enter a date in the desired format. Requiring dates to be entered in a consistent format ensures that the database will recognize each entry in this field as a date.

a more complex form

Yes/No Checkboxes

There are only two valid responses for the Pre Order and Paid fields in our related table: yes and no. Checkboxes give users an easy way to input this data. Simply click the checkbox for yes or leave it unchecked for no.

a more complex form

Add Item to Subform

This button launches the subform that allows you to add a new record to the embedded table. When using a subform like this, enter the data as you would in a normal form, then click Save and Close. The new record will be displayed in the embedded table.

a more complex form

Subform and Embedded Table

In our database, order items are stored in a separate table from the orders themselves. In order to enter and display order data, this form includes an embedded subform and table. This orders table can be viewed and searched like a normal table, but it only includes data linked to this particular order.

a more complex form

Challenge!

  1. Open our practice database.
  2. Open the Orders Form.
  3. Create a new record with the following data:
    Customer: Eric Oglesby
    Pickup date: February 14, 2017
    Order items: Cakes: Coconut (1)
    Notes: Write "Happy Valentine's Day!" with pink frosting
    Pre Order: Yes
    Paid: Yes
  4. Open the form named Customers Form.
  5. Find the record for customer Dwight Parker and make the following changes:
    Street Address: 190 Cook Street
    City: Chapel Hill
    Zip Code: 27514
    Email: dwightp@email.com

/en/access/sorting-and-filtering-records/content/

chatsimple