I am creating a simple Stock DB.
Could you please show me how I can move this Function from Front End Access to the Back End SQL Server where I know it will run much quicker. I would also like to create a trigger that runs this function after an Insert or Delete event as well. This will enable my StockItems table to always be up-to-date with the current stock level.
' This will calculate the quantity remaining for a single Stock Item ID
On Error GoTo Err_CalcRecievedItemsRemaining
Dim db As ADODB.Connection
Dim strsql As String
Set db = CurrentProject.Connection
strsql = "UPDATE tblReceivedItem LEFT JOIN vewTotalDispatchedForEachItemReceived ON tblReceivedItem.ReceivedItemID = vewTotalDispatchedForEachItemReceived.ReceivedItemID SET tblReceivedItem.StockRemaining = IIf(IsNull(SumOfQuantity),Quantity,Quantity-SumOfQuantity) WHERE (((tblReceivedItem.StockItemID)=" & FncStockItemID & "))"
Set db = Nothing
MsgBox Err.Description & " " & Err.Number