Show All

Hide All

You can use a query to supply data to a form or report. You can use a query when you create the form or report, or you can change an existing form or report by setting its Record Source property. When you set the Record Source property, you can either specify an existing query, or you can create a new query to use.

If you use a query as the record source, you might not be able to edit the data. Before you use a query as your record source, you should consider whether you need to edit data.

This article explains how to set the Record Source property for an existing form or report, and provides information about when you can and cannot edit query data.

This article does not explain how to create a form or a report. For help creating forms and reports, see the articles Roadmap to creating a form and Create a simple report.

• Use an existing query as the record source of a form or report

• Create a query as the record source of a form or report

• Editing data from a query

Use an existing query as the record source of a form or report

In Design view, set the Record Source property to an existing query that you want to use.

1. Open the form or report in Design view.

   If the property sheet is not already open, press F4 to open it.

2. In the property sheet, on the Data tab, click the Record Source property box.

   

3. Do one of the following:
   • Start typing the name of the query that you want to use.

     Access automatically fills in the name of the object as you type.

     - or -

   • Click the arrow and then select the query that you want to use.

 Top of Page

Create a query as the record source of a form or report

In Design view, use the Build button () in the Record Source property box to create a new query to use as the record source.

1. Open the form or report in Design view.

   If the property sheet is not already open, press F4 to open it.

2. In the property sheet, on the Data tab, click the Record Source property box.
3. Click .

   

   A new query opens in Design view.

4. Design the query, and then save and close it.

   For help designing a query, see the article Select data by using a query, and the See Also section.

 Top of Page

Editing data from a query

One of the main reasons you use forms is to enter and edit data. Some queries do not support data editing. Before you use a query as the record source for a form, you should consider whether you can edit the query data.

When can I edit data from a query?

You can edit the data from a query if:

• The query is based on only one table.
• The query is based on two tables with a one-to-one relationship (one-to-one relationship: An association between two tables in which the primary key value of each record in the primary table corresponds to the value in the matching field or fields of one, and only one, record in the related table.) between them.
• The RecordsetType property of the form is set to Dynaset (Inconsistent Updates), and none of the conditions in the following section applies.

 Note   Even when you can edit data in a query, some of its fields may not be available for editing. Such cases are listed in the following section.

When can I not edit data from a query?

You cannot edit the data from a query when:

• The query is a crosstab query (crosstab query: A query that calculates a sum, average, count, or other type of total on records, and then groups the result by two types of information: one down the left side of the datasheet and the other across the top.).
• The query is a SQL-specific query (SQL-specific query: A query that consists of an SQL statement. Subqueries and pass-through, union, and data-definition queries are SQL-specific queries.).
• The field you are trying to edit is a calculated field (calculated field: A field, defined in a query, that displays the result of an expression rather than displaying stored data. The value is recalculated each time a value in the expression changes.). In this case, you may be able to edit data from the other fields in the query.
• The query contains a GROUP BY clause.

 Top of Page

• Can the Report Update based on a Query being filtered?
• Query Builder - What's the trick?