forms for data entry and editing

Although it's possible in Access to fill tables directly from the grid in "Table mode", it's usually not done that way.

To populate our tables and then to edit them we use a new object: a Form.

The form object is used to display the information in a table or query in an organized fashion. A form displays information from the table one record at a time. You see all the fields from one customer, for example. The form is tied to the table so that any field that is new or changed is directly reflected in the table.

In addition to being clearer and more flexible, using forms will allow us to create a Navigation form so that the user can open tasks (forms or reports) from a menu structure. Before Access 2010 the Navigation form was called a Switchboard.

creating the simplest form

Click on the table that you want to use for the form, then Create --> Form and that's it. It couldn't be easier!

Then you can format the form - change colors, change and resize fonts, move boxes around, etc. Then save the form with the same name as the table. Don't worry - Access knows that there are 2 objects with the same name and for you, you will know that the Vehicles form lists the Vehicles table. By the way, the only objects that can't have the same name are tables and queries.

You'll notice that at the bottom of the Vehicles form there is a subform showing invoices. That's because there is a relationship between the Vehicles and Invoices tables. All invoices that the vehicle appears in will be listed in the subform.

In the example shown, the form tell me that the vehicle is owned by customer #1. But who is that? It would be nice to be able to see the customer's name and phone number in addition to customer ID.

But that means getting information from two tables into one form. To do that we use an intermediate - we'll create a query to get the information from two tables and then we'll build the form from the query.

To create the query:
  • use all the fields from the Vehicles table; this is important because you want to be able to edit any information in Vehicles, therefore it has to be on the form
  • use only the fields you think might be useful from the Customers table: name, phone and email, for example
  • perform calculations you need in the query - concatenation, age calc and so on

Now, create the form from the query.

The information on the owner is for reference only, it should not be changed in this form - there's a Customer form for that. So, highlight it and lock it so that it cannot be altered.

It may require some practice to master the formatting of forms. Don't forget that the right-click and the Undo will come in very handy!

Notice that we've lost the subform showing related invoices. That shouldn't be a problem most of the time. But just in case I need it later, I'll keep the first form I created as Vehicles and I'll save this new one as VehiclesPlus. I can then use whichever form is needed at any time.

Now we should do the same with all the other tables. Every table that may eventually need maintenance (adds, deletes, changes) should have a form associated with it so that it can be called from the navigation menu.

This does not apply to the transaction tables, Invoice and Invoice details. Those tables will require a special form called a transaction form that we'll cover in the next lesson.

For the Employee table we create a query so that we can display "Experience" that we'll calculate from [Hire date] using the Age() function.