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: