Базы данных • Интернет • Компьютеры • Операционные системы • Программирование • Сети • Связь • Разное

Ch 6 -- Forms and Reports

Microsoft® Access 97 Quick Reference

- 6 -
Forms and Reports

You use forms mainly for inputting data and for viewing the data on-screen. You use reports for printing lists and summaries of your data (including charts). You construct forms and reports similarly.

The elements you add to a form or report are called controls. Controls can be graphics, text labels, pictures, and other static elements that do not change as you move from record to record; as well as text boxes that do change when you move from record to record. Controls can also be used to display or enter data, or perform and display calculations. Controls can be buttons that perform actions; containers like subforms (datasheet grids of related records to the main form); or objects that make data entry or viewing easier, such as ActiveX calendar, spinner, and other custom controls.

In most instances, you add a control to a form or report by dragging a field name from the Field List or by using the Toolbox in Form Design View or in Report Design View. You place each control on a section within a form or report. Depending on the section, the control will be seen once, on every page, every time a group changes, or for every record.

Chart: Axes Modify

On the horizontal(X) or vertical(Y) axis, you can change the scaling (minimum and maximum numbers) and values where tick marks appear. You can also format fonts and numbers on the axes, and determine the patterns you want for the lines.

Steps

1. In Design View of a report or form, double-click the chart to open Microsoft Graph.

2. Right-click one of the axes and choose Format Axis.

3. Change the weight, style, and color of the axis line or style of tick marks by choosing the Patterns tab and making your selections. Change the maximum or minimum numbers or location of tick marks on the value axis by choosing the Scale tab. Change the format of the labels on the axes by choosing the Font and Number tabs. Align the labels up and down or diagonally on the axis by choosing the Alignment tab and making the appropriate changes. Choose OK when finished.

CAUTION: When you are selecting objects in a chart, be careful which object you select and then right-click. Since the objects are so close together, you may need to click more than once to get the correct object selected. If you have problems selecting the correct object, you can also use the Chart Objects drop-down button on the toolbar.

Chart: Colors Display

You can change the colors or patterns that appear for each bar or line series on your chart. You can make an individual series stand out more than usual by using Access's default color.

Steps

1. In Design View of a report or form, double-click the chart to open Microsoft Graph.

2. Select bar or line series or a pie slice: To select an entire data series, click any point in the series; to select an individual point in the series, click the data point twice (not a double-click); to change all data points for all series, click outside the chart to select the entire chart.

3. Right-click the selected object and choose the format option.

4. Make choices for the Border, Area, or Markers on the Patterns tab. Choose OK.

Chart: Create

Charts enable you to present data in graphical form. When you create a chart, the data used to create the chart is automatically linked to the chart. When the data changes, the chart is updated to reflect those changes. Access provides many features for creating and formatting charts. The Chart Wizard leads you step-by-step through the process. You can change chart types, add elements to a chart (such as titles or legends), and format chart elements (such as numbers, fonts, and styles).

You can launch the Chart Wizard from the Database window by selecting the New button from the Forms tab or the Reports tab. In the Design View of a report or form, you can also launch the Chart Wizard by selecting Insert, Chart and dragging the mouse pointer to draw the area where you want the chart to be placed.

CAUTION: Try not to add too many data points to your chart. It can be confusing to read. Be especially careful if you plan on turning your chart into a slide or overhead. Too many items on the page will be difficult to see at a distance.

Steps

1. From the Reports or Forms tab of the Database window, click the New button. Select the table or query source for the data from the drop-down button and double-click Chart Wizard.

2. On the next two steps of the Chart Wizard, choose the fields to be charted and the chart type and choose the Next command button after completing each step of the wizard.

3. The Chart Wizard then enables you to choose the layout for your chart. To change the location of what will be graphed, you can drag the name of a field to the Data box (value axis), the Axis box (category axis), or to the Series box.

4. Double-click a number field in the Data box and choose whether you want to sum, average, count, or find the maximum or minimum of each value.

5. Double-click a date field in the Axis box to determine how you want to group dates (years, months, and so on) and if you want to select specific data. Choose Next.

6. On the last (finish flag) step of the Chart Wizard, give the chart a title, decide whether you want a legend and whether you want to go to Design View. Choose Finish.

Chart: Data Labels

You can attach labels to data points on your chart. This can help the viewer interpret the data in a chart more easily. Data labels can represent the value for that data point or the category axis label associated with the data point. You can attach data labels to individual data points, a single data series, or all data points in a chart.

CAUTION: Only attach labels to charts with small numbers of data points or attach labels to only some of the points. Otherwise, your chart will become too crowded and difficult to read.

Steps

1. In Design View of a report or form, double-click the chart to open Microsoft Graph.

2. Select the data point(s) to which you want to add labels: to select an entire data series, click any point in the series; to select an individual point in the series, click the data point twice (not a double-click); to insert labels on all data points for all series, click outside the chart to select the entire chart.

3. Right-click the selection and choose Format Data Series (or Point) or Chart Options choice on the shortcut menu. Then click the Data Labels tab in the dialog box.

4. Select the Data Labels option you want to use, such as Show Value, Show Percent, or Show Label. (Depending on the chart type, some options might not be available.) Choose the None option if you want to remove existing data labels. Click OK.

NOTE: If you want to format the font or number of the data labels, right-click a label and choose Format Data Labels.

Chart: Data Source Change

In some cases, you might want to change the source for the data for the chart. Maybe you copied the form or report and want to use the same chart type and setup but not the same data source. Or perhaps you need to change your criteria when using Chart Wizard.

CAUTION: If you change the name of your table, your charts, forms, queries, and reports no longer work for data used from the table. Try to develop a good naming convention so you wont have to change table and field names midway through your database design.

Steps

1. From a Report or Query Design View, right-click the chart object (you are not in Microsoft Graph) and choose Properties if the Property sheet is not visible.

2. In the Row Source property, use the drop-down arrow to choose an existing table or query or click the build (...) button to enter the SQL Statement: Query Builder.

3. Choose fields from the field lists, add criteria and expressions, and change the Total choices (Sum, Avg, Count, and so on) as desired.

4. Click the SQL window's close button and choose Yes to save the changes you made.

NOTE: The SQL query builder window is the same as a normal query window. You can use the same procedures mentioned in the Queries and Filters and Calculations parts of this book.

Chart: Edit

In order to add labels, change colors, or change chart types, you need to be in Microsoft Graph, the applet that comes with Microsoft Office. The Chart Wizard automatically uses Microsoft Graph but you can also double-click a chart to enter this applet.

Steps

Design View

report

form

2. Select the part of the chart you want to modify. For data series, you click once to select an entire data series. You click a second time to select an individual point in the series. To select the entire chart, click outside the chart.

3. Right-click the selected object to bring up the shortcut menu. Make choices on the dialog boxes specific to the object. Choose OK when finished with each dialog box.

4. Repeat Steps 2 and 3 for each object you want to change.

5. When finished with your changes, click the Close (X) button in Microsoft Graph's upper right corner.

6. Back in Design View of the report or form, click the Save button to save the changes to your chart.

Chart: Grid Display

Use gridlines to help viewers compare markers and read values in a chart. If you use the Chart Wizard to create a chart, Access enables you to add gridlines as you are creating the chart.

You can add gridlines that originate from either the category or value axis, or both.

Steps

Design View

report

form

2. Click the Category Axis Gridlines or Value Axis Gridlines buttons on the toolbar to turn gridlines on or off.

Chart: Labels Enter

You can add a label attached to a specific portion of a chart (See also "Chart: Titles Add" and "Chart: Data Labels") or add a label and move it anywhere you want.

Steps

2. Type new text and press Enter.

You can move the label by selecting it and then dragging an edge of the title with the left mouse button to the desired location. If you select an existing label and then type, you replace the existing text. To delete a label, select it and press Delete. To edit a label, click once to select it and then position the mouse I-beam in the text and add or delete text.

Chart: Legend Display

A legend explains the markers or symbols used in a chart. When you use the Chart Wizard to create a chart, Access asks if you want to create a legend by default, based on the labels of the values you added to the Series box. You can edit the chart to add or remove the legend. You also can customize a legend with border, pattern, and font selections.

Steps

2. Click the Legend button on the toolbar to turn the legend on or off.

You can move the legend by selecting it and then dragging the legend to the desired location. To resize the legend, select it and then drag one of the black handles surrounding the legend.

TIP: To format the legend, right-click the legend and choose Format Legend from the shortcut menu. Make your desired selections from the Format Legend dialog box and then click OK.

Chart: Titles Add

You can add titles to help explain the data in your chart. Normally, you should include a main chart title, as well as titles for the category and value axes. If you use the Chart Wizard to create a chart, Access enables you to add chart titles as you are creating the chart. You also can choose to add chart titles later, or modify existing chart titles.

Steps

Design View

report

form

2. Choose Chart, Chart Options; then click the Titles tab in the Chart Options dialog box.

3. Select the text box for the title you want to add (such as Chart Title), and type the title; then click OK.

You can move the title by selecting it and then dragging an edge of the title to the desired location. To delete a title, select it and press Delete.

TIP: To format a chart title, right-click the title and choose Format Title from the shortcut menu. Make your desired selections from the Format Title dialog box and then click OK.

Chart: Trendlines

You can add a trendline to a chart to show the direction of the charted data and to make predictions. Regression analysis is used to create the trendline from the chart data. You can choose from five types of regression lines or calculate a line that displays moving averages.

