Northwind

[NigelW_SWUK]

In the Northwind database how do I get the orders detail (Quantity) to update
the products (units in stock) to reflect sales? Also, is there an easy way
to add new stock? Many thanks in advance.

 

[Ken Sheridan]

Put the following code in the order details subform's AfterInsert event
procedure

Dim cmd As ADODB.Command
Dim strSQL As String

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

strSQL = "UPDATE Products " & _
"SET UnitsInStock = UnitsInStock - " & _
Me.Quantity & " WHERE ProductID = " & _
Me.ProductID

cmd.CommandText = strSQL
cmd.Execute

First make sure that you have a reference to the Microsoft ActiveX Data
Objects Library (Tools | Refrences on the VBA menu bar).

As for entering new stock the sample database includes a Products form which
can be used for this. As it stands the UnitsInStock control would need to be
updated manually but you could if you wished add an unbound
txtNewStockQuantity text box and in its AfterUpdate event procedure put:

Me.UnitsInStock = Me.UnitsInStock + Me.txtNewStockQuantity

In the form's Current event procedure put the following to initialize the
unbound text box to zero:

Me.txtNewStockQuantity = 0

Ken Sheridan
Stafford, England

 

[NigelW_SWUK]

Put the following code in the order details subform's AfterInsert event
procedure

Dim cmd As ADODB.Command
Dim strSQL As String

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

strSQL = "UPDATE Products " & _
"SET UnitsInStock = UnitsInStock - " & _
Me.Quantity & " WHERE ProductID = " & _
Me.ProductID

cmd.CommandText = strSQL
cmd.Execute

First make sure that you have a reference to the Microsoft ActiveX Data
Objects Library (Tools | Refrences on the VBA menu bar).

As for entering new stock the sample database includes a Products form which
can be used for this. As it stands the UnitsInStock control would need to be
updated manually but you could if you wished add an unbound
txtNewStockQuantity text box and in its AfterUpdate event procedure put:

Me.UnitsInStock = Me.UnitsInStock + Me.txtNewStockQuantity

In the form's Current event procedure put the following to initialize the
unbound text box to zero:

Me.txtNewStockQuantity = 0

Ken Sheridan
Stafford, England

Thanks Ken - I will try this (although it may be a bit above my level of
competence!