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.


Example:

Consider the access database below:
Database

The database has 2 tables:

  • Table1
  • Table2

These tables both have the field:

  • Field1

We want to create the form below:
Form

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:
Query
Result:
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:
Form, 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:
Property Sheet, Form
Change the form’s Record Source to the query (Query1 in this example):
Record Source

Click on the textboxes and change their control source to the following expressions:

=[Table1.Field1]

=[Table2.Field1]

Control Source
Result:
Result2

You can download the sample file used in this article from the link below:

See also:

If you need assistance with your code, or you are looking for a VBA programmer to hire feel free to contact me. Also please visit my website www.software-solutions-online.com

One thought on “Access Forms, Fields From Multiple Tables”

  1. Mary says:

    Hi,

    I tried this example but could not get it to work in my database. I have an inventory tracking database with various forms. The main form allows the user to pick an JobID number which leads to the next level of forms where it chains the JobID number on each form ( i.e. the first form has a combo box and the other forms have textboxes that the user cannot change). I have used the following code on first and succeeding forms:

    Main form:
    Private Sub Combo65_AfterUpdate()
    If IsNull(TempVars(“JobID”)) Then TempVars.Add “JobID”, Me.Combo65.Value
    TempVars(“JobID”) = Me.Combo65.Value
    End Sub

    then on the succeding forms, on OnLoad Event:
    private sub form_load()
    Me.textboxID = Tempvars(“JobID”)

    Thanks so much.

Leave a Reply

Your email address will not be published. Required fields are marked *

Search