Access 97/2000: Forms

• What Are Forms?
• Why Use Forms?
• Views in the Form Window
• Creating New Forms
• Controls
• Selecting, Moving, and Sizing Controls
• Customizing Your Controls
• Changing a Form's Appearance
• Aligning Controls
• Adding Sections to Your Form
• Changing Section Sizes
• Setting Form Properties and Default View
• Including More than One Table in a Form
• Creating a Form/Subform

What Are Forms?

Like paper forms, Access forms collect and organize information. Forms give you a way to enter data into your database, display the data for review, and print it out. They are designed to make on-screen data entry and retrieval easier, as well as simplifying movement around your database application. With Access, you can present information in just the way that you want by combining text, pictures, lines, boxes, and color to create a fully interactive on-screen environment.

Why Use Forms?

A form provides an easy way to view data. Each of the objects in Access has certain specific advantages for viewing data. You use a table to look at records stored only in that table. You use a query to look at records in multiple tables or to see only portions of your data. To group your data and present it in an attractive printed format, use a report. But to make your data easy to see, enter, retrieve, and edit, use a form. A form provides an efficient way to enter data because rather than typing values into each field in a table, you can create drop-down lists to select the value you want or even write a macro to fill in a field value based on what is entered in a previous field.

Forms do not store the data they present. They keep only your design parameters, allowing you to tinker with it without affecting your data. Once you save the form design, it will stay the same until you modify it; however, you will retrieve into the form the current data from the underlying table or query. Forms can be used to retrieve information from more than one table but they must be based on a single underlying table or query. So, if you want to pull data stored in other tables or queries you will need to use subforms.

In Access, form design tools help you create attractive and functional forms that:

• present your data using specified fonts, colors, shading, and graphical effects
• look like the paper forms that you already use
• calculate totals or do arithmetic
• contain graphs or pictures
• display data stored in more than one table
• automate common tasks by using macros

Views in the Form Window

When you create or edit a form's layout, you work in the form's Design View . In addition to Design View , three other views are useful when working with forms: Form View , Datasheet View and Print Preview . You can switch between the different views by selecting them from the Form Design or Form View toolbars or from the View menu.

• Form View allows you access to all the available fields for one record at a time.
• Datasheet View allows you to view, enter, and edit information for available fields while looking at several records at once. The Datasheet View is limited by screen width and its inability to display pictures, OLE objects and command buttons (all these are visible in Form View ).

Design View

Design View is the most complex view for forms. Looking at a blank form in Design View is like looking at a blank canvas in an art studio or a blank sheet of paper at a writing desk. It requires boldness. Access gives you tools to use this potential and make your ideas appear the way you envision. By using the rulers at the top and left edges of the form window, the Design View toolbar, the palette, and the toolbox, you can create an attractive and useful form without searching through menus or remembering complex strings of keystrokes.

Creating New Forms

You create a new form by selecting Form in the Database Window and clicking on the New button. You must then select a table or query to base the form on. At this point, you can choose between using the Form Wizards or working from a blank form.

The Form Wizards

These are the most helpful wizards in Access because they do a lot of the basic formatting tasks and allow you to concentrate on refining special features. There are four basic types of wizards to choose from:

• AutoForms. These forms automatically display every field in the underlying table in either a datasheet, columnar, or tabular display. They are accessible from the New Object button on the toolbar. AutoForm: Columnar displays the values in one column with each field on a separate line. AutoForm: Tabular displays several records at the same time in rows and columns. AutoForm: Datasheet displays the values from the underlying table/query in datasheet view.
• Form Wizard . This is the most flexible wizard available. It will ask you to select all of the fields that you want to include on the form, and then prompt you to choose the layout you think will work best. Unlike the AutoForm options, it requires you to make decisions. However, it is also the only option that allows you to work with fields stored in multiple tables to create a main form and a subform. A Main/Subform form shows a one-to-many relationship between the data in the main form and the data in the subform. The main form is in a single-column format, and the subform is either in a datasheet or tabular layout.
• Chart Wizard . This form displays the selected data as a graph by using Microsoft Graph.
• Pivot Table Wizard . This wizard creates an Access form with an Excel Pivot Table . A Pivot Table is an interactive table that can summarize large amounts of data with the format and calculation methods you choose.

