An Easy Way to Add a Field to a Report in Excel

You can quickly add fields to a form or report by using the Field List pane. When you double-click a field in the Field List pane (or if you drag a field from the list to your form or report), Microsoft Access automatically creates the appropriate control to display the field — for example, a text box or check box — and then binds the control to that field.

If you do not have an existing form or report to add a field to, see Introduction to forms or Introduction to reports to help you get started.

What do you want to do?

• Add a field to a form or report by using the Field List pane

• Add a field to a form or report without using the Field List

• Learn more about the Field List pane

Add a field to a form or report by using the Field List pane

1. Open your report in Layout view or Design view.

   If the Field List pane is not displayed, do one of the following:

   • On the Design tab, in the Tools group, click Add Existing Fields.
     -OR-

   • Press ALT+F8.

2. In the Field List pane, find the table containing the field you want to add. To show or hide the list of fields in each table, click the plus (+) or minus sign (-) next to the table name.

   Note:If the database does not contain any tables, the Field List pane is empty.

3. When you find the field you want to add, do one of the following to add it to the form or report:

   • Double-click the field.

   • Drag the field from the Field List pane to the form or report.

   • Hold the CTRL key and click several fields, and then drag them all to the form or report at the same time.

     Note:This technique can be used only on fields in the Fields available for this view section of the Field List pane.

   • Add a range of fields from the Field List pane to the form or report by doing the following:

   • Click the first field in the range.

   • Press and hold the SHIFT key, and then click the last field in the range.

     Access selects the fields you clicked, as well as all the fields in between.

   • Drag all the selected fields to the form or report.

     Note:This technique can only be used on fields in the Fields available for this view section of the Field List pane.

4. Access creates the appropriate control to display each field, and also binds the control to the field. In addition, Access creates an attached label for the control. You can edit the label by clicking it once to select it, and then clicking it again to place the insertion point in the label. Then, you can edit the label in the same way that you would edit text in a document.

5. If you want a field displayed in a different type of control other than the one that Access creates automatically, you can change the control to another type. However, you can only change it to a control type that is appropriate for that field. For example, you can change a combo box to a text box or a list box, but you can't change a combo box to a command button.

   Change a control from one type to another

   1. Open the form or report in Design view by right-clicking it in the Navigation Pane and then clicking Design View.

   2. Right-click the control you want to change, and then click Change To.

      If the Change To menu item is unavailable, there are no other control types that are appropriate for this field. Otherwise, Access displays a list of control types.

   3. Click one of the available control types to change the control to that type.

      The control remains bound to the field, but you might have to set some properties to make the new control type work the way you want. To display the property sheet for a control, click the control to select it, and then press F4.

Top of Page

Add a field to a form or report without using the Field List

In general, using the Field List pane is the easiest way to add a field to a form or report. However, you can also add a field by creating a control and then binding it to a field. The following procedure shows you how.

1. In the Navigation Pane, right-click the form or report and then click Layout View or Design View.

   Control Wizards    You can use the Control Wizards to help you create command buttons, list boxes, subforms, combo boxes, and option groups. A Control Wizard asks you a series of questions about how you want the control to look and operate, and then it creates the control and sets the control's properties based on your answers. On the Design tab, in the Controls group, click the down-arrow next to the Control Gallery. If Use Control Wizards is not highlighted, click it to highlight it.

   

   If you would rather create controls without the help of the wizards, click Use Control Wizards so that it is not selected.

   

2. On the Design tab, in the Controls group, click the tool for the type of control you want to add.

   To determine the name of a tool, position the pointer over it. Access displays the name of the tool.

3. Click in the form or report design grid where you want to position the upper-left corner of the control. Click once to create a default-sized control, or click the tool and then drag in the form design grid to create a control of the size that you want.

4. If you have selected Use Control Wizards and the control you are placing has a wizard associated with it, the wizard starts and guides you through the settings for the control.

5. If you don't position the control perfectly on the first try, you can move it by doing the following:

   1. Click the control to select it. If there is a label associated with the control, hold down the CTRL key and then click the label to select it as well.

   2. Position the pointer over the control until it turns into a move pointer .

   3. Click and drag the control to the location that you want.

