Geek Certs Blog

Try the Form Tool First

Go to the database window, click a table within the Navigation Pane, click the Create tab, and within the Forms Group, click Form to display the new form. If you don’t like the result, don’t bother to save the form. You have lost all of 30 seconds and can start again with the Form Wizard.

Anatomy of a Form

A form is divided into one or more sections. Virtually every form has a detail section to display or enter the records in the underlying table. You can, however, increase the effectiveness or visual appeal of a form by adding a header and/or footer. Either section may contain descriptive information about the form, such as a title, instructions for using the form, or a graphic or logo.

Floating Toolbars

A toolbar is typically docked (fixed) along the edge of the application window, but it can be displayed as a floating toolbar within the application windows. To move a docked toolbar, drag the toolbar background (or the toolbar’s move handle). To move a floating toolbar, drag its title bar. To size a floating toolbar, drag any border in the direction you want to go. And finally, you can double-click the background of any floating toolbar to dock it.

Snap to Grid versus Free Form Movement of Controls

Some form and report developers prefer having the freedom to have move and position controls with pinpoint precision that they control. If you are working in free form, after you have selected a control you can move it a pixel at a time by using any of your four arrow keys.

Sizing or Moving a Control and its Labels

A bound and/or unbound control is created with an attached label. Select (click) the control, and the control has sizing handles and a move handle, but the label has only a move handle. Select the label (instead of the control), and the opposite occurs; the control has only a move handle, but the label will have both sizing handles and a move handle. To move a control and its label, click and drag the border of either object. To move either the control or its label, click and drag the move handle (a tiny square in the upper left corner) of the appropriate object.

Use the Property Sheet

You can change the appearance or behavior of a control in two ways by changing the actual control on the form itself or by changing the underlying property sheet. Anything you do to the control automatically changes the associated property, and conversely, any change to the property sheet is reflected in the appearance or behavior of the control. In general, you can obtain greater precision through the property sheet, but we find ourselves continually switching back and forth between the two techniques. Every object in an Access database has its own property sheet.

Align the Controls

To align controls in a straight line (horizontally or vertically), press and hold the Shift key and click the labels of the controls to be aligned.

Troubleshooting Focus Issues

A common error occurs when entering new or adjusting existing records. This is because you probably attempted to move focus to another record or perform another action (such as change views) and a required field in the new record (set during table design) was not completed.

Error Messages - #NAME? Or #ERROR?

The most common reason for either message is that the control source references a field that no longer exists, or a field whose name is misspelled. Go to the Design view, right-click the control, click Properties, then click the All tab. Look at the Control Source property and check the spelling of every field. Be sure there are brackets around each field in a calculated control. For example, =[QualityPoints]/[Credits]. Don’t forget that the spelling is case sensitive and there should be no spaces in variable names!

The Lookup Wizard and Relational Databases

The simplest way to use the Lookup Wizard is to type the potential values directly into the associated field. It’s more powerful, however, to instruct the wizard to look up the values in a table, which in turn necessitates the creation of that table, in effect creating a relational database. Indeed, the true power of Access comes from databases with multiple tables.

Bound Controls and Inheritance

A bound control inherits its properties from the associated field in the underlying table. A check box, for example, appears automatically for any field that was defined as a Yes/No field. In similar fashion, a drop-down list appears for any field that was defined through the Lookup Wizard. All of the other properties of the control are also inherited from the underlying table.

Windows Themes and Rounded Command Buttons

It’s a subtle difference, but we prefer the sleeker look of rounded command buttons on our forms, as opposed to the rectangular buttons that are created by default. Click the Microsoft Office Home Button, click Access Options, and click Current Database. Check the box to Use Windows-themed Controls on Forms, click OK, then close the Window. Save the form you are working on, and then reopen it. You should see rounded buttons.

Multiple Controls and Properties

Press and hold Shift as you click one control after another to select multiple controls. To view or change the properties for the selected controls, right-click any one of the selected controls, then click Properties to display a Property Sheet. If the value of a property is the same for all selected controls, that value will appear in the property sheet; otherwise the box for that property will be blank. Changing a property when multiple controls are selected changes the property for all selected controls.

The Tab Stop Property

The Tab key provides a shortcut in the finished form to move from one field to the next. Calculated controls, such as GPA, are not entered explicitly, however, and can be bypassed by setting the Tab Stop property to No. AutoNumber fields can be bypassed in similar fashion. Note, too, that the order in which fields are selected corresponds to the sequence in which the controls were entered onto the form, and need not correspond to the physical appearance of the actual form.

To restore a left-to-right, top-to-bottom sequence, click the Arrange tab, in the Control Layout Group, click Tab Order to display the Tab Order dialog box.

Introduction

Many people who use databases are not technically inclined and prefer that the tables, queries, and other technical objects be managed by others. Part of our job, as database administrators, is to hide the complexity of the technology we use from users so that they can focus on their work rather than the tool or tools they use every day to get things done. Compounding this is solid evidence that users prefer to work in simple windows that make sense to them and flow with the logic of what they are trying to perform.

Database forms, therefore, are an excellent tool we can employ to as we work to satisfy users and at the same time ensure that quality data is entered into our tables. However, we must invest sufficient time in the form design stage, or the forms we create to help users will frustrate them.