If you choose not to use the FormWizards, you will start with a blank form. You will need to place all of the desired fields on the form and decide what kinds of controls you need.

Controls

Forms use graphical objects, called controls, to link the object back to the underlying table or query. Three important controls are text boxes , frames , and labels. Text boxes display names and numbers stored in your underlying table or query. Frames show pictures and graphs. Labels display text as titles or other markers that describe data, but are not linked to information in the underlying table or query. Other kinds of controls, like lines and rectangles, visually organize the information and group data on-screen. There are three types of basic controls in Access: unbound, bound, and calculated.

• Unbound. An unbound control allows you to display information that is not stored in the database-- the title of the form or a descriptive label. This kind of control does not have a data source. You use unbound controls to enhance the presentation and clarity of your form by adding titles or descriptive labels, lines, and pictures.
• Bound. A bound control is always linked to information stored in the form's underlying table or query. A text box is always a bound control. You could display a person's name from the Name field in a Mailing List table. The text box would get the name from the Name field because you have bound it to that field. You can also use a bound control to display a picture if it is stored in the underlying table.
• Calculated. Calculated controls are a subset of bound controls, but they do not present information as it appears in a table or query. Instead, they operate on data taken from the table or query by using an expression--a combination of operators such as (+, -, and =), control names, field names, and constant values. For example, the following expression would combine a table's FirstName and LastName fields to present them both together: =FirstName + ì î & LastName.

Note: Access's on-line help has more detailed information on building expressions.

The Toolbox and Controls

The Toolbox is a special kind of toolbar that contains buttons allowing you to select the type of control that best suits your needs. When you need to create controls, the toolbox gives you quick access. Since the same toolbox is used for designing both forms and reports, you may not find all tools useful for all forms.

The toolbox should be visible when you enter the form's Design View . If it is not visible, then you can turn it on by selecting Toolbox from the View menu (or by selecting the Toolbox icon on the toolbar). The toolbox controls belong to five major categories: text display and entry, presentation of yes/no options, list boxes, graphics and subforms, and layout enhancement tools.

Text Boxes and Labels

Text boxes and labels are the default tools for looking at information in an Access form or report. Text boxes display the information either entered in a field or calculated on the form, while the label is a caption or descriptive text. All labels are unbound controls, but they can be attached to other controls. Every text box you create on your form will automatically have an attached label. You can use labels to create titles and headings.

Yes/No Options

The toolbox contains four different kinds of controls for displaying Yes/No values. Check boxes and option (or radio) buttons when turned on or selected indicate a Yes value. A check box contains a check mark when turned on, while an option button has a dot when selected. Toggle buttons indicate a choice between two values. You can change the face of a toggle button to display your own text. An option group shows which option was selected from several alternatives. Option groups frame a set of either check boxes or option buttons.

Note: only one option in the field can be selected for each record.

Drop Down Boxes

Access's toolbox contains two varieties of drop down boxes: a List box and a Combo box. These controls display a series of values from which you can select. They are most useful when entering data. You can take the values for listing in the boxes from existing values in a table or query or from a list you specify using the control's properties. The most effective way to take values from an existing table is to create a query that extracts all the unique values from a table and sorts them.

Graphics Objects and Subforms

Forms can be enhanced by including different kinds of graphics objects, or including other forms inside of the main form. The Graph tool allows you to create a graph in Microsoft Graph and place it on your form. The Subform/Subreport tool lets you insert a pre-existing form into your current form. The bound and unbound object frames allow you to insert onto your form pictures or other OLE objects.

Layout Enhancement Features

You can improve the appearance of your forms by including lines, boxes, or page breaks. The line and box tools are designated sections of your form that you want to separate visually from other sections. Page breaks allow you to control how much information goes on one page of a each form.

Selecting, Moving, and Sizing Controls

Access has easy drag and drop movement of controls in forms and reports. You select a control by clicking on it. You can also select several adjacent controls by "lassoing" them. To lasso, you need to click in an open space on the form, then drag the pointer through all of the controls that you want to select. Make sure that you start lassoing from a point on the form that will allow you to drag through all of the desired controls without intersecting any others. To select controls that are not adjacent, you begin by clicking on the first control, then select subsequent controls by clicking on them while holding down the <Shift> key. Once selected, you can move a control and its attached label anywhere in the form by dragging it to a new location.

