how to change number after report 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)
(also, sorry for posting this in the report section too, im not sure where it should go)

 

[llkhoutx]

Use the record source for your report to adjust your stock, either with an update query or DAO/ADO code.

 

[WayneRyan]

horsecow,

I know that you have a time problem, but your approach is not
the best solution. You should be storing the products that
are shipped/received in seperate tables, then you can easily
calculate the stock on-hand at any time.

However, given your situation you can:

On the screen where they are entering the data for the invoice,
decrement the total then.

Run a query like:

Update YourTable
Set       OnHand = OnHand - 1
Where  ProductID In (Select ProductID
                                   From  InvoiceTable
                                   Where InvoiceID = " & Me.InvoiceID)

Use the DetailPrint event on the report:

Dim dbs As DataBase
Dim sql As String
Set dbs = CurrentDb
sql = "Update YourTable " & _
         "Set       OnHand = OnHand - 1 " & _
         "Where  ProductID = Me.ProductID"
dbs.Execute(sql)

Again, if you designed your tables properly, you would not have
to "resort" to any of the above and life would be easier.

hth,
Wayne

 

[horsecow]

hello,

the tables in my database are designed suitably for what i needed them for. It's only today ive been told i need to add this record of the amount of stock needed to get the grade i want in the subject. I have 6 days (that includes today, 5 working days really) to finish the database and all the apaper work that goes with it.

I'm a little confused by your answer wayne. I appreciate the help so far, but could i trouble you for some more? This is my first database ive ever created and as said, im in a stress with the lack of time to find a solution (solving this will bump me up possibly 2 grades).

Update YourTable
Set OnHand = OnHand - 1
Where ProductID In (Select ProductID
From InvoiceTable
Where InvoiceID = " & Me.InvoiceID)

You said to run a query witht hat code? i'm a little confused as to what you mean. I assume you dont mean a query query, but to put that code in the form where they are entering data for the invoice? So if i go to the VB code place in access i can copy that code (changing PRoductID to the real name of that field etc) and paste it in and it should execute by itself? No button should need to be clicked.

Also related to this section of your post, what does the onHand part mean? Do i need to change the name to the name of the stock field in my database? little confused about that.

Finally, is thr YourTable just a name for this query, or is it the name of the table which holds the product info (tblproduct for example)?

Dim dbs As DataBase
Dim sql As String
Set dbs = CurrentDb
sql = "Update YourTable " & _
"Set OnHand = OnHand - 1 " & _
"Where ProductID = Me.ProductID"
dbs.Execute(sql)

i think i udnerstand this. i just bang this in the report code section but obviously change the field names like ProdictID to what they really are?


I appreciate your help. I just hope i can get this to work. Im sorry for asking so many questions and needing so much help.

Hopei get a reply.

Cheers

 

[WayneRyan]

horsecow,

I assume that your invoice report has a query as its
recordsource. Given that if you only need to handle
the quantity stored in your table use the DetailPrint
event on your report.

With your report in design view, select the properties
for the Detail Section, then choose the event tab.
Select the Detail Print event, choose [event function],
then click on the three dots at the right margin.

Put in this code:

Dim dbs As DataBase 
Dim sql As String 
Set dbs = CurrentDb 
sql = "Update YourTable " & _ 
      "Set OnHand = OnHand - 1 " & _ 
      "Where ProductID = " & Me.ProductID & ";"
dbs.Execute(sql)

YourTable is the name of your Product Table and
ProductID is the control on your report that has
the ProductID. In this case it is a NUMBER. If
it was a string you would:


"Where ProductID = '" & Me.ProductID & "';"

hth,
Wayne