Background:
Our orders go through a set of statuses based on where they are in the process.
The statuses used in this query are the following:
0 = no stock
1 = back order
2 = in stock
3 = allocated
I have a function written that gets currently how many we have onhand and I have a function that tells me how many we have availabletotal which includes product still coming from our vendors on top of what we have physically in stock.
Issue:
We must have an allocated status due to we are a consignment business and orders are done months out. The allocation query I run takes a long time for an order of 200+ items. I am looking to see if anyone can recommend an optimization for my queries or should I even be going at this another way completely?
Code:'Set status to allocated if we have enough on hand strSQL = "UPDATE tblOrderDetail SET tblOrderDetail.StatusID = 3 " & _ "WHERE (((tblOrderDetail.OrderID)=" & txtOrderID & ") AND ((tblOrderDetail.StatusID)<=2) AND ((tblOrderDetail.Quantity)<=OnHand([ProductID])));" CurrentDb.Execute strSQL, dbFailOnError 'Set status to back order if we have product coming in from vendors strSQL = "UPDATE tblOrderDetail SET tblOrderDetail.StatusID = 1 " & _ "WHERE (((tblOrderDetail.OrderID)=" & txtOrderID & ") AND ((tblOrderDetail.StatusID)=0 Or (tblOrderDetail.StatusID)=2) AND ((tblOrderDetail.Quantity)>=AvailableTotal([ProductID])));" CurrentDb.Execute strSQL, dbFailOnError 'Set status to no stock if we do not have enough on hand strSQL = "UPDATE tblOrderDetail SET tblOrderDetail.StatusID = 0 " & _ "WHERE (((tblOrderDetail.OrderID)=" & txtOrderID & ") AND ((tblOrderDetail.StatusID) Between 1 And 2) AND ((tblOrderDetail.Quantity)>=OnHand([ProductID])));" CurrentDb.Execute strSQL, dbFailOnError