If you place the cursor on a control's border, you will see one of three options. An open hand means you can move both the control and its attached label. A hand pointing with the index finger means that you can move either the selected control or label, but not both. A double-headed arrow means you can re-size the control. You then move or re-size by holding down the left mouse button and dragging the control to its new location or size.

Customizing Your Controls

In Access, properties determine the characteristics of fields, controls, tables, queries, forms, and reports. Every control on a form has properties. Control properties determine the structure and appearance of the control and the characteristics of the data it contains. For instance, using a control's properties, you could set properties that would format all displayed values as currency and in bold type. To modify a control's properties by changing its property sheet, open the property sheet by:

1. Double clicking on the control (unless you're using subforms/subreports or unbound object frames).
2. Selecting the control by clicking on it. Now either choose Properties from the View menu, or click the Properties button on the toolbar.
3. Right-clicking on the control and selecting Properties from the drop-down menu.

Where Control Properties Come From

A bound control in a form inherits the same property settings that went with the field in the underlying table or query. These settings include the Format, InputMask, and DecimalPlaces properties. If you set the input mask for a telephone number to be !(000)000-0000, then your form would automatically include the parentheses and dash. If you create a calculated control on the form, then it does not inherit any properties from the underlying table or query.

Changing a Control's Properties

Changing a control's property setting does not affect the property setting for the underlying field. You can set the appearance to be different in a form, but creating a difference usually leads to a cluttered appearance. Most of the time for a uniform appearance, it is better to set the Format, InputMask, and DecimalPlaces properties in the underlying table or query. The exception is calculated field properties that you will want to set to insure the desired appearance of your data in the form.

You customize controls by changing their properties on the property sheet. Access gives you several property categories. All Properties shows all of the control's properties. Data Properties deal with the characteristics of data displayed in the control, like the default data value, format, or number of decimal places. Event Properties specify a macro or module to be executed when an event occurs such as pressing certain keys, clicking or double-clicking the control, or changing data. Layout Properties define appearances like height, width, color, if the control can shrink and grow with the data, and font characteristics. Other Properties show the control's name and the description that appears on the status bar.

Note: You select a property by clicking on that option in the property sheet. If you click on the property and an arrow appears, you can click the arrow to display a list of settings. Otherwise, type an entry in the box.

Changing Properties for Multiple Controls

If you want several controls to have the same properties, such as height, width, or color, you can save time by changing the controls as a group rather than individually. To change the property of any control, display the property of any control. Then, select the controls you want to change by holding down the <SHIFT> key and clicking on each one. Change the desired properties in the control sheet.

Changing a Form's Appearance

Once you have created a form, you need to remember a few basics about working with controls in Access. First, for the best way to create and position controls on your form, use both the Toolbox and the Form Design Toolbar . The Toolbox, the Field List for the underlying table or query, and the Properties box can be displayed for easy access. You can turn all of these options on by selecting the appropriate buttons from the Form Design toolbar or from the View menu.

The easiest way to create a new control is by selecting the type of control you want in the Toolbox, and then clicking in the form to create an unbound control. You can then bind the control to a field in the underlying table or query by filling in the Control Source property with the appropriate field name. The best way to create a new control is to combine these two actions into one. When the both the Field List and the Toolbox are displayed, you can select the control type that you want to add in the Toolbox and drag the field you want bound from the Field List to the form's design window.

Aligning Controls

Access comes with tools that make it easy to get all the controls to their desired location.

To Select Controls:

1. Select a control by clicking on it.
2. Select adjacent controls by "lassoing" them by clicking in a blank space on the form and dragging.

To align controls use the Snap To Grid command in the Format menu. When this command is turned on, Access automatically aligns controls to the grid. When you have selected multiple controls, you can make the alignments match up. Under the Format menu, select whether you want to align controls with each other or with the grid underneath the form.
 

To Align One or More Controls to the Grid

1. Select the controls you want to align.
2. From the Format menu, choose Align, and then choose To Grid . Access will then align the upper left hand corner of each control to the grid.

To Align Controls Relative to Each Other

1. Select the controls you want to align.
2. From the Format menu, choose Align, and then Left, Right, Top, or Bottom depending on which edge you want to align. Left aligns the edges of the controls with the edge of the leftmost control. Right, Top, and Bottom do the same with the edge of the right, top, and bottom-most control.

Note: When you are aligning controls relative to each other, select only controls in the same row or column.

Adding Sections to Your Form

You can increase a form's effectiveness by adding sections. The basic section divisions are Form Headers and Footers, Page Headers and Footers, and Details. You can adjust the size of sections in your form, change section properties, and add whatever controls you want. If you want to use a form to both enter and print data you can add a header that appears only in Form View and another that appears when the form is printed.

Form Headers and Footers appear at the top and bottom of every page when the form is displayed in Form View . They display information such as a title, column totals, or column headings at the top or bottom of every page. When a form is printed, the Form Header appears at the top of the first page, and the Form Footer appears at the bottom of the last page. You can prevent a section's Form Header or Footer from printing by setting the DisplayWhen property to Screen Only . Page Headers and Footers appear only on printed forms. Details display either one record or as many records as will fit at once on the screen or page.

Changing Section Sizes

You can increase or decrease the height and width of a section. In Design View , use the rulers at the top and left of the form to determine how much space you want. You can change the height of sections independently, but a form has only one width. When you change the width of a section, you change the width of the entire form.

To change the size of a section

1. Place the mouse pointer on the bottom or right edge of the section you want to adjust. The pointer will change from the standard white arrow to a black, double-headed arrow when you are on the edge.
2. Drag the edge until the section is the size you want.

Setting Form Properties and Default View

Forms have properties just like sections and controls. Form properties affect overall form appearance and behavior. You can display a form's property sheet by double clicking in the gray area outside the form or the white box where the rulers meet. With an open property box, select the property category and set the properties.

DefaultView property is useful because it changes the way a form looks when you first open it.

• Single form specifies a display of one record at a time on the screen and is useful for intense data entry.
• Continuous form lets you see several records on-screen at once and is useful for displaying as many records as you want and can.
• Datasheet option displays the data in a rows and columns. Datasheets are usually used to display the "many" part of a one-to-many relationship.

Including More than One Table in a Form

You use a subform, a form within a form, to include information from more than one table in a form. Access calls the primary form the main form while the form within the form is the subform. In the sample form on the first page of this handout, the main form displays information about the artist, and the subform displays titles recorded by that artist.

The main form and the subform in this type of form are linked. The subform displays only records related to those displayed in the main form. Subforms are most effective when you want to show data from tables or queries with a one-to-many relationship, like one artist with many paintings. In the example, you can see all 48 pieces that artist Barrett Fiser has created.

Creating a Form/Subform

The easiest way to create a form/subform is use the Main/Subform Wizard to build your new form. When you create these forms without this wizard, you must design the main form and the subform separately. Then you insert the subform into the main form. You design your subform and save it just as you would any other form, and then you add it to the main form. The easiest way to link a main form with a subform is to choose the Form Wizard when you create a new form. By simply following the instructions on-screen, you can add all the fields to the form and get your desired layout. Then, all you have left is to go back into the form's design view and tweak the design to match your needs.

To Manually Add a Subform to the Main Form:

1. Open the main form in Design View .
2. Switch to the Database Window (press <F11>).
3. Drag the subform you want from the Database Window and put in the desired place on the main form.
4. Move the subform control and adjust its size to your tastes.
5. Switch to Form View to see the results.

Note: You can make changes to the subform while you are working in the main form's Design View by double clicking on the subform control. This action will take you into the subform's Design View.

To Set Properties to Link the Main Form and Subform:

In many cases, Access will automatically set up the links between the tables. In other cases, you have to set the properties yourself. For Access to set the links both the main form and the subform must be based on tables (not queries) that have an established relationship. The main form must also be based on a table with a primary key. The subform has to contain a field with the same name as that primary key and must have the same or a compatible data type. You will have to link the forms yourself by specifying the linking field or fields.

1. In the main form's Design View, open the subform control's property sheet.
2. Set the LinkChildFields property to the name of the linking field in the subform. To enter more than one linking field, separate the field names by semicolons.
3. Set the LinkMasterFields property to the linking field's name in the main form.