Validate a field value based on a query result

Question

I have a form for inventory Transaction. I want to restrict the Inventory operator to deduct an inventory item when there is not enough balance (Prevent Minus Inventory). But there is a problem. For validating the balance a query should be run (qryInventoryBalanceControl), that show the inventory balances . I don't know how I can validate the field value based on a record on this query!

---

Regards,

Answer 1

Part of the answer is depending on the exact structure of your data, as well as how you are making the User Interface experience - so this answer is somewhat generic:  you can trigger a query that results in the inventory count - let's call that query: InventoryCheck

Then depending on your data structure use either a DLookUp or possibly a DCount applied to InventoryCheck as part of event code triggering during the user data entry actions.

This value (DlookUp or DCount) - you have the logic as to whether the end user is allowed to proceed or potentially get a message to stop or whatever experience you want to invoke.

Answer 2

Hi Kvaziri,

A few questions:

1) Are you loking to validate the query Client side or server side?

If your pulling all of the inventory item data to the form(I assume your using a web form) you most certainly should have a unique identifier for each item. You can validate the balance of that item by checking the balace against some logic using the unique identifier of the item you want to check the balance for.

Client side, if your pulling the item data to the form, you can check the current balance against some logic, I'm not sure what programming language you're using, or how your GUI is set up.

Server side, you can do all of your balance logic before the form loads, and only show inventory that can be manipulated.

You really aren't providing enough details so I'm not sure what youre building.

---

mark a fisher

Answer 3

You'll find an example in Inventory.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

In this little demo file the order details subform in the orders form includes the following as its BeforeUpdate event procedure:

Private Sub Form_BeforeUpdate(Cancel As Integer)

    Const MESSAGE_TEXT = "Insufficient stock in hand."
    Dim lngStockInHand As Integer
    Dim strCriteria As String

    strCriteria = "ProductID = " & Me.ProductID
    lngStockInHand = DLookup("StockInHand", "qryStockInHand", strCriteria)
    
    If Me.Quantity > lngStockInHand Then
        MsgBox MESSAGE_TEXT, vbExclamation, "Invalid Operation"
        Cancel = True
    End If
    
End Sub

---

Ken Sheridan, Stafford, England

Answer 4

Dear Ken,

I used your sample and solve the problem.

Thanks a lot

---

Regards,