Code to change value in table (1 Viewer)

[parara]

Hello , I have a database name "sales". In my database , when i am doing any sale of product i want to update the stock value in table "product" by quantity which sale. Any ideas about updating these values? Table product has (IDProduct , ProductDescription , Price , Stock1 , Stock2 ).
Thanks

 

[MStef]

Try to do this with UPDATE QUERY.

 

[parara]

Hi , I know this method bat i dont want to see the message which displayed when the UPDATE QUERY execute

 

[MStef]

DoCmd.SetWarnings False
.....run update query
DoCmd.SetWarnings True

 

[parara]

No its not working. The 'sale' form has the following fields (CodeProduct , Description , Quantity , Price ). When i am sale a product , the Quantity must decrease the number in field Stock1 on table Product. In the same form 'sale' can be one or more records. Each recordset Quantity must decrease the correspond field Stock1 on table Product.

 

[MStef]

It must be that you do something wrong.
Send a short example of your MDB.

 

[parara]

This is my database , take a look. Thanks

 

[MStef]

Hello parara!

1) I don't know grecian letter,
2) I think your tables not normalized well.
3) I made an improvisation.
Look at "DemoMyleather" (attachment).
You have to know, there are 5 situation.
1) Add a new reocord.
2) Update an old record, (quantity).
3) Delete record.
4) Update an old record "IDProduct" field (I didn't make this).
5) Delete a Main record (I didn't make this).

Look at tables (especially "Products", StockRoom1).
Look at queryes, Forms, (VBA), Relationships.
Open "frmSales" and try, after that look at tbl "Products", StockRoom1.

I think it can help you, adapt it on your mdb.