Access Forms, Fields From Multiple Tables

Aug 23, 2015 by azurous in Access

This article explains how you can create a form which gets its data from multiple tables. Note that access forms can only get data from one table or query. Attempting to use expressions that reference fields from multiple table will result in the #Name? error. A workaround would be to create a query with fields from the different tables and to bind the form to the query.

–

Contents

Example:

Consider the access database below:

The database has 2 tables:

Table1
Table2

These tables both have the field:

Field1

We want to create the form below:

We want the first textbox to display the value from Field1 of Table1 and the second textbox to display the value of Field1 from Table2. Attempting to use expressions such as the ones below will result in a #Name? error:

=[Table1]![Field1]

=[Table2]![Field1]

The workaround would be to create a query with data from both tables and to bind the form to this query.

–

Step 1:

Create a query with 2 fields. One with data from Field1 of Table1, and the other with data from Field2 of Table2:

Result:

Note: This query is just for demonstrative purposes. Since the 2 tables don’t have any relationship, the resulting query doesn’t make much sense.

–

Step 2:

Create a form which gets its record source from the query:

Note: If a form is already existent and you want to change it so that it gets its data from the query, open the form in design view, and select the from from the property sheet drop down list:

Change the form’s Record Source to the query (Query1 in this example):