Access 2013

Introduction

Video: Creating Forms

Watch the video (4:08). Need help?

Creating forms for your database can make entering data more convenient. When you create a form, you can design it in a way that works with your database and that makes sense to you.

In this lesson, you will learn how to create and modify forms. You'll also learn how to use form options like design controls and form properties to make sure your form works exactly the way you want.

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

To create a form:

Access makes it easy to create a form from any table in your database. Any form you create from a table will let you view the data that's already in that table and add new data. Once you've created a form, you can modify it by adding additional fields and design controls such as combo boxes.

1. In the Navigation pane, select the table you would like to use to create a form. You do not need to open the table.
2. Select the Create tab, locate the Forms group, and click the Form command.
   Using the Form command to create a form using the Customers table
3. Your form will be created and opened in Layout view.
   The new form
4. To save the form, click the Save command on the Quick Access Toolbar. When prompted, type a name for the form, then click OK.
   Naming and saving the form

About subforms

If you created a form from a table whose records are linked to another table, your form probably includes a subform. A subform is a datasheet form that displays linked records in a table-like format. For instance, the subform included in the Customers form we just created displays linked customer orders.

We probably don't need to include this subform because we just want to use the Customers form to enter and review contact information. If you find that you don't need a subform, you can easily delete it. To do this, simply click it and press the Delete key on your keyboard.

An unnecessary subform in our Customers form

However, subforms are often quite helpful. Depending on the content and source of your form, you might find that the subform contains useful information, like in the example below. In our Orders form, the subform contains the name, quantity, and price of each item contained in that order, which is all useful information.

Our Orders form, which includes a useful subform

Adding additional fields to a form

When you use the Form command on an existing table, all of the fields from that table are included in that form. However, if you later add additional fields to that table, those fields will not automatically show up in existing forms. In situations like this, you can add additional fields to a form.

To add a field to a form:

1. Select the Design tab, then locate the Tools group on the right side of the Ribbon.
2. Click the Add Existing Fields command.
   Clicking the Add Existing Fields command
3. The Field List pane will appear. Double-click the desired field(s).
   
   Selecting a field from the source table
4. The field will be added.
   The added field

You can also use the above procedure to add fields to a totally blank form. Simply create a form by clicking the Blank Form command on the Create tab, then follow the above steps to add the desired fields.

Clicking the Blank Form command

To add a field from a different table:

You can also add fields from different tables in your database to the form.

1. From the Field List pane, click Show All Tables.
2. Click the plus sign + next to the table that contains the field you wish to add, then double-click the desired field.
   Selecting a field from another table
3. The new field will be added.

Adding design controls

Design controls set restrictions on the fields in your forms. This helps you better control how the data is entered into your forms, which in turn helps keep the database consistent.

Combo boxes

A combo box is a drop-down list you can use in your form in place of a field. Combo boxes limit the information users can enter by forcing them to select only the options that you have specified.

Combo boxes are useful for fields that have a limited number of possible valid responses. For instance, you might use a combo box to make sure people only enter a valid US state while entering an address, or that they only choose products that already exist in your database while placing an order.

To create a combo box:

1. In Form Layout view, select the Design tab, then locate the Controls group.
2. Select the Combo Box command, which looks like a drop-down list.
   Clicking the Combo Box command
3. Select the desired location for the combo box. A line will appear to indicate the location where your combo box will be created. In our example, we'll place it between the City field and the Add to Mailing List? fields.
   Selecting the location of the new combo box
4. The Combo Box Wizard dialog box will appear. Select I will type in the values that I want, then click Next.
   
   The Combo Box Wizard dialog box
5. Type in the choices you would like to appear in your drop-down list. Each choice should be on its own row.
   In our example, we are creating a combo box for the Add to Mailing List? field in our form, so we will enter all of the possible valid responses for that field. Users will be able to select one of three choices from our finished combo box: No, Yes - Weekly, and Yes - Special Events and Offers.
   Choosing how many columns to include in the combo box's drop-down list and typing the options that will appear in that list
6. If necessary, resize the column so all of your text is visible. Once you are satisfied with your list, click Next.
   Resizing the column that will appear in the combo box
