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

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.
Who is Participating?
BitsqueezerConnect With a Mentor Commented:

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.


Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.