none
VBA code isn't working RRS feed

  • Question

  • I have a ExempNameID combobox on the OrdersDetail subform and I am using below code in the AfterUpdate event behind this combobox to update the ExempQty in the ExempName table.

    For example, if user delete the value in the ExempNameID combobox then the OrderQty should add in the ExempQty. Similarly, if user select a value in the ExempNameID combobox then the OrderQty should subtract from the ExempQty.

    Private Sub ExempNameID_AfterUpdate()
    Dim strOrderQty As Long
    Dim strSQL As String

    strOrderQty = Me.OrderQty

    If strOrderQty > Val(Nz(Me.ExempNameID.Column(2), 0)) Then
        If Me.ExempNameID.Text = "" Then
            strSQL = "UPDATE ExempName SET ExempQty = ExempQty+" & strOrderQty & "WHERE ExempNameID =" & Me.ExempNameID
            CurrentDb.Execute strSQL, dbFailOnError
        Else
            strSQL = "UPDATE ExempName SET ExempQty = ExempQty-" & strOrderQty & "WHERE ExempNameID =" & Me.ExempNameID
            CurrentDb.Execute strSQL, dbFailOnError
        End If
        MsgBox "Sorry, the order qty is too large.", vbExclamation, "Warning"
        Me.ExempNameID = ""
    End If
    End Sub

    Friday, June 30, 2017 7:42 PM

Answers

  • For one thing, I think you need a space after the strOrderQty

    So

    & "WHERE ExempNameID

    should be

    & " WHERE ExempNameID

     

    Also, if you are using a naming convention, which it appears you are, then you'd probably want to change strOrderQty to lngOrderQty since it is long and not a string otherwise things will quickly become confusing and difficult to troubleshoot.

    Lastly, don't forget Error Handling.  So it would turn into something like:

    Private Sub ExempNameID_AfterUpdate()
        On Error GoTo Error_Handler
        Dim strOrderQty           As Long
        Dim strSQL                As String
    
        strOrderQty = Me.OrderQty
    
        If strOrderQty > Val(Nz(Me.ExempNameID.Column(2), 0)) Then
            If Me.ExempNameID.Text = "" Then
                strSQL = "UPDATE ExempName" & vbCrLf & _
                         " SET ExempQty=ExempQty+" & strOrderQty & vbCrLf & _
                         " WHERE ExempNameID=" & Me.ExempNameID
                CurrentDb.Execute strSQL, dbFailOnError
            Else
                strSQL = "UPDATE ExempName" & vbCrLf & _
                         " SET ExempQty=ExempQty-" & strOrderQty & vbCrLf & _
                         " WHERE ExempNameID=" & Me.ExempNameID
                CurrentDb.Execute strSQL, dbFailOnError
            End If
            MsgBox "Sorry, the order qty is too large.", vbExclamation, "Warning"
            Me.ExempNameID = Null
        End If
    
    Error_Handler_Exit:
        On Error Resume Next
        Exit Sub
    
    Error_Handler:
        MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
               "Error Number: " & Err.Number & vbCrLf & _
               "Error Source: ExempNameID_AfterUpdate" & vbCrLf & _
               "Error Description: " & Err.Description & _
               Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
               , vbOKOnly + vbCritical, "An Error has Occured!"
        Resume Error_Handler_Exit
    End Sub

    Also, if you set a variable to currentdb, you could then validate that the update operation succeeded, or not, by doing something like:

    Private Sub ExempNameID_AfterUpdate()
        On Error GoTo Error_Handler
        Dim db                    As DAO.Database
        Dim strOrderQty           As Long
        Dim strSQL                As String
    
        strOrderQty = Me.OrderQty
    
        If strOrderQty > Val(Nz(Me.ExempNameID.Column(2), 0)) Then
            Set db = CurrentDb
            If Me.ExempNameID.Text = "" Then
                strSQL = "UPDATE ExempName" & vbCrLf & _
                         " SET ExempQty=ExempQty+" & strOrderQty & vbCrLf & _
                         " WHERE ExempNameID=" & Me.ExempNameID
                db.Execute strSQL, dbFailOnError
                'Check to see if the update ran properly
                If db.RecordsAffected = 0 Then
                    'No records were updated?!
                Else
                    'Everything appears to have gone smoothly
                End If
            Else
                strSQL = "UPDATE ExempName" & vbCrLf & _
                         " SET ExempQty=ExempQty-" & strOrderQty & vbCrLf & _
                         " WHERE ExempNameID=" & Me.ExempNameID
                db.Execute strSQL, dbFailOnError
                'Check to see if the update ran properly
                If db.RecordsAffected = 0 Then
                    'No records were updated?!
                Else
                    'Everything appears to have gone smoothly
                End If
            End If
            MsgBox "Sorry, the order qty is too large.", vbExclamation, "Warning"
            Me.ExempNameID = Null
        End If
    
    Error_Handler_Exit:
        On Error Resume Next
        If Not db Is Nothing Then Set db = Nothing
        Exit Sub
    
    Error_Handler:
        MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
               "Error Number: " & Err.Number & vbCrLf & _
               "Error Source: ExempNameID_AfterUpdate" & vbCrLf & _
               "Error Description: " & Err.Description & _
               Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
               , vbOKOnly + vbCritical, "An Error has Occured!"
        Resume Error_Handler_Exit
    End Sub


    Daniel Pineault, 2010-2016 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net



    Friday, June 30, 2017 8:22 PM
  • Hi Khurram Ahmed Khan,

    I try to check the code with your latest version of database.

    I find that the if condition you wrote to check Null value is not working.

    to check the Null value you can use IsNull function.

    Reference:

    IsNull Function

    I try to update your code and it is working.

     If IsNull(Me.ExempNameID) Then
        strSQL = "UPDATE ExempName" & vbCrLf & _
        " SET ExempQty=ExempQty+" & lngOrderQty & vbCrLf & _
        " WHERE ExempNameID=" & lngID
        Debug.Print strSQL
        CurrentDb.Execute strSQL, dbFailOnError
    End If
     

    the value of strSQL in immediate window.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, July 5, 2017 2:36 AM
    Moderator

