none
Validate a field value based on a query result RRS feed

  • 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,

    Friday, February 19, 2016 1:57 PM

Answers

  • 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


    • Edited by Ken Sheridan Friday, February 19, 2016 6:56 PM Typo corrected
    • Marked as answer by kvaziri Sunday, February 21, 2016 6:25 AM
    Friday, February 19, 2016 6:54 PM

All replies

  • 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.

    Friday, February 19, 2016 2:30 PM
  • 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

    Friday, February 19, 2016 2:30 PM
  • 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


    • Edited by Ken Sheridan Friday, February 19, 2016 6:56 PM Typo corrected
    • Marked as answer by kvaziri Sunday, February 21, 2016 6:25 AM
    Friday, February 19, 2016 6:54 PM
  • Dear Ken,

    I used your sample and solve the problem.

    Thanks a lot


    Regards,

    Sunday, February 21, 2016 6:26 AM