Hi! This is my first post. I want to build a sales project via access 2003. I've created 2 tables named

1.PRODUCTS (fields- PRD_CODE, PRD_NAME, PRICE)
2. SALES (fields- MEMO_NO, DATE, PRD_CODE,PRD_NAME,PRICE)

I made relationship so that the PRD_CODE from SALES table must match with PRD_CODE with PRODUCTS table.

Now, I want that- when I will input the PRD_CODE in SALES table, the PRD_NAME and PRICE fields will be automatically filled up with values from PRODUCTS table's PRD_NAME and PRICE value.

I've created form for SALES table. I don't know what code to write at AfterUpdate() in PRD_CODE field.

Please, Help me.

you don't need to duplicate the data in the sales table, well If I'm being honest you shouldn't duplicate the data in the sales table.

product data stays in the product table, sales data in the sales table. the only reason you may wish to stroe the product data is if it changes between sales.

so you may want to if the price changes per sale (eg you give a discount or have some other pricing mechanism)

the only reason why I can think you may want to store the product description is if say you sold a generic item and the actual product varied. this may happen say with biros, you have a product which may come from manufacturer A one week, B next week, Z the week after.

bear in mind that you can 'glue' together data from different tables providing there is a mechanism to identify the common data.. in this case its your PRD_CODE

if you have defined the realtion ship, when you come to define the query add the two tables you need and the relationship will already be defined. build your query, adding whatever columns you need, and use that query to feed a report.

Thanks for ur reply. The actal reason I want to develop the database is for a phone shop-

In PRODUCT table, I will entry data PRD_CODE, PRD_NAME & PRICE previously.

Then, at the selling time, I will open the SALES table to enter the selling data. I want that- whenever I will enter the PRD_CODE in SALES table, it will match the PRD_CODE field from PRODUCT table and the entire row containing PRD_NAME & PRICE will come out and show in the SALES form.

That's why I need the code to write AfterUpdate() on PRD_CODE.

Thanks.

no you don't need to put that data in the sales form....

having selected the product code (presumably from a combo or list box) you then populate the controls on the form as required.)

theres several wasy of doing this
1) to issue a series of DlookUp function calls to retrieve the data
2) open a recordset and retrieve all the details in one hit
3) stuff the product code, price and description into the list/combo box, and retrieve the details form the combo/listbox selected item collection

of the 3 option 1 is probably the easiest and least efficient, option 2 is probably the 'best' solution. although neither 2 or 3 are difficult to do, they can be a bit of a struggle for people unused to dabbling on VBA

what yopu are p[roposing to do right now goes against good practise, if you have doubts have a look at the rules on normalisation.