none
Compare a query result with a control value after update or Save RRS feed

  • Question

  • I have a form that is "Cash Receipt". We use this form for registering received cash for each Insurance Policy. I want to add a validation to this form that compare the cash received amount with Insurance Policy Balance and if there is greater cash, inform the user. It is good when we add "new" forms (Cash Receipt) for any Insurance policy but the problem raises when the operator make a mistake and want to amend it. For example think we have a Policy balance for 12,000 USD and  the operator entered 10,000 USD for a cash received but after a while he find out that he made a mistake and want to amend it to 5,000 USD. Because the 10,000 USD is saved in tables and the "qryPolicyTransaction" that I use for validating the balance, the form after update show a message that the cash is more than the balance but it is wrong. After amending the 10,000 to 5,000 the balance must be 7,000 but it shows this message. I don't know. I think the "qryPolicyTransaction" should be refreshed after entering the new amount and before the comparision. But I don't know how?

    I put this code in form After Update event. The code is as follow:

    Private Sub Form_AfterUpdate()
       
         'For informing the operator that the received amount is more than insurance amount I add this code
            If Not IsNull(Me.cboInsurancePolicyID) Then
            Const Message = "The amount is greater than the insurance Policy balance"
            Dim curInsuranceBalance As Currency
            Dim strWhere As String
            
        'Find the criteria for comparing insurance policy balance and received amount
            strWhere = "True"
            strWhere = strWhere & " AND qryPolicyCreditTransaction.InsurancePolicyID= " & Me.cboInsurancePolicyID
            
                   
        'Find the Balance
            curInsuranceBalance = Nz(DLookup("[Balance]", "qryPolicyTransaction", strWhere), -1)
            If curInsuranceBalance <> -1 Then
            If Me.txtAmount > curInsuranceBalance Then
                MsgBox Message, vbExclamation + vbOKOnly + vbMsgBoxRtlReading + vbMsgBoxRight, "Greater Amount"
            End If
            Else
                MsgBox "Please enter the insurance policy number ", vbExclamation + vbMsgBoxRight + vbMsgBoxRtlReading, "Error"
            Cancel = True
            End If
                End If
    
    
    End Sub


    Karim Vaziri Regards,


    • Edited by kvaziri Monday, July 18, 2016 10:46 AM
    Monday, July 18, 2016 10:44 AM

Answers

  • If the validation is undertaken in the BeforeUpdate event procedure, this has a Cancel argument whose return value can be set to True in the event of the validation criteria not being satisfied, so the row will not be committed to the table.

    To cater for an existing amount being amended you can discount this value by adding the previous value, if any, of the current transaction to the balance, which you can obtain like this:

        Nz(DLookup("Amount", "Transactions", "TransactionID = " & [TransactionID]),0)

    where TransactionID is the primary key.

    If the value returned by the addition of this amount to the current balance is equal to or less than the new amount  being entered then the validation criteria are satisfied; if not then the user should be informed and return value of the BeforeUpdate procedure's Cancel argument set to True.

    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Monday, July 18, 2016 5:56 PM Typo corrected.
    • Marked as answer by kvaziri Monday, July 18, 2016 7:29 PM
    Monday, July 18, 2016 5:55 PM
  • After amending the 10,000 to 5,000 the balance must be 7,000 but it shows this message. I don't know. I think the "qryPolicyTransaction" should be refreshed after entering the new amount and before the comparision. But I don't know how?

    Hi Karim,

    In the AfterUpdate event of the control for CashReceived of an existing record, you can correct the value of InsuranceBalance with the value:

          Me!CashReceived - Me!CashReceived.OldValue

    If the InsuranceBalance = 0, then all is oke, if <> 0 there is still a problem: or there is paid too much, or there is paid to less, and further action has to be taken.

    If you extend this with a correction of InsuranceBalance when adding a new record, or deleting an existing record, you can have an always actual InsuranceBalance.

    Imb.

     

    • Marked as answer by kvaziri Monday, July 18, 2016 7:28 PM
    Monday, July 18, 2016 6:51 PM

All replies

  • If the validation is undertaken in the BeforeUpdate event procedure, this has a Cancel argument whose return value can be set to True in the event of the validation criteria not being satisfied, so the row will not be committed to the table.

    To cater for an existing amount being amended you can discount this value by adding the previous value, if any, of the current transaction to the balance, which you can obtain like this:

        Nz(DLookup("Amount", "Transactions", "TransactionID = " & [TransactionID]),0)

    where TransactionID is the primary key.

    If the value returned by the addition of this amount to the current balance is equal to or less than the new amount  being entered then the validation criteria are satisfied; if not then the user should be informed and return value of the BeforeUpdate procedure's Cancel argument set to True.

    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Monday, July 18, 2016 5:56 PM Typo corrected.
    • Marked as answer by kvaziri Monday, July 18, 2016 7:29 PM
    Monday, July 18, 2016 5:55 PM
  • After amending the 10,000 to 5,000 the balance must be 7,000 but it shows this message. I don't know. I think the "qryPolicyTransaction" should be refreshed after entering the new amount and before the comparision. But I don't know how?

    Hi Karim,

    In the AfterUpdate event of the control for CashReceived of an existing record, you can correct the value of InsuranceBalance with the value:

          Me!CashReceived - Me!CashReceived.OldValue

    If the InsuranceBalance = 0, then all is oke, if <> 0 there is still a problem: or there is paid too much, or there is paid to less, and further action has to be taken.

    If you extend this with a correction of InsuranceBalance when adding a new record, or deleting an existing record, you can have an always actual InsuranceBalance.

    Imb.

     

    • Marked as answer by kvaziri Monday, July 18, 2016 7:28 PM
    Monday, July 18, 2016 6:51 PM
  • It is a good trick and works.

    Thanks a lot


    Karim Vaziri Regards,

    Monday, July 18, 2016 7:28 PM
  • Thanks a lot.

    Yes, it works.


    Karim Vaziri Regards,

    Monday, July 18, 2016 7:31 PM