updating stock levels 1

[mudstuffin]

Hello.

I am trying to figure out how the stock level in the products table can be updated when a quantity is entered for a product in the order form. I am playing around with the Northwind database trying to figure it out. Can someone give some code to do this using the details below...?

The details are...

Table containing product details:- Products
contains fields:- ProductId,ProductName,UnitsInStock

The order form:- OrderForm
textboxes:- ProductID,ProductName,QtyOrdered.

Or am I going about this the wrong way...?

Thanks,


mudstuffin.

 

[Veep]

You're on the right track.
DAO version would go something like this:

dim db as DAO.Database
dim strSql as String
dim qdf as QueryDef
dim QtyOrdered as long
dim ProductID as long

ProductID = me.ProductID 'the value on the order form
QtyOrdered = me.QtyOrdered 'the quantity on the order form
strSql="UPDATE Products SET UnitsInStock = (UnitsInStock - " & QtyOrdered & &quot WHERE ProductID=" _
& ProductID

set db=currentdb()
set qdf = db.CreateQueryDef("",strSql)
qdf.execute
set qdf=nothing
set db=nothing

 

[mudstuffin]

Thanks for that Veep.

Just what I was looking for and does the job spot-on.

Regards,


mudstuffin.