question about altering data after a report is run

[horsecow]

hello,

I'm in need of some rather urgent help. I have 6 days left in my deadline and need to have my database finished (i thought it was) and more importantly, all the paperwork to accompany it done.



What i want to do is have a field in a producttable called "stock" and in that field will be a number. Probably between 1 and 10 but not necessarily (it wont be in the 1000's anyway).

The product table has, naturally, the list of products for the shop each with their own product key as the primary key.


Wghat i want to do? When an invoice is created (ther report run) i want for each product the Stock value to decrease by one.


I assume i will need to put something in the detail section of the report design to do this... I figure i can have the product ID number their with the visible status set to off and somehow make access do: take the product ID of this product, go to the product table, go to the record which has the product number as the primary key, alter the Stock valueof this record by subtracting 1 from it.


but ive no idea how to do this


If anyone can help id really appreciate it. Im running seriously short of time.

Thank you.

(sorry for the bad spelling/grammer; i'm in a rush)

 

[bjackson]

use the on print event for the report section
dim MyQty as long
Dim IdNo as long
MyQty=me.[the name of the qty control]
IdNo=[the name of the id control]

docmd.runsql("UPDATE Stock SET Stock.Qty = [qty]-MyQty
WHERE (((Stock.Id)=" & IdNo));

however every time the report is run the stock will be reduced
you really need to allow for all possibilitys,Like
What happens if someone prints the report twice
what happens if a week down the track you need to print a copy,