All replies

  • Hi,

    Is this a continuation of this previous discussion? What is wrong with the above code? Are you getting an error message?

    Friday, June 30, 2017 7:48 PM
  • Shouldn't there be an

    Else

    above the line

        MsgBox "Sorry, the order qty is too large.", vbExclamation, "Warning"


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, June 30, 2017 7:54 PM
  • Yes, you are right. But, now I want to update the ExempQty in the ExempName table. No, I am not getting any error message.

    Instead of updating the ExempQty the above code is deleting ExempName value in the ExempName table.

    Friday, June 30, 2017 7:59 PM
  • For one thing, I think you need a space after the strOrderQty

    So

    & "WHERE ExempNameID

    should be

    & " WHERE ExempNameID

     

    Also, if you are using a naming convention, which it appears you are, then you'd probably want to change strOrderQty to lngOrderQty since it is long and not a string otherwise things will quickly become confusing and difficult to troubleshoot.

    Lastly, don't forget Error Handling.  So it would turn into something like:

    Private Sub ExempNameID_AfterUpdate()
        On Error GoTo Error_Handler
        Dim strOrderQty           As Long
        Dim strSQL                As String
    
        strOrderQty = Me.OrderQty
    
        If strOrderQty > Val(Nz(Me.ExempNameID.Column(2), 0)) Then
            If Me.ExempNameID.Text = "" Then
                strSQL = "UPDATE ExempName" & vbCrLf & _
                         " SET ExempQty=ExempQty+" & strOrderQty & vbCrLf & _
                         " WHERE ExempNameID=" & Me.ExempNameID
                CurrentDb.Execute strSQL, dbFailOnError
            Else
                strSQL = "UPDATE ExempName" & vbCrLf & _
                         " SET ExempQty=ExempQty-" & strOrderQty & vbCrLf & _
                         " WHERE ExempNameID=" & Me.ExempNameID
                CurrentDb.Execute strSQL, dbFailOnError
            End If
            MsgBox "Sorry, the order qty is too large.", vbExclamation, "Warning"
            Me.ExempNameID = Null
        End If
    
    Error_Handler_Exit:
        On Error Resume Next
        Exit Sub
    
    Error_Handler:
        MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
               "Error Number: " & Err.Number & vbCrLf & _
               "Error Source: ExempNameID_AfterUpdate" & vbCrLf & _
               "Error Description: " & Err.Description & _
               Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
               , vbOKOnly + vbCritical, "An Error has Occured!"
        Resume Error_Handler_Exit
    End Sub

    Also, if you set a variable to currentdb, you could then validate that the update operation succeeded, or not, by doing something like:

    Private Sub ExempNameID_AfterUpdate()
        On Error GoTo Error_Handler
        Dim db                    As DAO.Database
        Dim strOrderQty           As Long
        Dim strSQL                As String
    
        strOrderQty = Me.OrderQty
    
        If strOrderQty > Val(Nz(Me.ExempNameID.Column(2), 0)) Then
            Set db = CurrentDb
            If Me.ExempNameID.Text = "" Then
                strSQL = "UPDATE ExempName" & vbCrLf & _
                         " SET ExempQty=ExempQty+" & strOrderQty & vbCrLf & _
                         " WHERE ExempNameID=" & Me.ExempNameID
                db.Execute strSQL, dbFailOnError
                'Check to see if the update ran properly
                If db.RecordsAffected = 0 Then
                    'No records were updated?!
                Else
                    'Everything appears to have gone smoothly
                End If
            Else
                strSQL = "UPDATE ExempName" & vbCrLf & _
                         " SET ExempQty=ExempQty-" & strOrderQty & vbCrLf & _
                         " WHERE ExempNameID=" & Me.ExempNameID
                db.Execute strSQL, dbFailOnError
                'Check to see if the update ran properly
                If db.RecordsAffected = 0 Then
                    'No records were updated?!
                Else
                    'Everything appears to have gone smoothly
                End If
            End If
            MsgBox "Sorry, the order qty is too large.", vbExclamation, "Warning"
            Me.ExempNameID = Null
        End If
    
    Error_Handler_Exit:
        On Error Resume Next
        If Not db Is Nothing Then Set db = Nothing
        Exit Sub
    
    Error_Handler:
        MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
               "Error Number: " & Err.Number & vbCrLf & _
               "Error Source: ExempNameID_AfterUpdate" & vbCrLf & _
               "Error Description: " & Err.Description & _
               Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
               , vbOKOnly + vbCritical, "An Error has Occured!"
        Resume Error_Handler_Exit
    End Sub


    Daniel Pineault, 2010-2016 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net



    Friday, June 30, 2017 8:22 PM
  • Thanks for responding, I tried your code. When I delete value  in the ExempNameID combobbox. I am receiving following error.

    Error Number: 3075

    Error Source: ExempNameID_AfterUpdate

    Error Description: Syntax error (missing operator) in query expression 'ExempNameID='.

    In addition, when I am typing large quantity in OrderQty the following condition is running & OrderQty is subtracting from the ExempQty.

     strSQL = "UPDATE ExempName" & vbCrLf & _
                         " SET ExempQty=ExempQty-" & strOrderQty & vbCrLf & _
                         " WHERE ExempNameID=" & Me.ExempNameID
                db.Execute strSQL, dbFailOnError

     
    Friday, June 30, 2017 8:42 PM
  • Change

    " WHERE ExempNameID=" & Me.ExempNameID

    to

    " WHERE ExempNameID=" & Nz(Me.ExempNameID, 0)


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, June 30, 2017 9:01 PM
  • Also I believe the line -

    If Me.ExempNameID.Text = "" Then

    should be :-

    If Me.ExempNameID = "" Then
    Peter Hibbs.
    Friday, June 30, 2017 9:35 PM
  • Hi Hans,

    I tried Nz() function according to your instructions. Now I am not getting query expression error. However, still the code isn't working.

    Also, I have noticed following issues in the code,

    1. When I am deleting a selected value in the combobox, it is giving me message "Sorry, the order qty is too large". Whereas, when a value is deleted the first strSQL expression should run instead of giving this message.
    2. When I am typing large OrderQty which is greater than ExempQty, it is giving me same message "Sorry, the order qty is too large". Also, the second strSQL expression is running which is subtracting OrderQty from the ExempQty. The message is Ok when the OrderQty is greater than ExempQty. But, it shouldn't run second strSQL expression.


    Saturday, July 1, 2017 3:14 PM
  • Hi Peter,

    I also tried your suggestion but nothing happened.

    Saturday, July 1, 2017 3:17 PM
  • See my suggestion higher up:

    Shouldn't there be an

    Else

    above the line

        MsgBox "Sorry, the order qty is too large.", vbExclamation, "Warning"


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Saturday, July 1, 2017 3:43 PM
  • Sorry Hans, I didn't get alert email of your post; therefore, I couldn't reply on time. I assumed that I should change If conditions.

    I tried below code, the second If condition is working when I select a value from combobox; however, when I am deleting a selected value first If condition isn't running & I am also receiving a message "Sorry, the order qty is too large" while deleting a value. I have bold the first If condition which isn't running for your better understanding.

    If Me.ExempNameID = "" Then
        strSQL = "UPDATE ExempName" & vbCrLf & _
        " SET ExempQty=ExempQty+" & lngOrderQty & vbCrLf & _
        " WHERE ExempNameID=" & Nz(Me.ExempNameID, 0)
        CurrentDb.Execute strSQL, dbFailOnError
    End If

    If lngOrderQty > Val(Nz(Me.ExempNameID.Column(2), 0)) Then
        MsgBox "Sorry, the order qty is too large.", vbExclamation, "Warning"
        Me.ExempNameID = Null
    Else
        strSQL = "UPDATE ExempName" & vbCrLf & _
        " SET ExempQty=ExempQty-" & lngOrderQty & vbCrLf & _
        " WHERE ExempNameID=" & Nz(Me.ExempNameID, 0)
        CurrentDb.Execute strSQL, dbFailOnError
    End If

    Sunday, July 2, 2017 5:52 PM
  • If ExempNameID is empty, it makes no sense to run an update query, does it?


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, July 2, 2017 5:59 PM
  • I just want that when a value is deleted from combobox the OrderQty should add in the ExempQty.

    What should I try instead of this?

    Sunday, July 2, 2017 6:08 PM
  • If ExempNameID is empty, how can we know what to update?

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, July 2, 2017 6:19 PM
  • Probably I am not getting your point or I couldn't explain the issue in the proper way. Ok, If the value is deleted from the combobox the ExempNameID will be empty.

    I wanted to set the condition that if the value is deleted the update query should run to add the OrderQty in the ExempQty.

    As the OrderQty is subtracting from the ExempQty when a value is selected from combobox. Similarly, the OrderQty should add in the ExempQty when a value is deleted from combobox.

    As you know, I am not a expert I just tried this through my own little knowledge. But, I don't understand what should I try instead of it.

    Sunday, July 2, 2017 7:22 PM
  • Hi Khurram Ahmed Khan,

    the issue with your code is when you delete the value from combobox then the value of "Me.ExempNameID" is Null.

    also you need to leave some space in query.

    but when you delete the value and then after you try to access the same value then you are not able to get the previous selected value.

    at that time you always get Null and that results in error.

    so you need to take that value before you delete. so that after whenever you need it you can use it.

    for that you need to create a global variable. then you can use "ExempNameID_GotFocus()" to store that value.

    now you can delete the value from combobox.

    and then in query you need to use value from the variable instead of the combo field column.

    so that you will not get error because of NULL.

    below is the example code.

    Option Compare Database
    Dim id As Long
    
    Private Sub ExempNameID_AfterUpdate()
    'If Me.OrderQty > Val(Nz(Me.ExempNameID.Column(2), 0)) Then
    'If Me.ExempNameID.Text = "" Then
    'Else
     '   MsgBox "Sorry, the order qty is too large.", vbExclamation, "Warning"
      '  Me.Undo
        
    'End If
    'End If
    Dim strOrderQty As Long
     Dim strSQL As String
    
     strOrderQty = Me.OrderQty
    
     If strOrderQty > Val(Nz(Me.ExempNameID.Column(2), 0)) Then
         If Me.ExempNameID.Text = "" Then
             strSQL = "UPDATE ExempName SET ExempQty = ExempQty + " & strOrderQty & " WHERE ExempNameID = " & id
             CurrentDb.Execute strSQL, dbFailOnError
         Else
             strSQL = "UPDATE ExempName SET ExempQty = ExempQty - " & strOrderQty & " WHERE ExempNameID = " & Me.ExempNameID
             CurrentDb.Execute strSQL, dbFailOnError
         End If
         MsgBox "Sorry, the order qty is too large.", vbExclamation, "Warning"
         Me.ExempNameID = ""
     End If
    End Sub
    
    Private Sub ExempNameID_BeforeUpdate(Cancel As Integer)
    
    End Sub
    
    Private Sub ExempNameID_GotFocus()
    Debug.Print (Me.ExempNameID)
    id = Nz(Me.ExempNameID)
    
    End Sub
    
    Private Sub Form_Load()
    Me.ExempNameID.RowSource = "SELECT ExempNameID, ExempName, ExempQty FROM ExempName WHERE ExempName Like '*" & Me.PartDescription.Value & "*' ORDER BY ExempNameID"
    End Sub
    

    I find that after making this changes , you can resolve your current issue but then also there is some issue in your logic that you need to correct by yourself.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, July 3, 2017 1:11 AM
    Moderator
  • Hi Deepak,

    Thanks for your explanation. I declared global variable as per your instructions; however, still bold part in the below code is not working when I delete quantity in the combobox.

    Option Compare Database
    Dim lngID As Long
    
    Private Sub ExempNameID_AfterUpdate()
     On Error GoTo Error_Handler
        Dim lngOrderQty As Long
        Dim strSQL As String
    
        lngOrderQty = Me.OrderQty
    
    If lngOrderQty > Val(Nz(Me.ExempNameID.Column(2), 0)) And Me.ExempName = Null Then
        MsgBox "Sorry, the order qty is too large.", vbExclamation, "Warning"
        Me.ExempNameID = Null
    Else
        strSQL = "UPDATE ExempName" & vbCrLf & _
        " SET ExempQty=ExempQty-" & lngOrderQty & vbCrLf & _
        " WHERE ExempNameID=" & Nz(Me.ExempNameID, 0)
        CurrentDb.Execute strSQL, dbFailOnError
    End If
        
    If Me.ExempNameID = Null Then
        strSQL = "UPDATE ExempName" & vbCrLf & _
        " SET ExempQty=ExempQty+" & lngOrderQty & vbCrLf & _
        " WHERE ExempNameID=" & lngID
        CurrentDb.Execute strSQL, dbFailOnError
    End If
        
    Error_Handler_Exit:
        On Error Resume Next
        Exit Sub
    
    Error_Handler:
        MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
               "Error Number: " & Err.Number & vbCrLf & _
               "Error Source: ExempNameID_AfterUpdate" & vbCrLf & _
               "Error Description: " & Err.Description & _
               Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
               , vbOKOnly + vbCritical, "An Error has Occured!"
        Resume Error_Handler_Exit
    End Sub
    
    Private Sub ExempNameID_GotFocus()
    Debug.Print (Me.ExempNameID)
    lngID = Nz(Me.ExempNameID)
    End Sub
    


    Before I was receiving message "Sorry, the OrderQty is too large." on deleting a value in the combobox. By adding And condition in the below line, I am not getting this message.

    If lngOrderQty > Val(Nz(Me.ExempNameID.Column(2), 0)) And Me.ExempName = Null Then

    Monday, July 3, 2017 6:42 PM
  • Hi Khurram Ahmed Khan,

    you had mentioned that," I declared global variable as per your instructions; however, still bold part in the below code is not working "

    which bold part? I can't see any bold part in code above.

    other thing you had mentioned that,"Before I was receiving message "Sorry, the OrderQty is too large." on deleting a value in the combobox. By adding And condition in the below line, I am not getting this message."

    it means that your 'And' condition is not satisfying. it gets false so you are not getting message.

    try to print the values in immediate window and check if your logic is wrong.

    I try to test the below part of your code on my side.

     If lngOrderQty > Val(Nz(Me.ExempNameID.Column(2), 0)) And Me.ExempName = Null Then
        MsgBox "Sorry, the order qty is too large.", vbExclamation, "Warning"
        Me.ExempNameID = Null
    Else
        strSQL = "UPDATE ExempName" & vbCrLf & _
        " SET ExempQty=ExempQty-" & lngOrderQty & vbCrLf & _
        " WHERE ExempNameID=" & Nz(Me.ExempNameID, 0)
        CurrentDb.Execute strSQL, dbFailOnError
    End If

    I find that your form does not have control "ExempName".

    so I get an error. your form have control "ExempNameID".

    so did you create a new control?

    in other places , I can see that you are still using "ExempNameID" control.

    so if you don't have "ExempName" then correct it in your code.

    when I check else part then I find that your code is not creating the full query. you no need to use vbCrLf .

     

    try to remove it and again try to debug the code and check the "strSQL" to verify that query is correct.

    other thing in if condition , I find that I am not getting value of "Val(Nz(Me.ExempNameID.Column(2), 0))".

    so try to check at your end that you are getting the correct value of it.

    Regards

    Deepak

     


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, July 4, 2017 2:21 AM
    Moderator
  • Hi Khurram,

    Could you share us your current project through OneDrive? I think the issue could be resolved quickly if we could reproduce your issue at our side. For sensitive information, I think you could crate a simple demo which could reproduce your issue.

    Regards,

    Tony


    Help each other

    Tuesday, July 4, 2017 8:20 AM
  • Hi Deepak,

    I was talking about the following query expression which I marked with bold font in my previous post.

    If Me.ExempNameID = Null Then
        strSQL = "UPDATE ExempName" & vbCrLf & _
        " SET ExempQty=ExempQty+" & lngOrderQty & vbCrLf & _
        " WHERE ExempNameID=" & lngID
        CurrentDb.Execute strSQL, dbFailOnError
    End If

    Later on I added a control "ExempName" according to Hans advice. The reason of adding this control is that the values in the "ExempNameID" which is a combobox weren't displaying properly. Therefore, Hans suggested me to add another control "ExempName" on top of the combobox except dropdown arrow.

    The And condition is satisfactory, the message I was receiving upon deleting a value in the combobox. Whereas, this message shouldn't appear. By adding And condition this issue has resolved. I just mentioned this in my previous post to let you know that this issue has overcome by adding And condition.

    I am not getting any error & all other part of the code is working except above strSQL expression. I am deleting a value but above strSQL expression isn't running. The above expression should run to add the OrderQty in the ExempQty.

    This is the only remaining issue in the code that has to be resolved. I don't understand why the above expression isn't running. Of course, there is something wrong. But, What? I need a clue to resolve this issue.

    Tuesday, July 4, 2017 4:38 PM
  • Hi Tony,

    Here is the OneDrive link to download the database,

    https://1drv.ms/f/s!AJg-0LB3x1iejAY

    Tuesday, July 4, 2017 5:21 PM
  • Hi Khurram Ahmed Khan,

    I try to check the code with your latest version of database.

    I find that the if condition you wrote to check Null value is not working.

    to check the Null value you can use IsNull function.

    Reference:

    IsNull Function

    I try to update your code and it is working.

     If IsNull(Me.ExempNameID) Then
        strSQL = "UPDATE ExempName" & vbCrLf & _
        " SET ExempQty=ExempQty+" & lngOrderQty & vbCrLf & _
        " WHERE ExempNameID=" & lngID
        Debug.Print strSQL
        CurrentDb.Execute strSQL, dbFailOnError
    End If
     

    the value of strSQL in immediate window.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, July 5, 2017 2:36 AM
    Moderator
  • Hi Deepak,

    Yes, you are right, the following If condition wasn't working,

    If Me.ExempNameID = Null

    Instead, I should've used IsNull function.

    Thanks a lot Deepak and other experts who responded on this thread.

    Wednesday, July 5, 2017 5:31 PM