Creating Forms

The purpose of a form is to have a convenient way to enter data into tables. Using a single form you can enter data into one or more tables or queries. A form does not have to come from a single table or query.

Auto Form

To create a form, click on a table or query and then click on the New Object toolbar button drop down list and choose Auto Form. When you do, a form will automatically be created for you from a single table or query.
 
 
 
 
 
 

Form Wizard

If you would like more options in creating a form you should use the Form Wizard. To open the Form Wizard you can either select this option from the New Object toolbar button or click on New once you are in the Form Tab of the datasheet.

Once inside the Form Wizard, you are asked to choose which table or query you would like to create a form from. Click on the drop down list and select the appropriate option then hit the NEXT button. You should then see that the table or query you selected is listed at the top and below that is a list of the entire field names within that object. To select which fields you would like to have appear in the form, select a field and choose the > button. That field name should then appear on the right hand side. Now, once you are finished selecting all of the fields you are interested in from that particular table or query, you can then click on the Table/Query drop down list and select another object. When you do, follow the same steps as above to select which field names you would like to appear in your form. This way you can create a form from more than one table or query. Follow the instructions provided in the Form Wizard to finish your form. Note, you can see a preview of any of the options listed, by clicking on them.

Design view

Creating a form in Design View is a manual option. There are no wizards that accompany this process. Design View option is not recommended for beginners.

Working in Design View

Design view is very useful when you are creating forms and reports. In design view you can change the font, font size, add boarders, resize cells, etc. so your document is tailored to your preferences.

Controls-Bound/Unbound

The objects in a document are called controls. There are two types of controls, bound and unbound. Bound controls are objects in a document that get information from another part of your database. For example, bound controls are cells that display names, dates, addresses, calculations, etc. Unbound controls are objects such as a boarder, page numbers, and the titles of the document. Do not be too concerned with these words. These words maybe helpful to know when they are mentioned in help menus or manuals but do not get hung up on them.

Sections in Design View

When you open up a form or report in design view you will see five different sections of the document. The first is called the Header. Here you can place information that you want displayed on the top of the document, a title for example. Headers will only be placed on the first page of the document. Likewise, information that I placed in the Footer is displayed only once, on the bottom of the last page.

Next there are Page Headers and Page Footers. Information placed in these sections is displayed on every page. Page Headers are shown at the top of the page, such as column headings, subheadings, and dates. Page Footers are displayed at the bottom of the page, like, page numbers, file paths, or graphics.

In case your Headers and Footers are not shown, choose Page Header or Header and Page Footer or Footer from the View Menu.

The last section in Design View is called Details. It is here that the actual data is displayed. This is the heart of the form or report.

Deleting Controls

If you used Auto or the Wizard to create your report or form you may decide later that you do not want certain controls. To delete a control, make sure you are in design view and have selected the control. When you do, tiny boxes appear around the object, meaning it is selected or highlighted. Once you have selected the object hit the DELETE key on your keyboard.

Adding Controls

To add text anywhere in the document, click on the Label toolbox button. (If your toolbox is not displayed, click on Toolbox from the View Menu). After clicking on the Label button, your mouse turns into a big letter A. Place your mouse where you want the text to be inserted and click. Without lifting your finger off the mouse button, drag the mouse to the proper text box size. When you do, a white square with a cursor appears. Access is letting you know it is ready for you to start typing. As you type, the words stay within the width size of the textbox you drew. Read below to find out how to resize and/or move the textbox.

In case you wanted to place an additional field in your document click on the field list toolbar button. A list of all the fields from the table that the report or form is being created from appears. To add one of these fields, click the field name from the list and drag it to the place you would like the field to be displayed. A label, as well as a field box appears. The label provides the name for the field and the field box is where the data will appear in datasheet view. Notice that the label for the field says something like Text20. If you click the label once, it becomes highlighted. If you click the label a second time, a cursor appears. Once the cursor is present backspace over the text and then type in a new name.

You can add many different things to your form or report. To insert a rectangle into you document, click the rectangle toolbox button. When you do, your mouse pointer changes into a small white rectangle. Click and drag the rectangle to the desired size and location. Read below to find out how to change the size or to change the colors of the rectangle.

Moving/Resizing Controls

To move a control, you first need to select it. (Remember to select or highlight a control you click on it once and you should then see tiny boxes all around it). If you click and drag on a control, when your mouse pointer is a hand, you can then place the control anywhere on the document.

To resize a control, select it and place your mouse on top of the tiny squares that appear. Once you have done that, your mouse should turn into a black double sided arrow. Only when you see this arrow, you click, hold, and drag to resize. Notice that not all of the tiny boxes around a field control are of equal size. In the top left corner of the field cell and the field label you will see a slightly larger box. When you click and drag your mouse on top of these larger boxes only that specific part of the field control will move. Since a field box and label are setup to move together, if you want to move either one separately, click on the larger square.

