Automatically populate fields for related records

[Chand]

Hello Everyboy

I am creating a small scale db with following fields in the table and
form for the data entry purpose.

Product ID Product Name Manufacturer
Location

101 Pen ABC & Co.
Boston
102 Rulers XYZ & Co.
Chicago

For the next new record, when the user enters the product id as 101,
the form should automatically populate Pen, ABC & Co., Boston as
respective values for the rest of the field controls in the form. And
as the field controls in the form are populated, the corresponding
fields in the table should also be populated.
I would really appreciate if somebody can explain me to solve this. I
am using Windows 2000 professional with Access 2003.

Thanks in advance

Chand

[Pieter Linden]

ch**********@yahoo.com (Chand) wrote in message news:<cd**************************@posting.google. com>...

Hello Everyboy

I am creating a small scale db with following fields in the table and
form for the data entry purpose.

Product ID Product Name Manufacturer
Location

101 Pen ABC & Co.
Boston
102 Rulers XYZ & Co.
Chicago

For the next new record, when the user enters the product id as 101,
the form should automatically populate Pen, ABC & Co., Boston as
respective values for the rest of the field controls in the form. And
as the field controls in the form are populated, the corresponding
fields in the table should also be populated.
I would really appreciate if somebody can explain me to solve this. I
am using Windows 2000 professional with Access 2003.

Thanks in advance

Chand

make the product ID field a combobox with 4 columns {Product ID,
Product Name, Manufacturer, Location} Show the first/second (or
whichever you want). then set the values for the other textboxes
=Me!cboProductID.Column(1)...
No need to populate the table with all that stuff if it's already in
the products table. You can query for it as you need it.

[Pieter Linden]

ch**********@yahoo.com (Chand) wrote in message news:<cd**************************@posting.google. com>...

Hello Everyboy

I am creating a small scale db with following fields in the table and
form for the data entry purpose.

Product ID Product Name Manufacturer
Location

101 Pen ABC & Co.
Boston
102 Rulers XYZ & Co.
Chicago

For the next new record, when the user enters the product id as 101,
the form should automatically populate Pen, ABC & Co., Boston as
respective values for the rest of the field controls in the form. And
as the field controls in the form are populated, the corresponding
fields in the table should also be populated.
I would really appreciate if somebody can explain me to solve this. I
am using Windows 2000 professional with Access 2003.

Chand,
this is a trick question. The answer is that you only write the
related foreign key into your table. then you use a query to retrieve
the rest of the data when you need it. You can either use DLookup or
use a combobox with lots of hidden columns and then reference the
hidden columns in your UNbound (yes, you read that right - unbound)
controls. the rowsource is something like (off the top of my head)
=Me!MyCombo.Columns(1) and remember that columns are zero-based, not
one-based, so subtract from the ordinal position...
HTH,
Pieter