Searching tables and displaying specific data in forms (1 Viewer)

[Automator]

Hello,

-What im working with:
MS Access 2003
Basic tracking MDB for tracking expired products

-Over view of my MDB
I have a tracking database with 2 tables and a form. The first table will be user entered information about expired products. The second table is a list of every item in our system keyed to a specific 5 digit number that is unique to every product.

On my form the first field a user will fill in is its 5digit product code. Once this is entered i would like the next few fields to automatically fill in by matching the entered product number to the 2nd table and then pulling the description/mfg#/price

example:
Product : 12345 <-user enters
Desc: "random product" <-matches product to table, finds corresponding desc and places in the text box.
MFG#: ALS124B <-matches product# to table, finds corresponding mfg and places it in text box
QTY: 3 <- user entered

Any help on how to do this would be appreciated I searched the web quite a bit and just cant figure this out.

 

[Pat Hartman]

Create a query that joins the two tables and selects the data you want from each table. Use that as the recordSource for your table. When you enter the productID or choose it from a combo, the related fields will populate automatically. It is best if you set the locked property to yes for any field selected from the "lookup" table since you don't want those values to be changed on this form.