If you use a control wizard, the wizard might contain steps to help you bind the control to a field. If the wizard does not bind the control to a field, and control is of the type that can display data (a text box or combo box, for example), you must enter a field name or expression in the Control Source property for the control before it will display any data. Use the following procedure to bind a control to a field.

1. Display the property sheet for the control by clicking the control, and then pressing F4.

2. On the Data tab of the property sheet, click the drop-down arrow next to the Control Source property and select the field you want displayed in the control. You can also type an expression in the Control Source box.

   For more information about expressions, see the article Build an expression.

Top of Page

Learn more about the Field List pane

The following list contains tips about working with the Field List pane that may be helpful to you as you add fields to your form or report. To display the Field List pane, open a form or report in Layout view or Design view, and then press ALT+F8.

• If the form or report's Record Source property is empty, and you drag a field from the Field List pane to the form or report, Access automatically fills in the form or report's Record Source property for you.

• If the form or report's Record Source property is a SELECT statement or a table name, you can toggle the Field List pane display between the following two states:

  • All database fields    Displays the Fields available for this view section, the Fields available in related tables section (if such tables exist), and the Fields available in other tables section (if such tables exist).

  • Record source fields only    Displays only the Fields available for this view section.

    To switch between the two modes, click either Show only fields in the current record source or Show all tables at the bottom of the Field List pane.

    Note:If you have set the form or report's Record Source property to a saved query, the Field List pane displays only the Fields available for this view section (there is no Show all tables option at the bottom of the Field List pane). To edit the record source, right-click any one of the fields, and then click Edit Record Source.

• If you have set the form or report's Record Source property to a table, and you then drag a field from the Fields available in related tables section of the Field List pane to the form or report, Access changes the Record Source of the form or report to a SELECT statement that includes the field you are adding. If Access cannot determine how to relate the tables, it displays the Choose Relationship dialog box so you can choose the appropriate relationship.

• If you drag a field from the Fields available in other tables section of the Field List pane to the form or report, Access displays the Specify Relationship dialog box. You must use this dialog box to specify a relationship between the other table and the form or report's record source. If you are not sure which fields to enter in this dialog box, you might want to click Cancel and then examine your table relationships.

  For more information about relationships, see Guide to table relationships or Create, edit or delete a relationship.

  It is possible that a field in the Fields available in other tables section of the Field List pane is indirectly related to the record source of the form or report through one of the tables in the Fields available in related tables section. If this is the case, dragging a field from one of the related tables to the form or report may cause one or more tables to move from the Fields available in other tables section to the Fields available related tables section.

• If the record source of the form or report is a SELECT statement or a saved query, you can view and edit the record source in the Query Builder by right-clicking any field in the Field List pane and then clicking Edit Record Source. If the record source is a table, this command invokes the Query Builder on the table. Access asks you if you want to create a query based on the table; click Yes to change the record source to a query, or No to leave the record source unchanged.

• You can open a table in Datasheet view by clicking Edit Table next to the table name, or by right-clicking the table name in the Field List pane and then clicking Open Table in Datasheet View.

• You can use the Field List pane to add a Lookup field to a table by doing the following:

  1. Click Edit Table next to the table in which you want to add the Lookup field.

     The table is displayed in Datasheet view.

  2. Drag a field from the Field List pane to the datasheet.

  3. The Lookup Wizard starts. Follow the instructions in the wizard. On the last page, click Finish.

• You can undock the Field List pane from the right side of the Access work area by positioning the pointer in the title bar of the Field List pane until it turns into a move pointer , and then dragging the Field List pane to the left. You can position the list anywhere in the work area, or you can dock it to the left side of the work area by dragging it all the way to the left.

Top of Page

coilsunwast.blogspot.com

Source: https://support.microsoft.com/en-us/office/add-a-field-to-a-form-or-report-0d5e2740-3b39-4341-8ec2-32c9f82cbfce

Share this post