You have probably experienced this yourself when completing a poorly designed Web page form. Do you find it annoying when you press tab and your cursor moves to the bottom of the page? On the other hand, do you not appreciate it when the fields on the form are in a logical order, of sufficient length (so that you can enter your last name, for example), the tab order is correct, and default values are supplied so you do not have to type in standard values (such as “United States”)?

The latter example is an example of good form design and these same guidelines hold true for forms we create in databases we administer. During this process, you will probably find, as you did when designing reports, that your most important tools will be paper and pencil.

Creating a User Interface

When you begin the process of designing forms, you must ask a number of questions of the people who will be using the forms. You should invest some time documenting how they do their work now, including the data they capture and the reports generated based on that data.

In the design of any product, users must be involved so that they accept what you provide to them. Too many designers have produced products that users simply will not use. When asked why this is so, users almost always answer something along the lines of ‘if they had asked me, I would have told them…’ So, user involvement is crucial to the successful implementation of the forms that you create.

Forms Basics

A form provides an easy way to enter and display the data stored in a table. You type data into a form and Access stores the data in the corresponding (underlying) table in the database. One advantage of using a form (as opposed to entering records in the Datasheet view) is that you can see all of the fields in a single record without scrolling. A second advantage is that a form can be designed to resemble a paper form, and thus provide a sense of familiarity for the individuals who actually enter the data.

A form has different views, as does a table. The Form view displays the completed form and is used to enter or modify the data in the underlying table. The Design View is used to create or modify the form.

All forms contain controls (objects) that accept and display data, perform a specific action, decorate the form, or add descriptive information. There are three types of controls bound, unbound, and calculated. A bound control has a data source (a field in the underlying table) and is used to enter or modify the data in that table.

An unbound control has no data source. Unbound controls are used to display titles, labels, lines, graphics, or pictures. Note, too, that every bound control in Figure 9.27a is associated with an unbound control (or label to identify the control). The bound control for First Name, for example, is preceded by a label (immediately to the left of the control) that indicates to the user the value that is to be entered. An unbound control is also used for the title of the form.

A calculated control has as its data source an expression rather than a field in the database. An expression is a combination of operators (e.g., +,-,*, and /), field names constants, and/or functions. An order’s sales tax is an example of a calculated control because it is computed by multiplying the order total by the applicable sales tax rate.

Properties

As previously stated, a property is a characteristic or attribute of an object that determines how the object looks and behaves. Every control in a form has its own set of properties, just as every field in a table has its own set of properties. The properties for a control are displayed in a property sheet.

There are many different properties (note the vertical scroll bar) that control every aspect of the label’s appearance. The properties are determined automatically as the object is created; that is, as you move and size the label on the form, the properties related to its size and position are established for you.

Other actions, such as various formatting commands, set the properties that determine the font name and size. You can change the appearance of an object in two ways by executing a command to change the object on the form, which in turn changes the property sheet, or by changing the property within the property sheet, which in turn changes the object’s appearance on the form.

AutoForms and the Form Wizard

The easiest way to create a form is by using the Form tool, which creates a form with a single mouse-click. Start by clicking a table within the Navigation Pane. Click the Create tab, and within the Forms Group, click Form, for example, and you are presented with a form that contains all of the fields in the underlying table or query. The Form Wizard gives you greater flexibility because you can select the fields you want and/or choose a different style from the default style provided by the AutoForm. The wizard asks you a series of questions, and then builds the form according to your answers.

Using the Form Wizard, you specify the underlying table and fields you want, the layout, and the style. After you create the form you can go to the Design view to further customize the form.

Modifying a Form

The Form Wizard provides an excellent starting point, but you typically need to customize the form by adding other controls (e.g., the calculated control for sales tax) and/or by modifying the controls that were created by the wizard. Each control is treated as an object, and moved or sized like any other Windows object. In essence, you select the control, then click and drag to resize the control or position it elsewhere on the form. You can also change the properties of the control through buttons on the various toolbars or by displaying the property sheet for the control and changing the appropriate property.

Consider:
1. Select a bound control and its associated label (an unbound control), click either the control or the label. If you click the control, the control has sizing handles and a move handle, but the label has only a move handle. If you click the label, the opposite occurs; that is, the label will have both sizing handles and a move handle, but the control will have only a move handle.
2. Size a control, click the control to select the control and display the sizing handles, then drag the sizing handles in the appropriate direction. Drag the handles on the top or bottom to size the box vertically. Drag the handles on the left or right side to size the box horizontally. Drag the handles in the corner to size both horizontally and vertically.
3. Move a control and its label, click and drag the border of either object. To move either the control or its label, click and drag the move handle (a tiny square in the upper left corner) of the appropriate object.
4. Change the properties of a control, point to the control, click the right mouse button to display a shortcut menu, then click Properties to display the property sheet. Click the text box for the desired property, make the necessary change, then close the property sheet.
5. Select multiple controls, press and hold the Shift key as you click each successive control. The advantage of selecting multiple controls is that you can modify the selected controls at the same time rather than working with them individually.

There is a learning curve, and it may take you a few extra minutes to create your first form. Everything you learn about forms, however, is also applicable to reports.