Update Stocks Public Subroutine not work (2 Viewers)

wendywu

New member
Local time
Today, 15:25
Joined
May 20, 2024
Messages
26
I have the public subroutine as follows to add or edit the "Stocks" table. But it doesn't work? Thanks in advance!


Code:
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
 
Last edited:
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?
 

Users who are viewing this thread

Back
Top Bottom