Steps

Design View

report

form

2. Choose Chart, Add Trendline; then select the Type tab in the Add Trendline dialog box.

3. Select the data series for which you want to create a trendline in the Based On Series list.

4. Select from the six Trend/Regression types: Linear, L_ogarithmic, Polynomial, Power, E_xponential, and Moving Average. For more information on these types, click the question mark in the title bar of the dialog box, then click the option for which you want more information.

5. Select the Options tab if you want to set any additional options for the trendline, such as the Trendline Name or Forecast options. Click OK.

Chart: Type Change

You can change an Access chart type to represent another type of data. You can change to any of the chart types that Access offers--bar charts, line charts, pie charts, or special custom charts like floating bar charts.

NOTE: While it is fun to play with all the different chart types that come with Access, try to pick something that your audience will understand and that is appropriate to understanding the data.

When to Change Chart Types

You should use an appropriate chart type for the data you want to chart. The following list illustrates some common chart types and explains their purpose. For more detailed information on all the chart types, and examples of their use, search on "example chart types" in Microsoft Graph help.

Column chart

Bar chart. Same as a column chart, but displays bars horizontally rather than vertically. This emphasizes values and there is less focus on time.

Line chart. Illustrates changes in a large number of values over equal time intervals.

Pie chart. Shows the relationship of each item to the sum of the items.

XY (Scatter) chart. Plots two groups of numbers as one series of XY coordinates; commonly used in scientific applications.

Area chart. Shows how volume changes over time and emphasizes the amount of change.

Steps

Design View

report

form

2. Right-click in a blank area of the chart, and choose Chart Type from the shortcut menu.

3. In the Chart Type dialog box, click the Standard Types or Custom Types tab.

4. Select the chart type you want; then click OK. Resize the chart, if necessary.

NOTE: In some cases, data can be more effective when presented in a Totals query or report. (See also "Calculations: Sum of Values" in the Calculations part of this book.) Don't overload your charts with too many data points. Combine data into logical units to make your charts more effective.

TIP: You can change the chart type for just one of the of the series. Right-click the bar or line for the series and choose Chart Type. The other data series are graphed in the old type, while the selected series is graphed with the new type.

Controls: ActiveX Add

ActiveX controls provide additional functionality for your forms. They provide additional input options or show feedback. The Calendar control is an option you can select during setup. If you have the Developer Edition of Microsoft Office, you have access to additional ActiveX controls. You can also buy additional controls from third-party vendors and perhaps download controls from the Web. Before you can complete this task, you must register the ActiveX control. (See also "Controls: ActiveX Register.")

NOTE: In versions prior to Access 97, ActiveX controls were referred to as OLE (Object Linking and Embedding) controls or custom controls.

Steps

form

Design View

2. Click the More Controls button on the Toolbox and select the control from the list.

3. Drag the mouse to draw the location where you want the control to be placed on your form.

4. After the control appears on your form, right-click the object and set its specific properties through the controlname Object choice on the shortcut menu.

5. To program other properties and events, right-click the object and choose the item in the Property sheet.

NOTE: For additional help on specific ActiveX controls, reference them in help. For Developer Edition tools, the help will be integrated with Access Contents and Index help (not necessarily the Office Assistant). For other controls, you will need to read the help that comes with the controls.

Controls: ActiveX Register

Before you can use an ActiveX control, you need to register it. Some controls are registered automatically when you install them, others need to be registered. The Calendar control comes with Microsoft Access. If you don't have it installed, this option is a choice under the Microsoft Access options during the setup procedure. If your control is not registered, or if you want to unregister the control, use the following procedure.

Steps

ActiveX

ontrols

2. To unregister a control, move to the name in the Available Controls list and choose the Unregister button.

3. To add a control, click the Register button and search for the file (extension is OCX) and choose the Open command button.

Controls: Add

A control is any object such as a text box, line, subform, or label added to a form or report. You can add controls in many different ways. This section summarizes how to add controls. On reports, the controls you normally add include text boxes, labels, lines, rectangles, page-breaks, and perhaps check boxes. On forms you can use all the controls in the Toolbox. For more details, see the following tasks for descriptions on how to add specific controls.

Steps

check boxes

Bound

field

Design View

List box

2. To add combo boxes, option groups, list boxes, command buttons, subforms, or subreports, click the Control Wizards button on the Toolbox in Form Design View, click the specific button in the Toolbox, click the form where you want to place the control, and follow the dialog boxes of the wizard.

3. To add a toggle button, option button, or check box outside of an option group, select that button on the Toolbox first, and drag the field name from the Field List box to the form.

4. To draw a line, rectangle, or tab control, click that button in the Toolbox and then drag the mouse pointer in the form or report.

5. To add a label, click the Label button in the Toolbox, click in the form or report, and then type the text for the label.

TIP: To lock a control in the Toolbox so that you can create several of those controls, double-click the control tool before you create the control on the design surface. That tool stays selected until you select another.

Controls: Align

