-----Original Message-----
I am trying to set up a table where I would input a
customer number in one field and the next field would
populate with that customers name. I have another table
with the customer's number and Name linked. I cannot seem
to specify the control source correctly in tables.
I have done this in forms by changing the control source,
but I do not know how to convert a form to a table.
Access is a relational database; you don't need or want to
store a name in addition to the key field. The customer
number uniquely defines each customer record.
I assume, however, that you DO want to DISPLAY the name
and perhaps other fields from the Customer table on your
form on entry of the number.
To do this, you can either:
- Create a query including all the fields from the MANY
side of the relationship (including the foreign key to
Customers), plus the fields from Customer that you want to
display when the customer number is entered. Base your
form on the query. You can optionally disable the
displayed Customer fields to avoid accidentally
overwriting them.
- Create a subform based on Customers including the fields
you wish to display. Drag this form to your Detail form
in Design view, and set the Link Master Fields and Link
Child Fields properties.
Try to design your tables so that they are one "thing".
Each field then, is an attribute that describes
the "thing", e.g.,
Customers Table
CustomerID AutoNumber (Key Field)
CompanyName
Address
City
State
Zip
....
Orders
OrderID
Customer (Foreign Key to Customers)
OrderDate
OrderDetails
OrderDetailID
OrderID (Foreign Key to Orders)
ProductID
QtyOrdered
The foreign keys here are the key field of another table.
I think of them as a metaphorical "key" which "unlocks"
the door to the related table, enabling access to the data
in the related record.
Look in any good Access reference book under "Database
design" or "Normalization" for a further description of DB
design.
HTH
Kevin Sprinkel
Becker & Frondorf
