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.

[pietlinden]

If the data is already in one of your tables, why copy it? Is it going
to change? If not, why not use a combobox for the 3-digit PK, set
limit-to-list to true, and then add a couple more columns to your
combobox and hide them? something like:
Column Count:=3
Column widths = 1;0;0
Rowsource= ? (Can't tell from the description of the problem)
then you have two textboxes - calculated fields.
=cboPK.Column(1)
and
=cboPK.Column(2)
Note: columns is a zero-based collection...

Hope that helps (and that maybe someone can explain it better than I!)

[Thats Me]

You don't need store the existing data in the second table, just store
the key from the first table and use a query to display the exiting
data when required. Remove the office and area columns from table two.

On your form to populate table two add a combo box control with;
row source type set to table/query,
row source set to table one,
bound column set to the column number of the 3-Digit(primary key),
Control source set to the field in table two that holds the
3-Digit number.
(the built-in wizzard will walk you through this)

To display the data create a query with Table two and table one as the
data source
use the 3 digit column from table two
Office and Area from table one
set the join type to a left join on the 3 digit column.

This method will allow you to add new offices, assign existing offices
to a diferent area and many other things with out having to go into
table two and "up-date" mismatched records due to the storage of
redundant data.

On Fri, 19 Aug 2005 22:22:07 -0600, "EJH" <se*****@cableone.net>
wrote:

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.

Have a nice day. ld****@NOPANTS.juno.com

Remove NOPANTS. To reply by direct E-Mail;
Support: The Right to Privacy and Anti-SPAM projects

[EJH]

Thanks to all,

I kind of came up with the duplication part while I was sleeping, but this
explains more how to do it. I will give it a try.
"Thats Me" <yq****@whab.pbz> wrote in message
news:ou********************************@4ax.com...

You don't need store the existing data in the second table, just store
the key from the first table and use a query to display the exiting
data when required. Remove the office and area columns from table two.

On your form to populate table two add a combo box control with;
row source type set to table/query,
row source set to table one,
bound column set to the column number of the 3-Digit(primary key),
Control source set to the field in table two that holds the
3-Digit number.
(the built-in wizzard will walk you through this)

To display the data create a query with Table two and table one as the
data source
use the 3 digit column from table two
Office and Area from table one
set the join type to a left join on the 3 digit column.

This method will allow you to add new offices, assign existing offices
to a diferent area and many other things with out having to go into
table two and "up-date" mismatched records due to the storage of
redundant data.

On Fri, 19 Aug 2005 22:22:07 -0600, "EJH" <se*****@cableone.net>
wrote:

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.

Have a nice day. ld****@NOPANTS.juno.com

Remove NOPANTS. To reply by direct E-Mail;
Support: The Right to Privacy and Anti-SPAM projects