When you move controls (see "Controls: Move) it is sometimes difficult to get the controls to line up. In these cases, align the controls with menu options.

Steps

Design View

form

report,

2. Choose Format, Align.

3. Choose one of the menu items: Left, Right, Top, or Bottom.

The To Grid item on the Align menu aligns the controls to the nearest grid dot. To have controls line up to the grid as you place them on the form or report, choose Format, Snap to Grid. (See also "Controls: Add.") To see the grid, choose View, Grid.

TIP: If you often align controls, you can create your own toolbar or add the align tools to the Toolbox or other toolbar. Right-click the Toolbox and choose Customize. Click the Commands tab of the Customize dialog box and choose Form/Report Design in the Cate-gories list. Scroll down the Commands list and drag the Align Left, Align Right, Align Top, and Align Bottom to your toolbar.

Controls: Bound Control Create

Controls are devices that display data. When a control displays data from a data source, it is called a bound control.

Steps

form

report

Design View

2. Click the Field List button on the toolbar to display the Field List.

3. Select the field(s) that your control is bound to.

4. Drag the selected field(s) to the form or report and position the upper-left corner of the icon where the upper-left corner of the control (not its associated label) will be positioned, then release the mouse button.

Access creates the appropriate control for that field and sets properties of the control based on the underlying field properties from the table and default display control properties.

If the bound control isn't the one you want, click the control and press Delete.

Controls: Calculation Create

You can create controls on your forms and reports that perform calculations. After you create a control, you type in the expression for the calculation. (See "Calculated Fields: Forms and Reports--Create by Typing" and other tasks in the Calculations part for details on the types of calculations you can create.)

Steps

Text Box

Toolbox

2. Type equals (=) and then type the expression to calculate. Include field names in square brackets. For example, =[Unit Price]*[Amount] for an extended price. If the control is in a header or footer section of a report, use summary functions such as Sum() or Avg() and include the field names in brackets within the paren-theses.

3. To edit the formula, right-click the control, choose Properties. In the Control Source property, change the expression. If the formula is too large to see, press Shift+F2 to zoom on the Control Source property.

Controls: Change Control Type

Access offers you an easy way to change an existing control on a form or a report. The Change To command can convert one control to another control. When doing so, the appropriate property settings are preserved. When a property exists, it is copied; when a property doesn't exist, it is ignored. If a property is left blank in the original control, Access sets it using the default control style.

The Change To command is used most often to change one type of control to another of the same type (for example, a bound control to another type of bound control). Only appropriate choices are available in the Change To submenu when you select a particular type of control.

Steps

Design View

2. Select the control you want to change.

3. Select new control type from the available choices on the Format, Change To submenu.

Controls: Check Box Create

A check box allows for speedy input of yes/no type fields. A check mark in the box indicates yes; a blank indicates no. When you're inputting, you can also move to the field with the keyboard and press Spacebar to turn the box on or off.

Steps

form

Design View

2. Display the Field List by clicking the Field List button on the toolbar.

3. Drag a field with a yes/no data type to the form. The default control for the field is a check box.

Controls: Colors

When you're designing an input form, consider using colors. The effective use of color can make inputting less boring and draw attention to important parts of the form. If you have a color printer, you can also print the form in color.

Steps

report

control

2. For text controls, click the Font/Fore Color button to change the text to the color on the button, or use the button's drop-down arrow to choose another color.

3. For text controls, rectangles, and the background of each section, click the Fill/Back Color button to change the background to the color on the button, or use the button's drop-down arrow to choose another color.

4. For lines, text controls, and rectangles, click the Line/Border Color button to change the line or the outline of the control to the color on the button, or use the button's drop-down arrow to choose another color.

If you want to see a grayed button when the value is Null, change the Triple State property to Yes. (See "Data: Blanks, Nulls, and Zero-Length Strings" in the Table and Database Design part of this book.)

NOTE: To change the formatting of the control programmatically, look at the format properties on the Property sheet. The code for the property name (BackColor) is without a space. To change the back color to red, the code would be controlname.BackColor = 255. To find the values for the colors, first change the color using the build button... for that property on the Property sheet. Then copy the number and paste it into your code.

Controls: Combo Box Create

A combo box enables the user to type or choose from a list of predetermined options. A combo box is especially useful if you have a code you need to place in a form and the code corresponds to a value. Instead of having to remember the codes, a user can select from more meaningful data. Using a combo box can also help avoid data entry errors.

The source for the drop-down list in a combo box can be a table or query, values you type during design, or a list of field names from a table or query. If you drag a field from the Field List whose data type is already a Lookup Field, Access will automatically create a combo box for you from the properties of the field in Table Design (see "Lookup Column: Create with Wizard" in the Table and Database Design part of this book). As an alternative to a combo box, you can also use a list box. (See "Controls: List Box Create.")

Steps

Design View

Toolbox

Control

2. Click the Combo Box button on the toolbar and click in the form where you want the combo box to appear. The Combo Box Wizard opens.

3. To type your own values of what will appear in the combo box, choose I Will Type the Values I Want, choose Next, and type the number of columns and values in each column you want. If you type more than one column, choose which column will be the source for the data to store in the field underlying the combo box, which field you want to store the value in, and the label for the combo box on the next screens.

4. To use an existing table or query, on the first screen of the Combo Box Wizard, choose the I Want the Combo Box option. Choose the table or query, the fields you want to see when you choose the drop-down arrow, the column width of the fields, and whether you want to hide the key column. As in Step 3, also choose which column becomes the value to store, which field you want to store the value in, and the label for the combo box on the next screens of the wizard.

5. If you want to use the combo box to move the form to a specific record, you usually place the box in the form header. On the first step of the Combo Box Wizard, choose the Find a Record (third choice), choose the fields you want, the column widths, and the label for your combo box on the next screens of the wizard.

After you finish the wizard, the combo box appears on your form. To see or modify the properties, right-click the combo box and choose Properties. The important properties and the property tab they appear on are as follows:

Control Source (Data tab). This property is the field in your table where you're storing data.
Row Source (Data tab). The name of the table or query used to lookup values. You can click the build button (...) to access the query builder and choose the fields and sort order of items that appear in the drop-down list. If you typed the list, the values appear separated by semi-colons with text enclosed in quotes.
Column Count (Format tab). The number of columns from the row source used for the list.
Column Widths (Format tab). The width displayed for each column in the list; 0 will not display a column.
Bound Column (Data tab). The column from the row source that will be placed in the field on the form.
List Width (Format tab). The width of the entire drop-down list.
Limit to List. This property determines whether you want to limit values to the table/query/list for your combo box or enable the user to type other values as well.

NOTE: When you use a table or query for the source of the combo box list, you often want to store the value of the primary key in a field on your form. The primary key field needs to be one of the fields you choose during the wizard setup. However, you can set the Column Width property to 0. Then, after you move off this field in Form View, the second field of the Row Source is visible.

Controls: Combo Box Not in List

To limit the user to values in the combo box, choose Yes on the Limit To List property. If the user types a value that is not in the list, you can have Access give the standard error message or create a procedure to run. For more help on procedures, see the Special Features and Programming part of this book.

Steps

Design View

form

2. Move to the On Not In List property (on the Event tab), click the build button (...) on the right and double-click Code Builder. You will enter the VBA code window in a procedure with your Controlname_NotInList.

3. Type your code. Notice that the procedure has two variables, NewData and Response. NewData contains whatever you typed in the combo box. Response is for your return value whether you want the default error message to be returned or skipped. Use Response = acDataErrContinue if you want to skip the message or Response = acDataErrDisplay if you want the default error message.

4. Click the Compile Loaded Modules button, close the code window, and test your procedure.

NOTE: For an example of the NotInList code, see the CategoryID field on the EnterorEditProducts form in the Solutions database. The example databases are in the Office or Access directory in the Samples folder (for example, C:OfficeSamples).

Controls: Command Button Create

Command buttons are common in forms and enable you to go to another form, preview a report, perform record navigation tasks, and more. Command buttons are the primary method for moving a user through a series of options. Command buttons can be part of a form with other controls or the form can consist only of command buttons. This kind of form is called a switchboard form.

Steps

Design View

Toolbox

Control

2. Click the Command Button tool on the toolbar and click in the form where you want the button to appear. The Command Button Wizard opens.

3. Choose the category and action to perform from the first step of the wizard. Choose Next.

4. Type the text you want to appear on the button or choose a picture. If you want to see more pictures, check the Show All Pictures check box. Choose Next.

5. Type a name for your button on the last step of the wizard. A good convention is to start the name with cmd and then give the button a meaningful name such as cmdPreviewEmployees.

If you want to view or edit the code created by the wizard, right-click the button in Form Design View and choose Build Event. The code procedure's name is the buttonname_click.

Controls: Copy

If you are creating a series of the same type of controls, you can copy the controls and move them on your form. (See also "Controls: Move)." Copying controls works especially well for lines and command buttons to create uniformly sized objects.

Steps

Design View

report

2. Choose Edit, Duplicate.

3. Move the new control to the desired position.

NOTE: You can also select the control and click the Copy button. Move to the new location (including a different form or report) and choose the Paste button.

Controls: Data Source

If you create a control and later need to change the field that it refers to, you can change the Data Source property. This might be necessary if you copied the form or report (see also "Database Object: Copy" in the File Management part of this book) and changed the Record Source property to a different table or query. You might also need to change the Data Source property if you copied the control. (See also "Controls: Copy.")

Steps

2. Move to the Control Source property (on the Data tab) and click the drop-down arrow to choose a different field.

3. If the control is a calculated expression, type an equal sign (=) and then type the expression or click the build button (...) and use the Expression Builder.

For more help on the expression builder, see "Expression: Using the Builder" in the Calculations part of this book.

Controls: Defaults Change

Each control has its own default properties. For example, when you select a text box control and click in the design area, the label associated with the control normally appears to the left of the text box. You can change the label properties of the text box as well as other default properties on controls you add.

Steps

Design View

form

report

Toolbox

2. Click the button in the Toolbox and then click the Properties button on the toolbar.

3. Change the properties for all controls of this type in the Properties sheet.

The default is set for all controls of this type for this form or report only.

NOTE: To set defaults for all forms or reports in the database, create a template. Create a form or report with all the settings for the default controls and background colors. Save the form. Then choose Tools, Options, click the Forms/Reports tab. In the Form Template or Report Template text box, type the name of the form or report. If you want to use the templates for other databases, copy them into each database you need to use the defaults. For more information on templates, see "Forms and Reports: Default Template."

Controls: Delete

When you no longer need a control on a form or report, you can delete it. Deleting the control does not delete the underlying data in the table. However, if the field is required or used in table validation rules, you won't be able to save the record.

Steps

2. To delete only the attached label, select the label and press Delete. You cannot delete a control with an attached label and leave the label.

Controls: Display or Hide

In some instances, you might need to hide a control on a form or report. You might need to use the value for calculations or programming.

Steps

Design View

2. Move to the Visible Property (on the Format tab). Choose No to hide the control or Yes to display the control.

NOTE: To hide a control programmatically, type Controlname.Visible = False. To display the control, type Controlname.Visible = True.

Controls: Font Size and Face

Font attributes such as typeface and size provide legibility for your forms and reports as well as make the document attractive. To change the text attributes of any control (label, text box, combo box, list box, and so on), you can use the buttons on the Formatting (Form/Report) toolbar. You can also use the properties of the control.

Steps

2. Click the Bold, Italic, or Underline buttons to apply that formatting.

3. Click the Font button's down arrow and choose a different typeface.

4. Click the Font Size button's down arrow and choose a different font size.

TIP: If the font is too big for the control, use Format, Size, To Fit to change the control size to fit the text.

NOTE: To change the formatting of the control programmatically, look at the format properties on the Property sheet. The code for the property is without a space. To change the font size, you would type the code controlname.FontSize = 14.

Controls: Labels Create

Labels are automatically created with controls such as text boxes, combo boxes, and so forth. The label itself is a control with its own control properties. They direct the user where to input text or what the data means. There are some instances where you want to add additional labels to a form or report. You can add a title to the form or report header and also replace a label that you deleted.

Steps

Design View

Toolbox

2. Click in the design area where you want the label to go.

3. Type the text for the label.

To edit the label, click once to select the control and click a second time to enter edit mode. You can also change the Caption property on the Property sheet.

NOTE: The default is for a label to be created with data controls. However, you can turn off this feature by clicking the Text Box control in the Toolbox, clicking the Properties button, and changing the Auto Label property (on the Format Tab) to No. Please note that the items on the Format tab of the Default Text Box are not alphabetically listed. You have to scroll down to find the Auto Label property.

Controls: List Box Create

Creating a list box is similar to creating a combo box (See "Controls: Combo Box Create."), especially for important properties such as Bound Column, Row Source, and Column Widths. A list box allows you only to choose from an item in the list and does not allow you to type new values. However, when you click in a list box, you can type the first letter to move to an existing item.

NOTE: If you don't have adequate room on a form, use a combo box instead of a list box.

Steps

Design View

form

Toolbox

Control

2. Click the List Box button on the toolbar and click in the form where you want the list box to appear. The List Box Wizard opens.

3. To type your values that will appear in the list box, choose I Will Type the Values I Want, choose Next, type the number of columns and values in each column you want. If you type more than one column, choose which column will be the source for the value for the underlying field, which field you want to store the value in, and the label for the list box on the next screens.

4. To use an existing table or query, on the first screen of the wizard choose the I Want The List Box To Look Up The Values In A Table Or Query. Choose the table or query, the fields you want to see in the list, and the column width of the fields and whether you want to hide the key column. As in Step 3, also choose the column source underlying field, which field you want store the value in, and the label for the list box on the next screens.

5. If you want to use the list box to move the form to a specific record, you usually place the box in the form header. On the first step of the List Box Wizard, choose the Find a Record (third choice), choose the fields you want, the column widths, and the label for your list box on the next screens.

After you finish the wizard, the list box appears on your form. To see or modify the properties, right-click the list box and choose Properties.

NOTE: If you use a list box (or combo box) to locate a record (see preceding Step 5), Access creates a procedure for the After Update property. See the Event Tab in List Box dialog box.

TIP: You can convert a list box to a combo box or text box. Choose Format, Change To and Combo Box or Text Box.

Controls: Move

You drag controls to move them to a desired location. However, getting the controls to line up is easier to accomplish if you use the Format, Align menu. (See "Controls: Align.")

Steps

Design View

form

report

2. Position the mouse pointer hand on the border of one of the controls and drag to move the selected control(s) and attached labels.

3. To move only the control or attached label, position the mouse pointer finger on the upper left corner of the control (the larger box) and drag.

Controls: Option Button Create

Generally, it's a good idea to use standard Windows conventions for your forms' interface. For this reason, a check box means a yes/no option and an option button means only one option out of a group of options can be selected. (See "Controls: Check Box Create" and "Controls: Option Group Create.") However, you can create an option button as a yes/no alternative where a filled circle means yes and a blank circle means no. Some programs call these radio buttons.

Steps

Design View

form

Toolbox

Field

2. Click the Option Button tool on the Toolbox and then drag the name of the yes/no field from the Field List to the form design area.

3. If you want to add another option button to an option group, click the Option Button tool and move into the option group (it will become selected) and click. If necessary, change the Option Value on the Properties Sheet Data tab and the caption (Properties Sheet Format tab) of the label attached to the option.

Controls: Option Group Create

An option group enables you to click one of a series of possible answers. The option group itself contains the reference to the field in its Control Source property. Each option button within the group refers to a potential value for the option group field. The values for the option buttons must be numeric (with no decimals) so the option group feature can only be used for numeric fields that can accept Byte, Integer, or Long Integer field sizes. (See also "Field: Size" in the Table and Database Design part of this book.)

Because screen space constrains you to about four options in a group, a combo box can be a better alternative if you have more options. (See "Controls: Combo Box Create.")

Steps

2. On the first steps of the wizard, type the label names for each option within the group. Also, choose if you want one of the options to be the default choice for new records and the values that each option represents.

3. On the next step, choose which field stores the value of the option. The other choice, Save the Value for Later Use, could be used in programming. Choose Next.

4. Decide whether you want option buttons, toggle buttons, or check boxes within your option group and what style the group will have; then, choose Next.

5. On the final screen of the wizard, give the option group a caption. Type the name and choose Finish.

To add option buttons, toggle buttons, or check boxes into the option group after it is created, click one of those buttons in the Toolbox and drag into the option group until it is highlighted. Edit the caption of the label and change the Option Value property of the new option.

NOTE: If you are using the option group for programming rather than to fill in a field, you would add code to the After Update property (on Event tab). A common way to handle options would be to use the Select Case, End Case statements. Between these two statements, use Case number on one line followed by programming statements on the next lines. Number would be the value for each option within the group.

Controls: Properties Change

Controls are edited in the Design View for forms and reports. Some aspects of a control can be altered, for example, through manipulating the control's shape on the design surface or resizing the control. Most aspects of controls are edited in the Property sheet for that control.

To view a control's Property sheet, select the control and click the Properties button on the toolbar.

You can click either the All, Format, Data, Event, or Other page to see a subset of the properties for that control.

Steps

Design View

form

report

Properties

2. Click the Property you want to set; or navigate to it using the Up or Down arrow keys, or the Page Up or Page Down keys.

3. Enter or edit the value of the property.

4. If the property has a down arrow displayed, you can select the value from the drop-down list; or if the property displays a build button (...), you can click that button and alter the expression in the Expression Builder.

5. Press the Enter key or click outside of the row to establish your changes.

For more help on the expression builder, see "Expression: Using the Builder" in the Calculations part of this book.

TIP: If you need to open up a larger window for that property, press the Shift+F2 key to open a Zoom box. To get help for a particular property, press the F1 key while that property is current.

Controls: Select

Before you move, delete, change font attributes, align, or change properties of a control, you need to select the control first. Depending on your needs, there are numerous ways to select controls.

When you select a control, small, black sizing handles appear around the border of the control. When the mouse pointer is on a sizing handle, the pointer turns to a double-headed arrow, enabling you to size the control. (See also "Controls: Size.") The upper left corner of a selected control displays a larger box that enables you to move the control independent of the attached label. (See also "Controls: Move.") In each of the following steps, you are in Design View of a form or report.

Steps

Select Objects

2. To select multiple adjacent controls, drag the mouse to draw an outline around the controls (do not start dragging on top of a control). All the controls within the outline and any control that touches the outline are selected. This is sometimes referred to as lassoing controls.

3. To select multiple controls, click the mouse on the first control, hold down Shift, and click the other controls.

4. To select all controls in a vertical or horizontal column or row, move the mouse pointer into the horizontal or vertical ruler and click or drag in the ruler.

CAUTION: When trying to select controls, you might accidentally move or size one or more controls. Immediately click the Undo Current Field/Record button to return the controls to the previous position.

To unselect controls, click in the design background, not on a control.

NOTE: You can change the effect of lassoing controls. Choose Tools, Options, Forms/Reports tab. In the Selection Behavior section, choose Partially Enclosed to lasso as mentioned in the preceding Step 2 or Fully Enclosed to require that the entire control be within the outline to be selected.

Controls: Size

For some controls, especially labels with larger fonts, you will need to resize the control. This is also true for most other controls with underlying field values. When you use a Form or Report wizard (see also "Forms: Create with Form Wizard" and "Reports: Create with Report Wizard") or AutoForm or AutoReport (see also "Forms: Create with AutoForm" and "Reports: Create with AutoReport") to create a form or report, text box controls are generally wide enough to display the widest value. When you drag fields from the Field List to add them in Design View, the control might not be wide enough to display the field's text.

Steps

Design View

form

report

2. If the control is a label, choose Format, Size, To Fit (this does not work with data controls).

3. For all controls, move to the center sizing handle on any edge. Drag the double-headed black arrow.

4. To size all selected controls the same, choose Format, Size and make one of the following choices: To Tallest, To Shortest, To Widest, or To Narrowest.

Controls: Space

Spacing on a form or report is often an important issue if you want to make the document legible or if you need to fit more items into an area. You can move the controls (see also "Controls: Move") or choose one of the Format menu options.

Steps

2. For a column of controls choose Format, Vertical Spacing or for a row of controls choose Format, Horizontal Spacing.

3. Choose one of the following: Make Equal, Increase Spacing, Decrease Spacing.

Controls: Text Box Create

Text box and label controls (see also "Controls: Labels Create") are the most common controls on your forms and reports. Text boxes are used to display the underlying fields in the table or query. In Form View, you also use text boxes to edit or type new values. You can also use them to show the results of calculations. (See also "Controls: Calculation Create.")

Steps

Design View

form

Toolbox

Field

2. Drag the field name from the Field List into the design area.

The Control Source property of the control shows the name of the field. You can use the drop-down arrow to change the field.

NOTE: If you have a large text or memo field, you can change the size of the control. (See also "Controls: Size.") Then change the Scroll Bars property (on the Format tab) to Vertical. This will enable you to scroll to see more text when you are in Form View and in the control.

Controls: Toggle Button Create

A toggle button is an alternative to a check box, enabling you to input Yes/No responses on a form. (See also "Controls: Check Box Create.") When the value is Yes or True, the button appears pressed. When the value is No or False, the button appears raised. You can also use toggle buttons as part of an option group. (See also "Controls: Option Group Create.")

Steps

Toolbox

Field

2. Click the Toggle Button tool on the Toolbox and then drag the name of the yes/no field from the Field List to the form design area.

3. If you want to add another toggle button to an option group, click the Toggle Button tool and move into the option group (it will become selected) and click. If necessary, change the Option Value on the Properties Sheet.

4. Click in the middle of the toggle button and type text to appear on top of the button.

Unless you complete Step 4, it is difficult to tell when a toggle button is pressed. If you want a picture on the toggle button instead of text, choose the Picture property on the Property sheet's Format tab and click the build (...) button to choose the picture. If you want to see a grayed button when the value is Null, change the Triple State property to Yes. (See "Data: Blanks, Nulls, and Zero-Length Strings" in the Table and Database Design part of this book.)

Controls: Unbound Control Create

A control that is connected to a data source is called a bound control; one with no data source is called an unbound control; and one attached to an expression is called a calculated control. How you create a control, or add it to a form or a report, depends on the type of control it is.

Steps

form

report

Design View

2. Click the button in the Toolbox for that unbound control.

Typical unbound controls are text labels, pictures, lines, and so on.

3. Click and drag the control onto the form or report.

4. If you used the Image or Unbound Object Frame buttons, complete the dialog boxes to insert the file or create the object.

Data: Default Value

One way to simplify data entry is to have Access automatically enter values that you use often in certain fields. For example, if most of your clients were from the same state, have Access set the default value. You can set the default value during table design. (See also "Field: Default Value" in the Table and Database Design part of this book.) However, you might have multiple forms for one table and decide to enter different default values for each form.

CAUTION: Make sure the default value does not click with the Validation Rule property (Data tab).

Steps

form

Design View

control

Property

2. Move to the Default Value property on the Data tab and type the value you want for all new records.

When you enter values in a new record you can always replace the default value with an actual value.

Data: Validate

If you want to make sure the correct data is entered on a form, you can use data validation procedures. One alternative is to use a combo box or list box to make sure only one of the valid choices is entered. (See also "Controls: Combo Box Create" and "Controls: List Box Create.") You can also set data validation properties originally during table design. (See also "Validate Data: Field Validation" in the Table and Database Design part of this book.) If you do this before you create the form, the validation properties are automatically carried on to the control.

Examples of validation rules include >100 and between 0 and 50 for number fields, or >Date() (greater than today's date). (See the Criteria tasks in the Queries and Filters part and Expression tasks in the Calculations part of this book.)

Steps

2. Move to the Validation Rule property (on the Data tab) and enter an expression.

3. If you want your own error message to appear if this rule is violated, type the message in the Validation Text property.

NOTE: If you want to create more complicated data validation rules, see also "Validate Data: Record Validation" in the Table and Database Design part of this book. You can also create a procedure that you would apply to the Before Update property (on the Event tab) of the control.

Date and Time: Insert on Report

When you want to know when a report or form was printed, you can insert the date and or time using menu choices. As an alternative, you can also create a text box on a report or a form and insert and format a date expression. (See also "Calculations: Date" in the Calculations part of this book.)

Steps

report

form

Design View

2. Choose Insert, Date and Time. The Date and Time dialog box appears.

3. Choose Include Date and one of the date format options. If desired, choose Include Time and one of the time format options; choose OK.

4. Drag the control to where you want it on the report or form.

Format: AutoFormat

If you like a particular background and format settings for a form or report, you can use the AutoFormat feature. When you initially create the form or report with a wizard you can also choose an existing AutoFormat. (See also "Forms: Create with Form Wizard.") An alternative to AutoFormats are Templates, used when you create a new form or report in Design View. (See also "Forms and Reports: Default Template.")

Steps

2. Choose from the list of Form (or Report) AutoFormats and preview the format.

3. If desired, choose Options and choose whether you want to apply the fonts of the example, the colors of the controls, or the border surrounding the controls. Choose OK when finished.

NOTE: You can also use the Customize button on the AutoFormat dialog box to save your current form as a new AutoFormat.

TIP: You can also change the background of the form (including graphics that are not included in AutoFormat) by changing the Picture property (on Format tab) of the form. Click the build button (...) to select the file you want to insert.

Format: Copy

To format all of your forms the same way, consider using the AutoFormat feature. (See also "Format: AutoFormat.") However, when you just want to copy the style (font, font size, and colors) from one control to another, use the Format Painter button.

Steps

report

form

Design View

2. Click the Format Painter button once to copy only to one control or double-click for multiple controls.

3. Click the control(s) for which you want to change attributes.

4. If you double-clicked the Format Painter button, click it again to turn off the feature.

Format: Numbers and Dates

Generally, when you format numbers in a table or query, the format should be sufficient for your form or report. However, there are instances when the format is not appropriate. This happens especially when you create calculated controls.

Steps

control

Property

2. Move to the Format property (on the Format tab).

3. Use the drop-down arrow to choose from the list of format options or type a custom format.

NOTE: To see custom format options, click the Office Assistant button on the toolbar, type Format Property, and click Format Property.

Format: Special Effects

When you create a form with the Form Wizard, Access will prompt you for a style that includes special effects for the controls--for example, raised, sunken, or normal. If you want to change these effects or create them during Form Design, you can use the Special Effect button on the Formatting toolbar.

Special effects for controls include raised, sunken, flat (border outline), shadowed, etched (carved outline), or chiseled (carved line on bottom of control).

Steps

form

Design View

2. Click the Special Effect button to choose the current effect or use the drop-down arrow and choose one of the six effects.

3. If you want to put a simple border around the control and remove the special effect, use the Line/Border Width button.

Format: Text

When you are inputting text, you can have your entry converted to all uppercase or all lowercase.

Steps

Property

2. Move to the Format property (on the Format tab).

3. Type a > (greater than sign) to format the entry as all upper case or a < (less than sign) to format the text as all lower case.

While inputting, when you press Enter or Tab to leave the field, the text will format as you indicated.

Forms and Reports: Data Properties

While you are designing forms, you might want to use some of the data properties available for the form or controls. You can make a form or control read-only, for input only, or determine how you want to control simultaneous users.

Steps

form

property

Design View

Report

2. To change a control property, double-click the control to bring up the Property sheet.

3. Click the Data tab of the property sheet.

4. Once the form or control is open, make changes to any of the properties shown in the bulleted list by following these steps.

<dd> Record Source. Underlying query or table for the form or report. (See also "Forms and Reports: Data Source.")
Filter. Indicates which records will show. Access remembers the filter when you save a form after using one of the filter buttons or Records, Filter choices in Form View. Filters are not available when the Allow Filters property is set to No. This property is also available on reports and is active if the Filter On property is set to Yes.
Order By. Type the name of the field to sort the records. This property is filled in when you choose the Sort Ascending or Sort Descending button in Form View. This property is also available on reports and is active if the Order By On property is set to Yes.
Allow Edits. If this is set to No, the data on the form cannot be edited. To get the same effect just on a control, set the individual Locked control property to Yes.
Allow Deletions. If this is set to No, you cannot delete a record.
Allow Additions. If this is set to No, you cannot add a record.
Data Entry. When this property is set to Yes, the form automatically opens to a new, blank record.
Recordset Type. When this property is set to Snapshot, data on the form cannot be edited. Dynaset means that most forms can be edited. For more information, press F1 for help.
Record Locks. Select No Locks to allow any user to change a record. If the same record is changed by two users at the same time, the second user to save the record is prompted to decide which record to keep. Select Edit Records to not allow a second user to edit the record while it is being edited by the first user. Select All Records to prevent anyone from editing the underlying table while the Form is open. This property is also available for Reports with the No Locks and All Records options.
Enabled. When this control property is set to No, the control cannot have the focus and it appears dimmed.

Forms and Reports: Data Source

After you copy a form or report, you can change the attached query or table, which is the source for your data. In some cases, you need to change the data source to add additional fields to the underlying query or sort.

Steps

form

report

Design View

Property

2. Move to the Record Source property (on the Data tab). Click the drop-down arrow to choose another table or query.

3. If you need to add fields or sort your data, click the build button (...). You will enter a SQL query builder that is the same as a normal query window. Drag the fields you need from the Field List; use the Close (X) button to get back to the Property sheet.

NOTE: To use the SQL query builder, use the same techniques as shown in "Query: Create with Design View" in the Queries and Filters part of this book.

Forms and Reports: Default Template

If you like the colors, location of labels relative to data controls, and other form or report properties, you can save the form or report as a default template. Then when you create a form or report in Design View (not through a wizard), those properties are the defaults for the new form or report. Existing forms and reports are not affected. If you have multiple forms or reports you would like to use as templates, consider using the AutoFormat feature. (See also "Format: AutoFormat.")

Steps

Toolbox

sections

2. Save the form or report and choose Tools, Options, and click the Forms/Reports tab.

3. Type the name of the form or report in the Form Template or Report Template text box and choose OK.

4. If you want to export this form or report to another database, choose File, Save As/Export, choose the To An External File or Database option, and choose the database in the file dialog box.

NOTE: If you want to use a template from another database, you can also use File, Get External Data, Import to copy the template. Otherwise, Access will revert to the Normal template even if you have another template listed. Options remain in effect for all databases, not just the current database.

Forms and Reports: Design View Options

You can turn on or off different Design View options to help you create forms and reports. These options help you add, size, and align controls. The following list describes these options in more detail:

Properties Sheet. Click the Properties button on the Design toolbar to turn the Property sheet on or off. To set properties, see also "Controls: Properties Change" and "Forms and Reports: Detail Properties." You can also double-click a control, section, or Form and Report Selector to open the Property sheet for that control.
Field List. Click the Field List button on the Design toolbar to turn the Field List on or off. You can drag a field name to create a control for that field. (See also "Controls: Add.")
Ruler. Turn on or off the horizontal ruler (at the top of the Design window) and the vertical ruler (at the left of the window) by choosing View, Ruler. You can use the ruler to estimate the position of controls on forms and reports and to select controls in a row or column . (See also "Controls: Select.")
Grid. Turn the grid on or off by choosing View, Grid. Even without the grid visible, you can still move the controls and they will stop at dots on the grid if the Format, Snap to Grid choice is selected. If you want to temporarily ignore the grid when you are moving a control, hold down Ctrl while you drag the mouse. To line up your selected controls, choose Format, Align, To Grid. To size controls to the nearest grid points, choose Format, Size, To Grid. To change the spacing between the dots, double-click the Form and Report Selector to open the Property sheet for the form or report and change the Grid X and Grid Y values. The values indicate the number of dots per unit of measurement (such as inches).
Toolbox. The Toolbox enables you to place different kinds of controls on your form or report. Click the Toolbox button on the Design toolbar to turn the Toolbox on or off.

Steps

Design View

2. Turn the appropriate option on or off. For descriptions of these particular options, see the preceding list.

Forms and Reports: Detail Properties

The detail section of a report and form is where the data controls from each record normally go. While headers and footers summarize data and show labels for the report or form as a whole, the values in the detail section change for every record. Six of the detail properties are described as follows:

Keep Together. Move to the Keep Together property. Choose Yes to try to keep all records in the detail section together on one page when printing. This will not work if the detail section is larger than a page.
Visible. Move to the Visible property and choose No to show just the summaries on a report and not the individual values for records.
Can Grow and Can Shrink. Change the Can Grow property to Yes if you want the section to be able to expand to accommodate long labels (the control's Can Grow property should also be set to Can Grow). Set the Can Shrink property to Yes if you want the detail section to remove extra space if there are blank or short values for a record.
Back Color. To change the Back Color property, you can click the detail section and then click the Fill/Back Color button on the Formatting (Form/Report) toolbar and choose a color.
Height. To change the Height property, you can move to the bottom of the detail section until the mouse pointer becomes a double-headed arrow and drag the mouse up or down.

Steps

2. Double-click the gray bar labeled Detail. The Detail Property sheet opens.

3. Click the Format tab of the Property sheet.

4. Change the appropriate option as shown in the preceding list.

NOTE: To find information on other properties besides those described in this section, move to the property in the Property sheet and press F1.

Forms and Reports: Name

When you create a form or report using a wizard, Access will ask you the name of the form or report as the last step. (See also "Forms: Creating with Form Wizard" and "Reports: Creating with Report Wizard.") When you create a form or report using any other method, you need to give the form or report a name after you create it.

The names appear in the Database window. A standard convention is to include a lowercase three-character frm or rpt abbreviation before the report name, to not include spaces, and to capitalize the first letter of each word. However, Access allows you to include spaces and type up to 64 characters for a name.

Steps

form

report

2. To give the form or report a new name while in Design View, choose File, Save As/Export. In the New Name text box enter the name.

3. To rename a form or report, right-click the name in the Database window, choose Rename, and type the new name.

Forms: Create Hypertext Links

A hypertext link enables you to go to another document on your hard drive or network drive or to a Web site. You can enter hypertext links within fields for each record. (See also "Hyperlinks: Enter" in the Database Essentials part of this book.) However, you can also create a form that has hyperlinks to the documents you want. This form can act as an alternative to a switchboard with command buttons. (See also "Controls: Command Button Create.")

Steps

table

query

2. Click the Insert Hyperlink button on the Form Design toolbar. The Insert Hyperlink dialog box opens.

3. In the Named Location in File (Optional), choose Browse and select the object from the Select Location dialog box and choose OK twice.

4. If you want to change the name on the form, double-click the Hyperlink in Design View and type a new name in the Caption property.

NOTE: If you want to go to a different database, type the path and file name in the Link to File or URL text box in the Insert Hyperlink dialog box. You can even use this form to go to different documents in different applications (Word and Excel, for example).

Forms: Create with AutoForm

The quickest way to create a form is to use one of the AutoForm options. Use this procedure if you probably won't be changing the order of fields and you plan on using most of the fields from a table or query. To add a background and choose the order of fields, use Form Wizard instead. (See also "Forms: Create with Form Wizard.") For the most control, create a form in Design View. (See also "Forms: Create with Design View.")

Steps

Database window,

2. On the New Object button, select AutoForm if it does not show. Access creates a columnar form by default.

If you want other AutoForm options, select Form on the New Object button and in the New Form dialog box, double-click AutoForm: Tabular or AutoForm: Datasheet.

Forms: Create with Design View

Forms are one of the primary methods used to work with data, navigate, and perform actions in a database. You create, specify the contents of, and modify forms in the Design View. You work with forms and enter data into them in Form View. This construct separates the construction of your form from its use.

Steps

Database window

2. Choose the table or query to base your form on from the drop-down list. If you are creating a switchboard or dialog box, you can leave the text box blank.

3. Double-click Design View in the New Form dialog box. Access opens the blank form in the Design View.

4. Add labels, text boxes, and other controls.

5. Click the Save button on the Form Design toolbar or choose File, Save. Enter the name of the form in the Save As dialog box, then choose OK.

(See also "Controls: Add" and other "Controls" tasks to place controls on your form.)

Forms are used to:

Display and enter data into a database using a data entry form.
Select options via a form. The form you create has the attributes of a dialog box (text boxes, combo boxes, option buttons, and command buttons such as OK and Cancel).
Provide a method for launching or opening other elements of a database, such as forms and reports, through a form that looks like a switchboard.

All of the preceding forms are created in the Design View, and some can be created with the Form Wizard. In Design View, you can add graphic elements such as lines, boxes, text labels, and bound controls. Bound controls display data from underlying record sources like tables, queries, or calculations; or they contain the results of calculations based on data in your database.

When you work with forms in Design View, you add controls to the design surface of your form by clicking that control in the Toolbox and clicking and dragging it onto the form. You can add and delete sections from forms to control what appears on the form and where it appears.

Forms: Create with Form Wizard

Access contains a Form Wizard that can create many different kinds of forms based upon your input. Even if you intend to create a custom form, the Form Wizard can be a good starting place from which you can make modifications. As a quicker alternative to the Form Wizard, you can create a form automatically with AutoForm. (See also "Forms: Create with AutoForm.") You can also have more control over creating a form by going into Design View. (See "Forms: Create with Design View.")

Steps

Database window

dialog box

table

query

data source

2. In the first step of the wizard, select the fields you want to see on your form from the Available Fields list box by double-clicking to place them in the Selected Fields list. For any related tables: Select that table from the Tables/Queries list box, then add the fields of interest to the Selected Fields list box; then choose Next.

3. If you are building a form based on a relationship, in Step 2 of the wizard you can specify the parent table used to control the view of your data (choose the parent table in the How Do You Want To View Your Data list box).

If you have more than one data source, select also whether the child table appears in a Form with Subform(s) or is a Linked Form Linked Through a Button to a New Window; then choose Next.

4. Select the layout and style desired on the next two steps of the wizard. When you select a choice, a preview is shown in the window.

5. Enter a name for the form in the Form text box, or a name for the subform derived from the related or linked tables as appropriate and then choose Finish on the next screen.

Forms: Create with New Form Dialog Box

The New Form dialog box is your key to creating forms. There you can create forms from scratch, or use one of the different form wizards to create forms based on your input.

Steps

Database window

2. Select a table or a query to base your form on or leave blank to create a dialog box or switchboard.

3. Select one of the choices in the New Form dialog box (as detailed in the following list).

Among the choices you have in the New Form dialog box are the following:

Design View. This selection opens a blank form that you can build from scratch. (See also "Forms: Create with Design View.")
Form Wizard. This wizard runs you through several steps that enable you to select the tables, relationships, controlling table, form style, and other features you want your form to have. (See also "Forms: Create with Wizard.")
AutoForm Columnar. This columnar report is one where fields are stacked vertically with text labels on the left and text boxes on the right. Each record appears on a single page. (See also "Forms: Create with AutoForm.")

NOTE: Some databases refer to tabular layouts as columnar layouts.

AutoForm Tabular. This wizard creates forms in which you see several records on every page.
AutoForm Datasheet. This wizard creates a spreadsheet-like display for your form where records display in rows, and fields in columns.
Chart Wizard. The Chart Wizard creates a form with an attached chart. This is an optional setup option when you install Access or Office. (See also "Chart: Create.")
PivotTable Wizard. A pivot table is an interactive table where data is summarized by field. Access uses the Microsoft Excel PivotTable Wizard to create these types of forms. (See also, "Pivot Table: Create" in the Calculations part of this book.)

When you run an auto wizard, the wizard runs with default choices and without your intervention, creating a form. In all cases, you will need to save your form to disk using the File, Save command or the Save button on the toolbar.

Forms: Dialog Box Properties

You can create a form for user input and present the form as a dialog box. In this case, normally the form has no Record Source property, the text boxes are not bound, and command buttons are used for user confirmation. For a dialog box, you set the properties mentioned in "Forms and Reports: Data Properties" as follows: Allow Deletions and Allow Deletions set to No and RecordSet Type choose Snapshot.

Steps

2. Double-click the Form Selector (the square above the vertical ruler and to the left of the horizontal ruler). The Property sheet for the form opens. Change the properties mentioned in the following Form Properties section.

3. Click each control and change the properties mentioned in the following Control Properties section.

4. When finished, save, close, and test the form.

Form Properties

You need to change the following form properties to make your dialog box function property. These properties show how the entire form will look or act.

Caption (Format tab). Type the title for the dialog box that appears in the title bar.
Scroll Bars (Format tab). Shows horizontal or vertical scroll bars for the form. Set to Neither.
Record Selectors (Format tab). Enables the user to select the current record (for deletion). Set to No.
Navigator Buttons (Format tab). At the bottom of the form enable the user to go to the first, last, next, and previous records. Set to No.
Pop Up (Other tab). Normally set to No. This keeps the form on the top even when working on other forms. Set to Yes.
Modal (Other tab). Normally set to No. This keeps the focus on the dialog box. A user cannot do anything else in Access until the dialog box closes. Set to Yes.
Border Style (Format tab). Normally set to Sizable to be able to change the size of the window. Set to Dialog, which keeps the forms title bar but removes the maximize, restore, and minimize options from the window and the Control menu.
Shortcut menu (Other tab). Normally set to Yes. This enables the user to use the right-mouse click to copy, paste and do other shortcuts. Set to No.
Auto Center (Format tab). Set to Yes to have the dialog box appear in the middle of the screen.

Control Properties

The following properties are for controls that will be on your dialog box. These help the user find information or determine how the control reacts to user input.

Default (Other tab). Set the Default property of the OK command button to Yes. Only one command button can have the Default property in the form. This button has the default focus. When the user presses Enter, the procedure behind this button runs.
Cancel (Other tab). Set the Cancel property of the Cancel command button to Yes. Only one command button can have the Cancel property in the form. When the user presses Esc, the code behind the Cancel button runs.
Caption (Format tab). The text on the button. This does not appear when the Picture property is set.
Picture (Format tab). Places a picture rather than text on the button. Click in this property and choose the build button (...) to select from a list of icons.
Status Bar Text (Other tab). Place text in this property to provide the user with help in the Status Bar when the control has the focus.
ControlTip Text (Other tab). Place text in this property to provide the user with help when the user points the mouse to a control.

Forms: Multiple Records on One Form

If you want to see more than one form in the Form View, you can set the Default View property to see one line for each record or to see multiple records for small forms.

Steps

2. Move to the Default View property (on the Format tab). Change the property to Datasheet to see one record on each line or Continuous Forms to enable you to scroll through multiple records with the vertical scroll bar. Set the Default View to Single Form to only see one record at a time.

3. If you only want the Datasheet View possible in Form View, change the Views Allowed property to Datasheet. To see just a form, change the property to Form. To have both options, choose Both. When Both is set, you can use the View button in Form View to switch to either view.

Forms: Navigation

You should design your form so it is easy for a user to move around and input data. If you are inputting from a paper form, design the Access form to match the paper form with the fields in the same order if possible. When you press Tab or Enter, Access moves to the fields determined by the Tab Order. (See also "Forms: Tab Order.") In addition to clicking the appropriate field, you can also use the following procedures to move around on a form when you are in Form View.

Steps

record

3. If you are in field mode (you've tabbed into a field and the entire field is highlighted), press Ctrl+Home to get to the first field on the first record or Ctrl+End to get to the last field on the last record.

4. If you are in edit mode (you've clicked into a field and the cursor is a blinking line), press Ctrl+Home to get to the beginning of the field or Ctrl+End to get to the end of the field.

5. To switch back and forth between field and edit mode, press F2.

NOTE: You can change how certain keystrokes operate in Access. Choose Tools, Options, and click the Keyboard tab. You can set the Enter key to move to the next field (the default), move to the next record, or not do anything. You can set the left or right arrow key to move to the next character or next field (default) and specify if you want the field to be selected or go to the beginning or end of a field when you enter into the field. If you choose Cursor Stops at First or Last field, Tab and Enter will continuously cycle through the same record rather than moving to a previous or next record.

To change the cycling through a form to just include the current record and not previous or next records, you can also change the form's Cycle property (on the Other tab). For a text box, you can change the control's Enter Key Behavior property (on the Other tab) to add a new line or move to the next field.

Forms: Sorting

You can sort your forms so that the records appear in any order you want. If you have no sort specified, the records appear in the order you entered them.

Steps

Database window

Form View

2. Move to the field to sort on and click the Sort Ascending or Sort Descending button.

When you exit the form, the sort order is saved. To remove the sort order, open the form in Design View, double-click the Form/Report Selector (the square above the vertical ruler and to the left of the horizontal ruler) and delete the value in the Order By property for the form.

NOTE: If you want to sort on more than one field, click the Design View option on the View button and double-click the Form/Report Selector button on the top left of the window. Go to the Record Source property (on the Data tab). Click the build button (...) and add the fields for the form. Place the sort fields first and choose Ascending or Descending for the Sort row.

Forms: Tab Order

The order in which you move through fields on a form is called the tab order. By default, the tab order is for fields going from left to right across the screen, and top to bottom. You can change the tab order to suit your purpose, even leaving fields out of the tab order to aid in speeding up data entry when a field has data entered into it infrequently.

NOTE: When you add a field to the form, that field automatically has the last tab order. So after you redesign a form, you will probably have to reset the tab order.

Steps

form

Design View

2. For a custom tab order, click the control selector; then drag the selector into the position in the order you want and choose OK.

3. To test the tab order, switch to the Form View and tab to each of the controls.

Header/Footer: Form and Report

The header and footer of a form or report only occur once; the header occurs at the top of the first page and the footer occurs at the bottom of the last page. In Form View, the text and graphics on a form header and footer remain constant as you move from record to record. In both reports and forms, the header occurs at the top of the first printed page and the footer occurs at the bottom of the last printed page. (See also "Header/Footer: Page.")

Steps

2. If the header and footer are not shown, choose View, Form (or Report) Header/Footer.

3. Click the Label button on the Toolbox and click in the header or footer to add text. Then type the text for the label.

4. Click the Line button on the Toolbox, hold down Shift, and drag the mouse in the header or footer to add a straight line.

If you don't want to see the footer, but you want to keep the footer, delete any controls in the footer and drag the lower edge so the footer section has no height. You can also reverse this procedure to see the footer but not the header.

To remove both the header and footer, choose View, Form (or Report), Header/Footer again and confirm that you want to remove the sections.

Header/Footer: Page

The page header and footer only have to do with printing. In a form, you will not see the page header and footer in Form View, but will only see them in print preview or when you print the form. Unlike the form or report header and footer (see also "Header/Footer: Form and Report") the page header and footer appear on every page. Use the page header especially for column headings in reports.

Steps

form

report

Design View

2. If the header and footer are not shown, choose View, Page Header/Footer.

3. Use the Label control to add text.

NOTE: In a report, you can specify how the page header and report header print relative to each other. Go to the Page Header property (on the Report Properties Format tab) and choose one of the options: All Pages, Not With Report Header, Not With Report Footer, or Not With Report Header and Footer. The same options are available for the Page Footer property.

Lines: Change Width

To change the line width, you first need to create a line or go to an existing line. (See also "Lines: Create.") You might need to change the line width to make the line more noticeable.

Steps

form

report

Design View

2. Click a line or rectangle to select it.

3. Choose the drop-down arrow on the Line/Border Width button on the Formatting toolbar and choose one of the widths.

You can also change the border around a control by using the same procedure.

NOTE: If the line does not appear, make sure the Line/Border Color button is not set to Transparent or to the same color as the background.

Lines: Create

Lines add organization to your form or report. You can use them to add a visual break between column headings and the detail section of your report or between each record.

Steps

2. Click the Line button to draw a line or the Rectangle button to draw a rectangle.

3. To make the line straight, hold down Shift as you drag the line horizontally or vertically. To draw a rectangle, drag from the upper left corner to the bottom right corner of the area.

To change the color of the line or rectangle, choose an option on the Line/Border Color button. To change the width of the line or rectangle, choose an option on the Line/Border Width button. To change the effect (raised, sunken, and so on), choose an option on the Special Effects button.

Reports: Create with AutoReport

The quickest way to create a report is to use the AutoReport feature. There are two AutoReport options when you use the New button on the Reports tab, AutoReport: Tabular and AutoReport: Columnar. A tabular report presents your records in rows while a columnar report presents the fields in each record going down the page, similar to a label. Tabular reports are much more common than columnar reports.

Steps

Database window

command button

2. On the New Report dialog box choose the name of the table or query to base the report on in the drop-down combo box.

3. Double-click AutoReport: Tabular or AutoReport: Columnar. Access will create a report for you, placing all the fields, and show you the report in Print Preview.

4. Click the Design View button to modify the report design or choose File, Save to save the report.

NOTE: If you want to create a columnar report, you can also select the table or query in the Database window and then click the AutoReport option on the New Object drop-down button.

Reports: Create with Design View

In rare cases, you might create a report from scratch and place all the controls on the report yourself. However, since the task of lining up the controls can be tedious, you will be better off using AutoReport and removing the controls you don't need. An alternative is to use the Report Wizard. If you want to create a report with few controls or if your report only contains subreports, use Design View. (See also "Controls: Add," "Controls: Align," "Reports: Create with AutoReport," "Reports: Create with Report Wizard," "Subform/Subreport: Create.")

Steps

Database window

Design View

dialog box

2. If you are going to base your report on a table or query, choose one in the New Report dialog box. If you are only using this report as a container to hold multiple sub-reports, you can leave the table or query box blank.

3. Double-click the Design View option. Access opens the blank form in the Design View.

4. Add labels, text boxes and other controls.

5. Click the Save button on the Report Design toolbar or choose File, Save and enter the name of the form in the Save As dialog box, then choose OK.

Reports: Create with Report Wizard

The Report Wizard gives you the most flexibility in creating reports. You will be prompted for the fields you want from one or more tables or queries, how you want to group and sort your data, if you want to add summaries, and the layout and style for your report. While you can accomplish these features in Design View, the job is often tedious. However, if you have a simple report to create, consider using AutoReport. (See also "Reports: Create with AutoReport.")

The steps and options of the wizard change depending on which options you select.

Steps

Database window

table

query

data source

2. In the first step of the wizard, double-click the fields you want to see on your report in the Available Fields list box. For any related tables, select those tables from the Tables/Queries list box, and add the additional fields, then choose Next.

If you get an error message such as Subscript out of Range, you chose fields from two unrelated tables. Double-click the incorrect fields in the Selected Fields list box to remove them.

3. Depending on the fields you chose, the next two steps of the wizard will ask you how to group your data. If the fields you chose come from different tables, Access will ask you how you want to view your data. Specify the table used to group your data and choose Next.

On the next step, select the field(s) used to group your data, and position them in a priority order. If you want to change the group interval (first letter for text, months or years for dates, or range for numbers) choose the Grouping Options button, make your choices, and choose OK. Choose Next.

4. Select the sort field(s) of your data with the top sort field being the primary sort key; click the Sort button to specify ascending or descending sorts.

If you chose any grouping options in Step 3, the Summary Options button might be available. Choose that button to find the sum, average, minimum, or maximum of numeric fields, show a summary or detailed records, and if you want to calculate percents. Choose OK to return to the sort step. From the sort step, choose the Next button.

5. Select a layout and orientation for your report, then choose Next. Select a presentation style for your report, then choose Next.

6. Enter a title for the report, and click either Preview the Report or Modify the Report's Design in the last step of the Report Wizard; then choose Finish.

Access creates the new report and saves it to disk. If you selected Preview, Access opens the report in the Preview mode; otherwise, it appears in the Report Design window.

To switch between views of your report, select the appropriate command from the View menu, or from the View button's drop-down menu in the Report Design toolbar.

Reports: Grouping

When you want to see subtotals for numeric values, and you want to see the records that make up the subtotals as well, you need to use the grouping feature of reports. In a query, you can see the records or the subtotals only, but not both. (See also "Grouping in Queries" in the Calculations part of this book.) When you group records, all of one value group together, then there is a break (usually a group footer with subtotals) and then the next value group together. You can also have a group header with text introducing the group. In addition to doing subtotals, you can also group for lists of data, such as an employee phone list where all the A's are grouped together, all the B's, and so forth.

You can create a grouped report through the Report Wizard. (See also "Reports: Create with Wizard.") When you create a report with the wizard, Access creates the group headers and footers for you as well with calculated expressions for your labels and summaries. If you need to add grouping to a report, follow the procedures in these steps.

Steps

1. Open the report in Design View and click the Sorting and Grouping button if the Sorting and Grouping window is not visible.

2. In the Field/Expression box, choose a field from the drop-down list or type an expression.

3. In the Sort Order box, choose Ascending or Descending for the order in which the group will appear.

4. If you want a group header or footer section to appear for this group, choose Yes for either in the lower half, Group Properties section of the Sorting and Grouping window. If you do not choose Yes for either the header or footer, you do not get grouping. As soon as you choose a header or footer, Access puts a grouping icon to the left of the field name.

5. Depending on the field data type you are grouping, the options in Group On will change. If the field is a date, choose a time unit such as Year or Month, and then type how many of those units you want to group together in the Group Interval box. For example, if you want to group on five-year intervals, choose Year in Group On and type 5 in Group Interval.
If your field is a text or numeric value, you can choose Each Value in the Group On box. Then for every change in the value, you get a group header or footer. You can also choose prefix characters in the Group On box and then type the number of characters in the Group Interval box. If you are typing an alphabetical list, choose prefix characters and set the Group Interval to 1. If the first three characters of an employee ID determine the department, type a Group Interval of 3.

6. Set the Keep Together property to No to not worry about where the page breaks. Set it to With First Detail to keep the group header with the first record. If you have a small group that you want all to fit on one page, choose Whole Group.

When you finish setting the group properties, save your report and preview to test your settings. To turn off grouping, change both the Group Header and Group Footer options to No.

NOTE: You can type an expression in the Sorting and Grouping window instead of a field name. For examples, see the Summary of Sales by Quarter and Employee Sales by Country in the sample Northwind database.

Reports: Modify

To modify a report, you must switch to the Report Design View. In Design View, you can add and remove sections or controls, format the report or any object contained therein, adjust properties, and so on. To change the grouping or sorting on a report, see also "Reports: Grouping."

Steps

Database window

2. Remove (press Delete) or add controls (using the Toolbox or Field List); change properties in the appropriate Property sheet; remove or add sections using commands on the View menu; and format objects using the Formatting toolbar.

3. When done modifying your report, select File, Save.

To save the resulting report as a different object, select File, Save As.

Access saves your report to disk. If you create a new report, that report's name appears in the Reports tab of the Database window.

Reports: Sorting

Your reports are based on tables or queries. If your report is based on a query and you change how the query is sorted, the report might sort in the new order determined by the query. For this reason, you can use Report Design View to sort records independent of the underlying query's sort. Reports also enable you to group records. (See also "Reports: Grouping.")

Steps

2. In the Field/Expression box, choose a field from the drop-down list.

3. In the Sort Order box, choose Ascending or Descending for the order the group will appear.

4. Repeat Steps 2 and 3 for additional sort levels.

To change the sort order if you have more than one sort, move the mouse pointer until it becomes a black right arrow to the left of the field name. Click to select the row and then use the white arrow mouse pointer to drag the row to a new position.

Sections: Add and Remove

The sections of a form or a report enable you to set up a page for display, or to provide a particular kind of layout. Through proper use of sections, you can provide information that appears on every page at the top or bottom, at the beginning or end of a form or report, repeats for each record, repeats for each group of records, and so on.

Forms contain the following sections: Form headers and footers, Page headers and footers, and a Detail Section.

You can also create sections in subforms that appear inside forms: headers, detail sections, and footers.

Reports can contain all of the same sections as forms do (although they are called Report headers, and so on), but a report can also contain bands for grouped records. When a record set is grouped by a field, each value in that group becomes a group of records. Each group will appear in its own detail section, and can be preceded by a Group header and followed by a Group footer. (See also "Reports: Grouping.")

Steps

form

report

Database window

2. Choose View, Page Header/Footer or View, Form (or Report) Header/Footer to add those items to view for a form.

3. Choose those commands again to remove the check mark and eliminate them from your form or report.

4. Click the Save button to save your changes.

You can set properties for sections that make them hidden, or set their height to 0 if you don't want them to appear.

Subform/Subreport: Create

A subreport is a report that is embedded inside another report. The main report can be bound or unbound to a table, query, or a SQL statement. A subform is a form that is embedded inside another form. Usually, the main part of the form can have multiple records that are related to the records in the subform.

Unbound reports serve as container devices into which subreports can be placed when the subreports are unrelated to one another and derive their data from different sources. You use a bound main report when you want to use subreports that use the main report's data source. An example of an unbound main report would include three unrelated subreports summing up revenue by each employee, revenue by month, and revenue by division.

Steps

Design View

Toolbox

2. Click the Subform/Subreport tool in the toolbox. Make sure that the table relationships are correct before proceeding.

3. Click the location of the subreport or subform.

4. Follow the directions in the Subform Wizard for the data source (table/query or existing form or report), fields, relationships, and subform/subreport name.

When you finish, a subreport or subform control is added to your report. The wizard also creates a separate report or form that is displayed in the Database window.

NOTE: The link between the main form/report and the subform/report is through two (Data tab) properties on the subform/report control. The Link Child Fields and Link Master Fields properties should be related fields between the two data sources. If you want the subform/report to expand to include all data when printed, make sure that the Can Grow (Format tab) property is set to Yes.
In most cases, you also want the subform to appear as a datasheet inside of the form. To change this property, go to the subform in Design View (you can double-click the subform control within the main form) and check that the Default View property (on the Data tab) is set to Datasheet. You can also set this property to Single Form, although this is quite a bit less common for a subform.

Toolbox: Display and Use

You see the Toolbox normally when you are in the Form or Report Design View. The toolbox contains buttons that let you create and manage controls.

Steps

2. To use a tool in the Toolbox, click the button for that tool. To use a tool repeatedly, double-click the tool first. That tool stays locked until you either click another tool or press the Esc key.

Each tool has its own default properties. To see or modify what they are, click the tool and choose the Properties button on the toolbar.

Назад в раздел

Emanual.ru – это сайт, посвящённый всем значимым событиям в IT-индустрии: новейшие разработки, уникальные методы и горячие новости! Тонны информации, полезной как для обычных пользователей, так и для самых продвинутых программистов! Интересные обсуждения на актуальные темы и огромная аудитория, которая может быть интересна широкому кругу рекламодателей. У нас вы узнаете всё о компьютерах, базах данных, операционных системах, сетях, инфраструктурах, связях и программированию на популярных языках!


Copyright © 2001-2024

Реклама на сайте

Microsoft® Access 97 Quick Reference

- 6 - Forms and Reports

Chart: Axes Modify

Steps

Chart: Colors Display

Steps

Chart: Create

Steps

Chart: Data Labels

Steps

Chart: Data Source Change

Steps

Chart: Edit

Steps

Chart: Grid Display

Steps

Chart: Labels Enter

Steps

Chart: Legend Display

Steps

Chart: Titles Add

Steps

Chart: Trendlines

Steps

Chart: Type Change

When to Change Chart Types

Steps

Controls: ActiveX Add

Steps

Controls: ActiveX Register

Steps

Controls: Add

Steps

Controls: Align

Steps

Controls: Bound Control Create

Steps

Controls: Calculation Create

Steps

Controls: Change Control Type

Steps

Controls: Check Box Create

Steps

Controls: Colors

Steps

Controls: Combo Box Create

Steps

Controls: Combo Box Not in List

Steps

Controls: Command Button Create

Steps

Controls: Copy

Steps

Controls: Data Source

Steps

Controls: Defaults Change

Steps

Controls: Delete

Steps

Controls: Display or Hide

Steps

Controls: Font Size and Face

Steps

Controls: Labels Create

Steps

Controls: List Box Create

Steps

Controls: Move

Steps

Controls: Option Button Create

Steps

Controls: Option Group Create

Steps

Controls: Properties Change

Steps

Controls: Select

Steps

Controls: Size

Steps

Controls: Space

- 6 -
Forms and Reports