Results 1 to 7 of 7
  1. #1
    joym is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2017
    Posts
    57

    Help Debug code

    Please have a look at the code. It is used to update trouble tickets (TT). It works however there is a slight issue. If the TT priority category is "Service Affected" and the TT status is "Closed" when i click the update button the 2nd set of if commands is executed and it calculates the required field and Saves it and displays a msgbox indicating update was successful and you click ok so that you can update another TT. The same should be done if the TT priority category is "Service Affected" and the TT status is "resolved". However it executes the 2nd set of if commands updates and displays the Msgbox indicating its updated. When you click ok on the messaged box it executes the 3rd set of if commands and only does one calculation and displays the msgbox.



    This should not happen.

    Is there something wrong with the way i have set my if command.







    Code:
    Private Sub btn_update_Click()
    
    
    If Me.TT_Status = "Open" Or Me.TT_Status = "Pending" Then
        DoCmd.Save
        MsgBox "TT Updates Succesfully", vbInformation, "Updated Successfully"
    End If
    
    
    If Me.cboPriorityCategory = "Service Affected" And Me.TT_Status = "Closed" Or Me.TT_Status = "Resolved" Then
        If IsNull(Me.txtRTF) Then
        MsgBox "Please Click on Resolution Time Frame if nothing appears check to see that you have selected the site from the site list. If you have selected the site from the site list and click on Resolution Time Frame and still does not appear please email Jyotesh", vbCritical, "Incomplete details"
        
        ElseIf IsNull(Me.Service_Person) Then
        MsgBox "Please Enter the Technician who worked on the fault", vbCritical, "Incomplete details"
        
        ElseIf IsNull(Me.txtVehicle) Then
        MsgBox "Please Enter the Vehicle Number", vbCritical, "Incomplete details"
        
        ElseIf IsNull(Me.Cause) Then
        MsgBox "Please enter the cause of fault identified by the technician ", vbCritical, "Incomplete details"
        
        ElseIf IsNull(Me.Solution) Then
        MsgBox "Please enter the the solution used to fix the fault", vbCritical, "Incomplete details"
        
        ElseIf IsNull(Me.txtRDT) Then
        MsgBox "Please Enter the date and time fault was resolved", vbCritical, "Incomplete details"
        
        ElseIf IsNull(Me.Closed_By) Then
        MsgBox "Please Enetr Your name In Closed By Field", vbCritical, "Incomplete details"
        
        Else
        
        Me.txtTOD = Diff2Dates("ymdhn", [Date Fault Lodged], txtRDT)
        Me.txtDSE = IIf(txtRDT > [txtRTF], Diff2Dates("ymdhn", [txtRTF], txtRDT), "")
        Me.txtSS = IIf([txtDSE] = "", "Within SLA", "SLA Exceeded")
        Me.Availability = Round((1 - ([txtA] / 525600)) * 100, 6)
        DoCmd.Save
        MsgBox "TT Updates Succesfully", vbInformation, "Updated Successfully"
         
        End If
    
    
    End If
    
    
    If Me.cboPriorityCategory = "Non Service Affected" And Me.TT_Status = "Closed" Or Me.TT_Status = "Resolved" Then
    
    
        If IsNull(Me.txtRTF) Then
        MsgBox "Please Click on Resolution Time Frame if nothing appears check to see that you have selected the site from the site list. If you have selected the site from the site list and click on Resolution Time Frame and still does not appear please email Jyotesh", vbCritical, "Incomplete details"
        
        ElseIf IsNull(Me.Service_Person) Then
        MsgBox "Please Enter the Technician who worked on the fault", vbCritical, "Incomplete details"
        
        ElseIf IsNull(Me.txtVehicle) Then
        MsgBox "Please Enter the Vehicle Number", vbCritical, "Incomplete details"
        
        ElseIf IsNull(Me.Cause) Then
        MsgBox "Please enter the cause of fault identified by the technician ", vbCritical, "Incomplete details"
        
        ElseIf IsNull(Me.Solution) Then
        MsgBox "Please enter the the solution used to fix the fault", vbCritical, "Incomplete details"
        
        ElseIf IsNull(Me.txtRDT) Then
        MsgBox "Please Enter the date and time fault was resolved", vbCritical, "Incomplete details"
        
        ElseIf IsNull(Me.Closed_By) Then
        MsgBox "Please Enetr Your name In Closed By Field", vbCritical, "Incomplete details"
        
        Else
        Me.txtTOD = Diff2Dates("ymdhn", [Date Fault Lodged], txtRDT)
        Me.txtDSE = ""
        Me.txtSS = ""
        Me.Availability = ""
        DoCmd.Save
        MsgBox "TT Updates Succesfully", vbInformation, "Updated Successfully"
        End If
    
    
    End If
    
    
    End Sub

  2. #2
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    And's and Or's don't live happily together. They need brackets.

    If Me.cboPriorityCategory = "Non Service Affected" And Me.TT_Status = "Closed" Or Me.TT_Status = "Resolved" Then

    not sure what belongs to what with this statement.

    Note: DoCmd.Save is saving the open object, which is the form - not the data.

  3. #3
    joym is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2017
    Posts
    57
    So basically ill have to separate the codes

    Code:
    If Me.cboPriorityCategory = "Service Affected" And Me.TT_Status = "Closed" Then
             If         
            End if 
    End if 
    
    If Me.cboPriorityCategory = "Service Affected" And Me.TT_Status = "Resolved" Then         
             If         
             End if
    End if

  4. #4
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Not if you can group them as suggested. The inference is like this:
    If a OR b AND c OR d then - if Access groups them successfully, it was a lucky break. The statement can be interpreted as
    If (a OR b) AND (c OR d)
    If a OR (b AND c) OR d
    If a OR b AND (c OR d) etc.

    Aside from that, you can simplfy code by reusing it as much as possible (via sub functions or procedures) and Select Case is sometimes better than a whole whack of If's and Elsei
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Code:
        If Me.cboPriorityCategory = "Service Affected" And (Me.TT_Status = "Closed" Or Me.TT_Status = "Resolved") Then
    should work....

  6. #6
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    That's what I had in my play version.

    @joym: Since I'm waiting for stain to dry, I played with the code to illustrate some points I alluded to. Not that I expect it would work as intended since I don't know exactly what the flow is. The point I'm making is, whenever possible, anything that's common should go outside of a decision block rather than be repeated in every one. In other words, if action A happens in block 1 and is exactly the same as in block 2, why repeat it? Hopefully, you'll get the idea from my sorry attempt to understand the flow of what's going on.

    Then there is testing for Null in a control. It is a rare occasion for me when that is not good enough, but it happened again today. The table field appeared empty, but it wasn't Null - it was an empty string. So I recommend a little function (I use IsNullEmpty) in a standard module. Then you can use it from anywhere. Also, there's a neat way to build a list of missing data and present it all at once, instead of umpteen times. See all below:

    Code:
    Private Sub btn_update_Click()
    Dim strMsg As String
    Dim bolMissing As Boolean
    
    '***************
    If IsNullEmpty(Me.Service_Person) Then  
      MsgBox "Please Enter the Technician who worked on the fault", vbCritical, "Incomplete details"
      bolMissing = True
    End If
    
    If IsNullEmpty(Me.txtVehicle) Then 
      MsgBox "Please Enter the Vehicle Number", vbCritical, "Incomplete details"
      bolMissing = True
    End If
    
    If IsNullEmpty(Me.Cause) Then 
      MsgBox "Please enter the cause of fault identified by the technician ", vbCritical, "Incomplete details"
      bolMissing = True
    End If
    
    If IsNullEmpty(Me.Solution) Then 
      MsgBox "Please enter the the solution used to fix the fault", vbCritical, "Incomplete details"
      bolMissing = True
    End If
    
    If IsNullEmpty(Me.txtRDT) Then 
      MsgBox "Please Enter the date and time fault was resolved", vbCritical, "Incomplete details"
      bolMissing = True
    End If
    
    If IsNullEmpty(Me.Closed_By) Then 
      MsgBox "Please Enter Your name In Closed By Field", vbCritical, "Incomplete details"
      bolMissing = True
    End If
    
    If bolMissing = True Then Exit Sub
    '*****************
    
    If Me.TT_Status = "Open" Or Me.TT_Status = "Pending" Then
        DoCmd.Save
        MsgBox "TT Updates Succesfully", vbInformation, "Updated Successfully"
     'shouldn't you exit sub here if all was successful?
    End If
    
    If Me.cboPriorityCategory = "Service Affected" And (Me.TT_Status = "Closed" Or Me.TT_Status = "Resolved") Then
      If IsNullEmpty(Me.txtRTF) Then
        strMsg = "Please Click on Resolution Time Frame. " & vbcrlf
        strMsg = strMsg & "If nothing appears, check that you have selected the site from the site list." & vbcrlf & vbcrlf
        strMsg = strMsg & "If you have selected a site from the site list and clicked on Resolution Time Frame" & vbcrlf
        strMsg = strMsg & "and it still does not appear, please email Jyotesh"
        MsgBox strMsg,vbCritical, "Incomplete details"
        'would have thought an Exit Sub statement should be here. Maybe not...
      End If
    
        Me.txtTOD = Diff2Dates("ymdhn", [Date Fault Lodged], txtRDT)
        Me.txtDSE = IIf(txtRDT > [txtRTF], Diff2Dates("ymdhn", [txtRTF], txtRDT), "")
        Me.txtSS = IIf([txtDSE] = "", "Within SLA", "SLA Exceeded")
        Me.Availability = Round((1 - ([txtA] / 525600)) * 100, 6)
    
    End If
    
    If Me.cboPriorityCategory = "Non Service Affected" And (Me.TT_Status = "Closed" Or Me.TT_Status = "Resolved") Then
        If IsNullEmpty(Me.txtRTF) Then
          MsgBox strMsg, vbCritical, "Incomplete details" 'this same message was already assigned to the variable.
          'another Exit Sub here??
        End If
    End If
        
        Me.txtTOD = Diff2Dates("ymdhn", [Date Fault Lodged], txtRDT)
        Me.txtDSE = ""
        Me.txtSS = ""
        Me.Availability = ""
        DoCmd.Save
        MsgBox "TT Updates Succesfully", vbInformation, "Updated Successfully"
        End If
    
    End If
    
    End Sub
    Code:
    Public Function IsNullEmpty(ctl As Control) As Boolean
    
    IsNullEmpty = False
    If IsNull(ctl) Or ctl = "" Then IsNullEmpty = True
    
    End Function
    All the stuff between ****'s can be simplified if you use the .Tag property of the required controls. If you enter Reqd (for example, no quotes) as a .Tag property value for those, then there'd be a declaration for a control object (Dim ctl As Control) and a list variable (strList) and the bolMissing is not needed. IF the label for the control is "attached" to the control, we can get the label caption instead of some cryptic control name. If it isn't an error would be raised if trying to get the control's unattached label caption. It would look like

    Code:
    On Error Resume Next
    strList = ""
    For each ctl in Me.Controls
      If IsNullEmpty(ctl) And ctl.Tag = "Reqd" Then strList = strList & " - " & Me.ctl.Controls(0).Caption & vbcrlf
    Next
    If strList <>"" Then 
      Msgbox "Please choose a value for: " & vbcrlf & strList
      Exit Sub
    End If
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    My example....

    I used looping and Select Case instead of the IF constructs.......
    Attached Files Attached Files

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 15
    Last Post: 03-21-2017, 12:19 PM
  2. Can't test code - "Debug" - "Run to Cursor"
    By GraeagleBill in forum Programming
    Replies: 4
    Last Post: 05-15-2016, 05:16 PM
  3. Code runs in break/debug mode but not in normal mode
    By hansendl in forum Programming
    Replies: 2
    Last Post: 10-15-2014, 07:23 AM
  4. Replies: 4
    Last Post: 11-26-2013, 10:47 AM
  5. Is There a Way to Debug or Step Through VB code?
    By jeffbase34 in forum Programming
    Replies: 1
    Last Post: 05-28-2009, 08:14 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums