CODEWITHSUNDEEP
ms-accessforeign-keys
Nick Dandoulakis
Nick Dandoulakis

Reputation: 43110

How can I display a *foreign* field value in a text box?

How do I bind a text box with a field, which doesn't belong to form's "Record Source" table, through the Design View?

Example: I have "Order.cust_id" (Record Source=Order) and I want to display "Customers.name". I believe it is trivial but I have no experience with MS Access. I tried to use the text box "Control Source" property but no luck.

Upvotes: 1

Views: 5732

Answers (3)

Fionnuala
Fionnuala

Reputation: 91336

You can use DlookUp as the control source of a textbox:

 =DlookUp("[Name]", "Customer", "ID=" & Cust_ID)

Syntax: What to look up, table name, where statement

The Where statement should follow the rules for Jet SQL, which means that you must use delimiters if the field is text or date format.

Note that Name is a very bad name indeed for anything. I suggest you rename the field immediately before things get worse.

It can be useful to know the error(s).

Upvotes: 2

Mark3308
Mark3308

Reputation: 1333

One method would be to convert the text box to a combo box. Then set the row source to include both the cust_Id and the Customer.Name from the customer table. SQL statement example

Select Cust_ID, Name From Customer 
Order By Name;

By setting the number of columns to 2 and the column widths; the first column as zero (i.e. "0;6") then the foreign key would be hidden from the user and the customer name would be displayed.

Note this method does force you to have limit to list set to true.

Also you do end up with a drop down list which may not be what you want.

Upvotes: 3

Peter Perháč
Peter Perháč

Reputation: 20782

You could create a new View (e.g. OrdersAndCustomerNames), select all the columns you want to use in the form, then instead of using the Order table as Record Source, you would just switch to OrdersAndCustomerNames. You say you have no experience with MS Access, so I am guessing you are not building anything huge and overly complicated, so I would do it this way. I am quite sure it can be done more elegantly but this will do for now.

Upvotes: 0

Related Questions