Access Forms, Fields From Multiple Tables
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.
The database has 2 tables:
These tables both have the field:
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:
The workaround would be to create a query with data from both tables and to bind the form to this query.
Create a query with 2 fields. One with data from Field1 of Table1, and the other with data from Field2 of Table2:
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.
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):
Click on the textboxes and change their control source to the following expressions:
You can download the sample file used in this article from the link below:
- #Name? Error Referencing Fields Access Forms, Expression Builder
- Access Using Expressions (Calculated Fields) In Form Controls