Formatting Controls

To change the font size, font, colors, etc. of any control with text you first select the control. Next, the toolbar has options for fonts, font sizes, alignment, bold, italic, and underline. Note you do not have to select the text within the control in order to format it. Selecting the control itself is sufficient. Once the control is highlighted you format it in the same way as in Word.

To change the color of a control, you must first select it. On the toolbar, you have a Fill option. When you click on this button you get to choose any color from the color palette. The color you choose will be the background color of the control. To color the text itself, choose the Font Color toolbar button. There is even an option called Line Color that can change the color of the control’s outline or choose the Line Border button to choose the width of a control’s outline. A toolbar button called Special Effects can change a field box to make it look risen, sunk, or even 3-D.

Note if you right-click on a control you can make formatting changes directly from the pop-up menu.
 

Relationships

In order to use the full power of Access you should become familiar with relationships. In brief, relationships let Access know how tables and queries are related to each other. Some terminology is listed below.

Primary Key

You most often relate two tables or queries by using the primary key field. A primary key is a field that uniquely identifies a record. For example, if I had an address book, I would associate each person in my address book with a unique number, which will never be used by any other record. Now that a number is associated with each record, I could simply can use this number to refer to the record and all the information contained within it.

Adding tables and queries

To setup relationships, click on the Relationships toolbar button. When you do, a window pops up asking which tables or queries do you want to create relationships for. If you do not see this window, then click on the Show Table toolbar button. Click on the table or query you would like to make a relationship for and choose the Add button. Note you have to click on the query tab to view the list of queries. When you have selected all of the tables and queries choose close.

Next, you will relate the tables or queries. When you close out of the add tables and queries window, you should see each of the tables and all of their field names appear in small boxes. To relate them, you must have a primary key field in one of the tables. For example, in my address book (see table one below) there is a primary key field. In the table I want to relate to this table, I also have a number field and type in the number that refers to a specific record from the other table. The number field in Table 2 is referred to as the foreign key and the number field in Table 1 is called the primary key. Don’t let the words scare you.

Now that I have setup my tables, I need to let Access know that I want these two tables related, meaning let Access now that the numbers in Table 2 refer to the numbers in Table 1.

TABLE 1:
 

Number (Primary Key Field)	Name	Address
1	Betty Crocker	132 Euclid Avenue Euclid, OH 44123
2	Pam Lee	790 E. 177th Street Cleveland, OH 44109
3	Amy Goode	5729 E. 185th Street Cleveland, OH 44109

TABLE 2:
 

Number (Foreign Key Field)	Spouse Name
1	Henry Crocker
2	Sam Lee
3	Bob Goode

Going back to our list of tables in the Relationship window, I can relate two objects by clicking on the number field from one table and dragging it on top of the number field from another table. When you do, Access pops up a window to ask you specific questions about this relationship.

Enforce Referential Integrity

The first option is to check "enforce referential integrity". Now this sounds a lot scarier than it actually is. When this option is checked, Access helps you to not mess up the relationship that you have created. When this is checked, you can not delete a record if there are related records in another table. You can not change a primary key field if there are related records. You can not type in a value in the foreign key field if that value does not exist in the primary key field. For example, if I typed in 4 in the number field of Table 2, Access will beep at me and not let me do that since number 4 does not exist in Table 1. It is a good idea to always set the relationships before entering in data to avoid mistakes.

Null

If for some reason you need to type in a record in the foreign key table that is not related to the primary key table, you have the option of typing in NULL in the foreign key field for that record. For example, in the above address tables, I could add a record in Table 2 (the foreign key table) for Karen Jones, along with the spouse name. Note, that Karen Jones does not exist in the Table 1 (the primary key table) so according to the rules of enforced referential integrity I should not be allowed to add this record. However, if I type NULL in the Number field of Table 2 (foreign key field), Access will allow me to type this record in. This command comes in handy when you need to bend the rules a bit. Null values can be a bit complicated, but at least know it exists.

Cascade Update

There are helpful ways to maintain these rules but have a bit more flexibility when you are editing your tables. When Cascade Update is chosen, you are allowed to change the primary key field values. When you do, the values in the foreign key are automatically changed also.

Cascade Delete

When you choose Cascade Delete option, you can delete a record in the table that has the primary key field (which is called the primary key table) and all the other related records in other tables will also be deleted. So be careful with this one.

This all sounds more complicated that it really is. Once you get used to all the new terminology you will see it all makes sense.  Just keep practicing.

Back to Access Syllabus