Update Stocks Public Subroutine not work (2 Viewers)

[wendywu]

I have the public subroutine as follows to add or edit the "Stocks" table.
The public subroutine "UpdateStocks" is called by the click event of the confirmed button on the purchase order.
The purchase order has a purchase details sub-form(called "進貨單維護子表單"), which contains the "ProductID" field and "Quantity" field. The purchase order also has a "WarehouseID" field. These three fields are the parameters to the public subroutine to add or edit the alike three fields ("ProductID"、"WarehouseID" and "Quantity") of the "Stocks" table.
But the result of the execution is that the "Stocks" table is not updated.

The click event of the confirm order button of the purchase order is as follows:

Private Sub cmdConfirm_Click()

If Me.NewRecord Or Me.Dirty Or _
   Me.進貨單維護子表單.Form.NewRecord Or Me.進貨單維護子表單.Form.NewRecord Then
 
   Exit Sub
 
End If

If Me.Confirm = True Or IsNull(Me.Confirm) Then

   Exit Sub
 
End If

If MsgBox("確認本筆進貨單?", vbYesNo, "進貨單確認") = vbNo Then
   Exit Sub
End If

Dim strSQL As String
Dim rst As ADODB.Recordset
Dim Net As Integer
 
Me.Confirm = True

DoCmd.RunMacro "mrcSaveRecord"
 
If Me.Property = "1" Then
 
   Net = 1
 
Else
 
   Net = -1
 
End If

strSQL = "SELECT * " & _
         "FROM PurchaseDetails " & _
         "WHERE PurchaseID = '" & Form_進貨單維護![PurchaseID] & "' "

Set rst = New ADODB.Recordset

rst.Open strSQL, CurrentProject.Connection

Do While Not rst.EOF
 
    Call UpdateStocks(rst![ProductID], Me![WarehouseID], rst![Quantity] * Net)
 
   rst.MoveNext

Loop

End Sub

Public Sub UpdateStocks(ProductID As String, WarehouseID As String, Quantity As Long)

If ProductID = "" Or WarehouseID = "" Or Quntity = 0 Then
 
   Exit Sub
 
End If

Dim strSQL As String
Dim rst As ADODB.Recordset
Dim cmd As ADODB.Command

Set rst = New ADODB.Recordset
Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection

strSQL = "SELECT ProductID " & _
         "FROM Stocks " & _
         "WHERE ProductID = '" & ProductID & "' " & _
         "AND WarehouseID = '" & WarehouseID & "'"
 
rst.Open strSQL, CurrentProject.Connection

If rst.BOF Then
 
   strSQL = "INSERT INTO Stocks " & _
            "(ProductID, WarehouseID, Quantity) " & _
            "VALUES (" & _
            "'" & ProductID & "', " & _
            "'" & WarehouseID & "', " & _
            Quantity & ")"
   
   cmd.CommandText = strSQL
   cmd.Execute

Else
   strSQL = "UPDATE Stocks " & _
            "SET Quantity = Quantity + " & Quantity & " " & _
            "WHERE ProductID = '" & ProductID & "' " & _
            "AND WarehouseID = '" & WarehouseID & "'"
   
   cmd.CommandText = strSQL
   cmd.Execute
 
End If

rst.Close
Set rst = Nothing
Set cmd = Nothing
 
End Sub

 

[June7]

Please post code between CODE tags to retain formatting and readability.

Probably shouldn't even maintain a "Stocks" table. Review http://allenbrowne.com/AppInventory.html

Why are you using Str() function? Are those ID fields text type? If they are not, don't use apostrophes. The posted code is missing a closing quote on the last line of UPDATE SQL. Why open recordset?

 

[Gasman]

Have you thought about walking the code, line by line and see what it actually does, not what you *think* it does?

In what way does it not work?

I would debug.print your sql and see what you really have.

Here is a tip, put the space at the start of the next line. Makes it easier to spot missing ones.

 

[arnelgp]

agree with post #2.
but if you cannot do it, maybe change the code on the Else part:

...
..
Else
   strSQL = "UPDATE Stocks " & _
            "SET Quantity = Nz(Quantity,0) + " & Nz(Quantity,0) & " " & _
            "WHERE ProductID = '" & ProductID & "' " & _
            "AND WarehouseID = '" & WarehouseID & "'"
           
   cmd.CommandText = strSQL
   cmd.Execute
 
End If

 

[Ken Sheridan]

Storing stock in hand values in a table runs the risk of update anomalies, as the values can easily be amended so that they do not reflect the movements in and out of stock. Commonly the stock in hand for a product is computed by subtracting the total quantity of stock disposals from the total quantity of acquisitions per product, with adjustments resulting from periodic stock takes applied.

The following query is an example which returns all movements into or out of stock as positive or negative numbers:

SELECT
    "Acquisition" AS MovementType,
    ProductID,
    Quantity
FROM
    StockAcquisitions
WHERE
    AcquisitionDate >= NZ (
        (
            SELECT
                MAX(StockTakeDate)
            FROM
                StockTakes
            WHERE
                StockTakes.ProductID = StockAcquisitions.ProductID
        )
    )
UNION ALL
SELECT
    "Disposal",
    ProductID,
    Quantity * -1
FROM
    StockDisposals
WHERE
    DisposalDate >= NZ (
        (
            SELECT
                MAX(StockTakeDate)
            FROM
                StockTakes
            WHERE
                StockTakes.ProductID = StockDisposals.ProductID
        )
    )
UNION ALL
SELECT
    "Stocktake",
    ProductID,
    Quantity
FROM
    StockTakes AS ST1
WHERE
    StockTakeDate = (
        SELECT
            MAX(StockTakeDate)
        FROM
            StockTakes AS ST2
        WHERE
            ST2.ProductID = ST1.ProductID
    );

UNION ALL
SELECT
    "Sale",
    ProductID,
    Quantity * -1
FROM
    Orders
    INNER JOIN OrderDetails ON Orders.OrderNumber = OrderDetails.OrderNumber
WHERE
    OrderDate >= NZ (
        (
            SELECT
                MAX(StockTakeDate)
            FROM
                StockTakes
            WHERE
                StockTakes.ProductID = OrderDetails.ProductID
        )
    );

The stock in hand values per product can then be returned by aggregating the values returned by the above query in another query:

SELECT
    ProductID,
    SUM(Quantity) AS StockInHand
FROM
    qryStockMovements
GROUP BY
    ProductID;

The above queries are taken from my Inventory demo file which you can find in Inventory.zip in my Dropbox public databases folder at:

https://www.dropbox.com/scl/fo/0sci...cGdr3QW0?rlkey=ib6bs6g9jqcrywwzivur3265t&dl=0

I should stress that the file illustrates very basic inventory management. Having worked at one time in the Purchase and Supply department of a large manufacturing company I can testify to the complexity of a fully developed inventory management system.

 

[Pat Hartman]

But it doesn't work

You got a lot of responses which surprises me. "it doesn't work" is totally useless as a problem description.

 

[CarlettoFed]

The routine "Public Sub UpdateStocks" does not exist within the file you linked.

 

[Ken Sheridan]

The routine "Public Sub UpdateStocks" does not exist within the file you linked.

Unless I'm going blind, I don't see any link in the OP's post.