How do I refer to a field in a lookup query and complete action based on value?

Question

I am working on a database for my store inventory in MS Access.

There is an "orders" form where my staff types in the item and the quantity the customer requested. Right now, there is nothing stopping my staff from placing orders for items we don't have. This creates negative values in the query where I calculate my total stock (and gives me messy data). I want to create a system that checks the stock levels after the quantity text box has been updated so my staff is alerted to the low volume.

My current plan is to pull up my calculated stock query after the order quantity has been updated and if the value is <0 then a message box would pop up. Each item has its own unique code, and the query is pulling up only the specific coded item via Forms![Orders]![ItemCode].

My code is under the AfterUpdate event for my Order Quantity textbox.

Private Sub OrderQuantity_AfterUpdate()
      DoCmd.OpenQuery "qryCheckCurrentStock", acViewNormal, acReadOnly
        Dim Total As Integer
        
        If Total < 0 Then
            Beep
            MsgBox "ITEM NOT IN STOCK!", vbExclamation, ""
        End If
End Sub

I think my problem is how I am referring to the field in the query (Total) or its syntax. If anyone has ideas or has another method of preventing negative stock when placing orders I would greatly appreciate it!

Answer 1

Ok, so your code is close, but it would in theory be somthing like this:

Private Sub OrderQuantity_AfterUpdate()
  dim strSQL  as string
  strSQL = "SELECT ID, TotalInInventory FROM qryCheckCurrentStock WHERE StockID = " & me!StockID
  dim rstResult      as dao.RecordSet
  set rstResult = currentdb.OpenRecordSet(strSql)
  dim Qty as long
  Qty = rstResult!TotalInInventory
  rstResult.Close
    
    If Qty < 0 Then
        Beep
        MsgBox "ITEM NOT IN STOCK!", vbExclamation, ""
    End If
End Sub

Now, the above is air code, but you see how a query based on a given StockID or StockNumber can be used here, and thus you have use of the current Qty in stock for the given stock item.