Creating Forms in Access

Forms in Access show a different view of our data than we've seen with tables and queries. Forms can be useful, even in a database with only one table, because we can show all the information for a record in an integrated space and make data entry easy and user friendly. The real power of forms begins when you use relational forms with information from multiple tables. This information can then be displayed in an intuitive way, more like forms found on the web, for example, rather than in table format.

We'll start by creating a simple form for our faculty.

Creating a Simple One-Table Form

If we start to create our form with a specific object selected in the navigation pane, Access will make its best guess as to what fields to include based on the selected object. Since our first form will be just based on tblFaculty, we'll select that first.

Step1. To select tblFaculty, in the navigation pane,

Click tblFaculty

NOTE: You do not need to open tblFaculty, just have it selected.

Step2. Click the Create tab on the Ribbon.

There are several options in the Forms group of the Create tab, but we're going to make use of the simplest option.

Step3. To create a simple form, in the Forms group,

Click

Access takes the fields in tblFaculty, and creates a basic form:

In addition, all of the fields are filled with information from the first record. Let's navigate through a few of the records until we find one with a photo.

NOTE: The exact layout of your form may be different, depending on your window size and screen resolution.

Step4. To navigate to Joanne Benavidez's record, on the keyboard, press:

Page Down key until the record for Joanne Benavidez is displayed

We can now see the photo that was attached to this faculty member's record. This is a distinct advantage that forms have over tables, in that photos that are attached can actually be viewed in a form.

There are a number of things that could be adjusted to improve this form, such as adjusting the column width or renaming the fields to make them more user-friendly. For now, we'll leave the form as-is, and save it.

Step5. To save the form, press:

Control key + S

Although the tab seemed to have the name tblFaculty, Access sees that this is a new object, so the Save As dialog box launches. We'll use the prefix "frm," which is the standard Access naming convention for forms.

Step6. To give the form a logical name, type:

frmFaculty, Click

Step7. Close frmFaculty.

Next, we'll create a relational form.

Creating a Relational Form

Relational forms can pull together data from multiple tables. To see that in action, we'll create a form based on tblDepartments.

Since tblDepartments is on the "one" side of the one-to-many relationship with tblFaculty, Access will assume that we want to pull information from both tables together.

Step1. Switch to the Create tab, if necessary.

Step2. Click tblDepartments in the navigation pane.

Step3. To create a relational form,

Click

NOTE: If the Form button is grayed out, make sure that you have tblDepartments selected in the navigation pane.

The form that is created has two sections. At the top, we see the department information. In the bottom half, we see information about the faculty that are in that department:

NOTE: The title of the form initially shows as tblDepartments, because that table was the basis for the form; we can change that title by clicking on it if we desire.

NOTE: Depending on your screen size and resolution, the gap between the subform record navigation and the form record navigation might be larger than pictured here.

The first department that is shown is the Anthropology department. As we saw earlier in the workshop, this department is brand new and does not have any faculty assigned to it in the database.

Let's use the record navigation to go to the next record. If we look closely, we can see that both sections have their own record navigations.

Step4. To navigate to the next department, at the very bottom of the form, in the record navigation for the main form,

Click

The next department, Biology, is displayed, and in the bottom section of the form we see the faculty from the department listed.

According to the subform navigation, there are 24 faculty records for the Biology department. We've been told that there's an error in our faculty data, so let's fix that now. We don't need to open tblFaculty, however; we can make the change directly in the form and it will save to the underlying table.

Changing Data in a Form

We've learned that Bella Cedillo's salary is incorrectly entered in tblFaculty. We'll make the fix to the data here in our form.

Step1. Scroll right, if necessary, until the Salary field is visible.

Notice that Access has maintained the frozen columns that we saved in the underlying table, so we can still see the names of the faculty members as we scroll.

Step2. To find Bella Cedillo's record, scroll down in the subform, if necessary.

Step3. To select the correct field, in Bella Cedillo's record,

Click in the Salary field, Press & Drag across "$61,000," type 81000 Down Arrow key

The information is changed in the form, but in addition, the change has just been made to the underlying table and all of the database objects that refer to that data.

To confirm this, let's reopen the last query we created and search for Bella Cedillo. With her previous salary value, she would not have shown up in the query, but she should appear now.

Step4. To open our most recent query, in the navigation pane,

Double-Click qrySalariesByRank>70,000Hired2000OrLater

The query opens in datasheet view. We could scan the records, or use the Find function to search for Bella Cedillo, but instead, we'll explore the field header sort and filter options.

Step5. To open the sort and filter menu for the last name column,

Click

Step6. To deselect all names,

Click the Select All checkbox

All names are deselected; now we can choose the specific name we wish to display.

Step7. To select Bella Cedillo,

scroll down if necessary, Click Cedillo, Click

We are returned to our dynaset, which now only displays the record for Bella Cedillo.

Step8. Confirm that the new salary is $81,000.

Let's close the query without saving the changes we've made, so the next time we open it, the results won't be limited to Bella Cedillo.

Step9. Right click the qrySalariesByRank>70,000Hired2000OrLater tab, choose Close.

Step10. In the dialog box, click No to close without saving.

Before we move on, we should save the form we created.

Step11. On the Quick Access toolbar, click Save.

Step12. To enter a name for the form, in the Save As dialog box, type:

frmDepartmentsAndFaculty, Click

Step13. Close the form.

To learn more about forms in Access, consider taking the course Access: Designing the Database Interface.

Next, we'll explore how to create a simple report using Access.