7. Select Store that value in this field, then click the drop-down arrow and select the field where you would like selections from your combo box to be recorded. After making your selection, click Next.
   Selecting the field that will be affected by the combo box—choices made while using the combo box will be recorded in this field
8. Enter the label, or name, that will appear next to your combo box. Generally, it's a good idea to use the name of the field you chose in the previous step.
   Naming the combo box
9. Click Finish. Your combo box will appear on the form. If you created your combo box to replace an existing field, you should delete the first field. In our example, you might notice that we now have two fields with the same name. These two fields send information to the same place, so we don't need them both. We'll delete the one without the combo box.
   Deleting the field that the combo box will replace
10. Switch to Form view to test your combo box. Simply click the drop-down arrow and verify that the list contains the correct choices. The combo box can now be used to enter data.
    Testing the combo box in Form view

If you want to include a drop-down list with a long list of options and don't want to type them all out, create a combo box and choose the first option in the Combo Box Wizard, I want to get the values from another table or query. This will allow you to create a drop-down list from a table field.

A combo box that takes its values from a table

Some users report that Access malfunctions while working with forms. If you have a problem performing any of these tasks in Layout view, try switching to Design view.

Customizing form settings with the Property Sheet

Video: Customizing Form Settings

Watch the video (3:32). Need help?

The Property Sheet is a pane containing detailed information about your form and each of its components. From the Property Sheet, you can make changes to every part of your form, both in terms of function and appearance.

The best way to familiarize yourself with the Property Sheet is to open it and select various options. When you select an option, Access will display a brief description of that option on the bottom-left border of the program window.

Selecting a Property Sheet option to find out what it does

Pay close attention as you modify your form and its fields. It's possible to make subtle changes with the Property Sheet. Because there are so many options, it can sometimes be difficult to remember which one you used to modify each aspect of your form.

Modifying form settings

There are far too many options in the Property Sheet to discuss them all in detail. We'll review two useful ones here: hiding fields, and setting fields with dates to automatically fill in the current date. Practicing these procedures should also give you a sense of how to work with other Property Sheet settings.

To hide a field:

1. In either Layout or Design view, select the Design tab, then locate the Tools group. Click the Property Sheet command.
   Clicking the Property Sheet command
2. The Property Sheet will appear in a pane on the right. On the form, select the field you wish to hide. In our example, we'll hide the Customer ID field because we don't want any of our users to try to edit it.
   Selecting the field we wish to hide
   
3. In the Property Sheet, click the All tab, then locate the Visible option on the fifth row.
4. Click the drop-down arrow in the column to the right, then select No.
   Selecting "No" from the drop-down list in the Visible option
5. Switch to Form view to verify that the field is hidden.

To set a field to autofill with the current date

1. In either Layout or Design view, select the Design tab, then locate the Tools group. Click the Property Sheet command.
2. The Property Sheet will appear in a pane on the right. On the form, select the field you would like to automatically fill in the current date. This must be a field with the date data type. For our example, we'll select the Pickup Date field on our Orders form.
   Selecting the Pickup Date field
3. In the Property Sheet, click the Data tab, then select the Default Value field in the fourth row. Click the Expression Builder button that appears in the column to the right.
   Clicking the Expression Builder button for the Default Value option
4. The Expression Builder dialog box will appear. In the Expression Elements list, click the words Common Expressions.
5. In the Expression Categories list, double-click Current Date.
   The Expression Builder dialog box
6. The expression for Current Date will be added. Click OK.
   The Current Date expression
7. Switch to Form view to verify that the expression works. When you create a new record with that form, the date field you modified will automatically fill in the current date.
   The current date is automatically entered into the Pickup Date field when a new record is created
   

Challenge!

1. Open an existing Access database. If you want, you can use our Access 2013 sample database.
2. Create a form from the Customers table.
3. Delete the subform.
4. Create a combo box.
   • Add the following choices to the combo box:
     • Raleigh
     • Durham
     • Hillsborough
     • Cary
     • Chapel Hill
     • Garner
     • Charlotte
   • Store the value in the City field, and label the combo box NC City List. Click Finish when you are done with the Combo Box Wizard.
5. Switch to Form view, and click on the drop-down arrow in the combo box you created. You should see a list of cities.