The combo box needs to be bound to the Supplier Number column in the
subform's underlying table or query. But if you want a text box then you can
either do as I originally advised and as the subform's RecordSource property
use a query which joins the Suppliers table to the Customers table and bind
the text box to the Supplier Name column, or use an unbound text box whose
ControlSource is an expression which calls the DLookup function:

=DLookup("[Supplier Name]", "[Suppliers]", "[Supplier Number] = " & [Supplier
Number 1])

or if Supplier nuber is a text data type rather than a number;

=DLookup("[Supplier Name]", "[Suppliers]", "[Supplier Number] = """ &
[Supplier Number 1] & """")

I've called the column in the subform's underlying table Supplier Number 1 in
this instance as you said you have three columns each referencing the
Suppliers table, so you'd do the same with each. This also means that if you
do use a query to pull in the supplier names the joins would probably have to
be LEFT OUTER JOINS to allow for a Null in any of the three columns.

The fact that you have three columns referencing Suppliers does suggest
however that the logical model is flawed. What you seem to be doing here is
representing different attributes of the relationship type (the type of
service provided in your case) between Customers and Suppliers as column
headings in Customers. Attributes are data, and data should only be stored
as explicit values at column positions in rows in tables. This was Codd's
Rule 1 (the Information Rule) when he first proposed the database relational
model back in 1970. The way to model this relationship would be as a table
with foreign key columns referencing the primary keys of Customers and
Suppliers, and a third column representing the nature of the realtionship.
This third column would also be a foreign key, in this case referencing a
table of the different types of relationship (i.e. the services) between
customers suppliers. The relationship type is therefore a ternary one
between the three entity types. So a customer with all three types of
relationship to suppliers would be represented as three rows in this table,
each with the same customer number, different values in the service type
column and either the same or different values in the supplier depending on
whether the services to that customer are provided by the same or different
suppliers. If a customer cannot receive the same service from more than one
supplier at any one point in time this should be enforced by means of a
unique index on the Supplier number and service type columns, in combination,
not individually.

Ken Sheridan
Stafford, England

--
Message posted via http://www.accessmonster.com