Autofilling a form field? Access 2002?

[Avestruz]

Hello -

I'm trying to create a relatively simple (I think) input form.
Resulting from the input will be a "Products" table that includes all
sort of details related to each.

The first field in my form / table is "ProdName". In the form its
presented a drop down list generated by a simple query that grabs
unique product names from another table ("Lines").

The second field is "ProdNum". Again, In the form its presented a
drop down list generated by a setting the fields lookup value to the
"Item#" column in the "Lines" table.

The third form field is "ProdDesc". I'd like this field to autofill
based on the product number selected in the 2nd field, ProdNum. The
Help file @ Access 2002 is not much help and I'm something of a
freshman at this?.

I've copied the Lines table and called it "Desc". The ProdNum field
exists in both and is a primary key in the Lines table. I then did a
quick query that includes both tables (Access sees the common fields
and creates a join relationship). Showinf the results of thew query I
do in fact get product descriptions.

So how do I get the Access to autofill / update the form field (and
thereby table record) with the results of this query when its based
on the Product Number ("ProdNum") selected in in field number 2? Do I
point the query as a lookup value in the table? Or, do I adjust the
data source in the form?

I'm stumped. Your help is much appreciated.

A.

 

[Larry Linson]

It almost sounds as if you are setting the Product Name and the Product
Identification _separately_ when they are directly related. Consider having
both in the Combo Box and using its Columns feature from code in After
Update to set the other. On the other hand, consider not storing anything
but that foreign key in the record and joining with the Lines table in the
Query when you use it. That will be good relational design, but storing
redundant information will not be.

Larry Linson
Microsoft Access MVP