Populating fields from a separate table

[EJH]

I have a Database that has three tables. One of the three is just a table
that contains three fields and is filled with reference information. One
field is 3-Digit(primary key), the next is Office, and the last is Area.
The next table has the same type of headings plus several other fields but
is not filled with data. I want to be able to create a form and enter a 3
digit number in the 3-Digit field of the second table and have it extract
information from the first table to fill in the Office and Area fields of
the second table. There will be more information in the second table, but I
just need to know how to populate these two fields. I have tried different
functions, including a Dlookup, but I must be putting it in the wrong place
or creating the syntax wrong. I am a newbie and appreciate any help.
Thanks.

 

[John Vinson]

I have a Database that has three tables. One of the three is just a table
that contains three fields and is filled with reference information. One
field is 3-Digit(primary key), the next is Office, and the last is Area.
The next table has the same type of headings plus several other fields but
is not filled with data. I want to be able to create a form and enter a 3
digit number in the 3-Digit field of the second table and have it extract
information from the first table to fill in the Office and Area fields of
the second table. There will be more information in the second table, but I
just need to know how to populate these two fields. I have tried different
functions, including a Dlookup, but I must be putting it in the wrong place
or creating the syntax wrong. I am a newbie and appreciate any help.
Thanks.

The solution is:

Don't.

There is NO need to store the Office or Area fields in the second
table - AT ALL. There are many good reasons NOT to do so.

Relational databases use the "Grandmother's Pantry Principle":

"A place - ONE place! - for everything, everything in its place".

Store the Area and Office in the reference table, only; if you need to
see them in conjunction with other information in the second table,
use a Query linking the two tables; or use a Form with a combo box (or
other tool) to display the data without storing it. One such tool
would be to use a Form for the Reference table, with a subform for the
second table, linking on the three digit ID.

John W. Vinson[MVP]

 

[EJH]

The solution is:

Don't.

There is NO need to store the Office or Area fields in the second
table - AT ALL. There are many good reasons NOT to do so.

Relational databases use the "Grandmother's Pantry Principle":

"A place - ONE place! - for everything, everything in its place".

Store the Area and Office in the reference table, only; if you need to
see them in conjunction with other information in the second table,
use a Query linking the two tables; or use a Form with a combo box (or
other tool) to display the data without storing it. One such tool
would be to use a Form for the Reference table, with a subform for the
second table, linking on the three digit ID.

John W. Vinson[MVP]

Thanks John,

I kind of came up with the duplication part while I was sleeping, but this
explains more how to do it.