CS130/230 Lecture 11

Introduction to Access

 

Thursday, January 22, 2004

 

Save or print a pdf version of this document

Forms

In creating and entering information into our tables, we have so far been using the datasheet view. Another way to enter new data and edit existing data is to use forms. Forms are more user-friendly and are more suitable for the end-users of a database than the datasheet view of a table.

AutoForms

The simplest way to create forms for any table is to use the AutoForm object. Unfortunately, the forms created by AutoForm are not customizable. However, they do serve to illustrate the way that forms work.

Problem

Let us create an AutoForm for the customer table. Select the customer table (you do not have to open it), go to the Insert menu and select AutoForm. You can also find AutoForm on the toolbar. It is the New Object toolbar icon.

 

Once you have created your form, add a couple of new records to the table. You can just make up the data.

 

Question: What are the advantages/disadvantages of using AutoForms over the datasheet view?

 

Once you have created and saved your form, you can switch between the various views of the table from the View menu.

Creating Reports

Sometimes it is necessary to print out the contents of a table. You can do this directly by clicking on Print Preview. What you’ll notice is that the data does not all fit on one page. It might be necessary to change the orientation of the page from portrait to landscape.

 

You should now be able to see the whole contents of the table on one page.

 

Question: Do you think there are any problems with the printed page? What additions might you like to add?

 

Reports are Access’ way of allowing the user to change the format of a printed table. For the next problem we will be creating a report using the report wizard.

Problem

Create a report that displays the Customer Name, Amount Paid and Current Due.

More on Access Queries

Zoom for calculated fields

When entering the calculated fields, as we did yesterday, Access provides a useful zoom option so that we can see the whole calculated field on one line. You can open the zoom window by right clicking on the field in the design view of a query and selecting zoom.

Grouping

Statistics are often used with grouping. For example, we might just want to find the statistics of a group of records.

Problem

What is the sum of the Amount Paid to customers grouped by the driver number? In other words, the customer of which driver get paid the most?

Validation Rules in Queries

Nothing that we have done so far ensures that user’s only ever enter valid data into tables. The database designer needs to specify the validation rules that distinguishes the data that is valid from the data that is not.

 

Validation rules can indicate:

-      A required field

-      The entry is within a certain range of values

-      Legal values

-      The default value of a field

Problem

Let us go back to the customer table. Let us make the address, city, state, amount paid and current due be fields that are not required (could be left blank), while the customer number, name, zip and driver number should be required fields.

 

You should also specify a range for the amount paid and the current due. Both of these should be positive amounts. You should therefore type >0 in the validation rule box. In the validation text box you type the message that appears to the user when they enter a negative number. Test your range now by adding a record to your table and trying to type in a negative amount.

 

Let us add another field to the customer table to store the customer type. The type must be EDU, MAN or SER. We must therefore specify that these are the only possible legal values in this field. We will again do this in the validation rule box. Type =”EDU” OR “MAN” OR “SER”. Test that the rule works correctly by trying to enter some incorrect values.

Referential Integrity

Yesterday we learnt that tables should have primary keys. We also saw how we can join two tables together. Joining two tables requires that a field from one table match a field in another table.

 

A foreign key is a field in one table whose values are required to match the primary key for another table.

 

Question: Do we have a foreign key in our customer and driver tables?

 

The property that ensures that the value in a foreign key must match another table’s primary key is called referential integrity.

 

Before we specify referential integrity, we must specify a relationship between the two tables. Access will then prohibit any updates to the database that would violate the referential integrity.

 

The type of relationship between two tables specified by the relationship command is referred to as a one-to-many relationship. This means that one record in the first table matches many records in the second table, but each record in the second table is only related to one record in the first table.

Problem

Let us create a relationship between the driver number in both tables.

 

From the Tools menu select Relationships and add both tables to the relationship. Drag the driver number from one table to the driver number on the other table. In the box that appears check the Enforce Referential Integrity box.

 

Question: describe how the one-to-many relationship in this example.

 

Let us try to add and delete records and observe how referential integrity works.

Subdatasheets

Once you have a relationship between two tables, it is possible to view the fields in which the primary key appears as a foreign key.

 

We know this by the + sign that has appeared to the left of the driver number. Click on the + sign to see what I mean.

Complex Forms

The form that we created at the start of class was created using the AutoForm option. This created a standard form where all the fields were listed on the left hand side. As you can imagine this is not an entirely useful or pleasing form.

 

The easiest way to create our own custom forms is to use the form wizard to create a basic form for us, then we can modify it as we see fit.

Problem

The goal is to create a form like the one below for the driver table.

 

 

Once you have created the basic form above, try and enhance the form by adding command buttons and a combo box for easy navigation. To create Command Buttons, select the Design View and using the Toolbox, drag a button onto the screen giving it the functionality of “Next Record.” Do the same for “Previous Record”, “Add Record”, “Delete Record”, and “Close Form.”

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Since we can have images in forms we are going to add a couple of fields to the driver form. Let us add one field for the driver’s picture (you can just download any picture from the Internet to use) and the driver’s Internet home page.