Displaying data located in a remote database table on a local form.

Posted on 2011-04-21
Last Modified: 2012-05-11
I am relativly new to Access 2007.

I have two tables, one local as TestBooking and one remote as WorkCard.

I have set up a relationship as “TestBooking.Works Order No”  with “WorkCard.WONo”.
Works Order has a primary key.

The relationship joins have been set to “Only include rows where the joined fields from both tables are equal.”

A single value is available in [Works Order No] and [WONo] (as “77622”, in text format in both fields).
I generated a Query that included these fields and also included a couple of other fields.
The Query reported correctly “77622” in BOTH fields (as expected) and successfully included the data in the other requested fields.

I have a form with these fields included.

In the Control Source dropdown box of the properties I would expect to see the fields from the remote table. But they are not there.

If I use the expression builder the fields of the remote table (WorkCard) are visible.
I created the expression = [WorkCard]![WONo]

But when I return to “Form view” the result is #Name?

The data in the remote table (WONo) seems impossible to access.
How can I get data located in the remote to display on my form?

I have asked a similar question on EE recently and as a result have progressed.
Question by:usatrfe
    1 Comment
    LVL 24

    Accepted Solution


    to see the wanted fields in the ControlSource dropdown you only need to add a query to the form's RecordSource property. The query must contain all the fields you want, if they come from different tables it needs a JOIN between them. The query should display all the fields you want if you open it directly in the navigation bar. After adding the query to the RecordSource of the form you can add any field of this query to your form (the simplest way is to open the field list in the ribbon which displays all available fields, you can then drag and drop the field to the form). But this method will not automatically create comboboxes for lookup tables. This must be done by yourself, if you want to create a lookup combobox you can add a new combobox and use the wizard to create such a combobox.



    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Lync meeting or Lync conferencing is what many organizations would like to deploy to allow them save money. But companies are now giving up for various reasons, one of which is that they cannot join external meetings (non-federated company meetings)…
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
    The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now