The problem has been resolved and the program can now be executed. Thank you very much. (1 Viewer)

[wendywu]

＊＊The problem has been resolved and the program can now be executed.
I changed the names of the three parameters in the ＂UpdateStocks" public sub in th public function module. (they originally had the same names as the three columns in the "Stocks" table).＊＊ Thank you very much!

I have the purchase order and the purchase order subform.
There is a confirmation button on the purchase order, the "Confirm" button click event will call the "UpdateStocks" public function in the "Public Function" module.

Once the purchase order is confirmed, the "WarehouseID" of the purchase order, and the "ProductID" and "Quantity" of the purchase order subform, add or update the "ProductID" and "WarehouseID" and "Quantity" of the "Stocks Table".

The confirm button click event is as follows:

Private Sub cmdConfirm_Click()

If Me.NewRecord Or Me.Dirty Or _
   Me.PurchaseOrderSubform.Form.NewRecord Or Me.PurchaseOrderSubform.Form.Dirty Then
 
   Exit Sub
 
End If

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

   Exit Sub
 
End If

If MsgBox("Are you sure to confirm this order?", vbYesNo, "Order Confirm") = 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 = '" & Me.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

The public sub "UpdateStocks" in the public function module is as follows:

Public Sub UpdateStocks(ProdID As String, WHID As String, Qty As Long)

If ProdID = "" Or WHID = "" Or Qty = 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 = '" & ProdID & "' " & _
         "AND WarehouseID = '" & WHID & "'"

rst.Open strSQL, CurrentProject.Connection

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

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

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

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.