How to display a field from a foreign table using DLookup?

[Guest]

I am trying to display a field (DestinationType) from a foreign table on my
form header.

Table: GuatemalaDB
Form: TextForm
Foreign key: Destination
Foreign key properties-------
Row Source Type: TABLE/QUERY
Row Source: SELECT GuatemalaDestinations.GuatemalaDID,
GuatemalaDestinations.Destination FROM GuatemalaDestinations;

Foreign table: GuatemalaDestinations
Field: DestinationType
Foreign table key: GuatemalaDID

In design view, I have pasted variations on the following into a text
control that I created on the form header:

=DLookup("[DestinationType]", "GuatemalaDestinations", "[GuatemalaDID] =" _
& Forms![TextForm]!GuatemalaDID)

=DLookup("[DestinationType]", "GuatemalaDestinations", "[GuatemalaDID] =" _
& Forms![TextForm]!Destination)

Neither of these works and I get the following message:
The expression you entered contains invalid syntax.
You omitted an operand or operator, you entered an invalid character or
comma, or you entered text without surrounding it in quotation marks

I have no problem selecting records from the foreign table and displaying
them on my subform using the foreign key, but I wish to also show
DestinationType on the form header.

Thank you

Peter

 

[Marshall Barton]

I am trying to display a field (DestinationType) from a foreign table on my
form header.

Table: GuatemalaDB
Form: TextForm
Foreign key: Destination
Foreign key properties-------
Row Source Type: TABLE/QUERY
Row Source: SELECT GuatemalaDestinations.GuatemalaDID,
GuatemalaDestinations.Destination FROM GuatemalaDestinations;

Foreign table: GuatemalaDestinations
Field: DestinationType
Foreign table key: GuatemalaDID

In design view, I have pasted variations on the following into a text
control that I created on the form header:

=DLookup("[DestinationType]", "GuatemalaDestinations", "[GuatemalaDID] =" _
& Forms![TextForm]!GuatemalaDID)

=DLookup("[DestinationType]", "GuatemalaDestinations", "[GuatemalaDID] =" _
& Forms![TextForm]!Destination)

Neither of these works and I get the following message:
The expression you entered contains invalid syntax.
You omitted an operand or operator, you entered an invalid character or
comma, or you entered text without surrounding it in quotation marks

I have no problem selecting records from the foreign table and displaying
them on my subform using the foreign key, but I wish to also show
DestinationType on the form header.

Since you included a RowSource based on the same table you
are calling the "foreign table", I guess you want to display
the DestinationType value in a text box after you have
selected an item in a combo. If this is what you are trying
to do, then you do not need the DLookup or any code.

Change the RowSource query to include the type field:

SELECT GuatemalaDID, Destination, DestinationType
FROM GuatemalaDestinations
ORDER BY Destination

and change the combo box's ColumnCount property from 2 to 3.

Then the text box can display the type by using the
expression:

=comboboxname.Column(2)

 

[Guest]

I'm a novice and I learnt something useful today.

Thank you.