troubleshooting Question

Why isn't my Code Triggering the Correct Line of Code

Avatar of gdunn59
gdunn59 asked on
Microsoft OfficeMicrosoft Access
110 Comments1 Solution412 ViewsLast Modified:
I have the following Code (see code section) that based off of whether one of the 3 fields (rst![Assoc],  rst![Opers], rst![Impact]) is marked "Y" then it should be triggering this line of code (I've also include the entire Private Sub after this line of code):

----->  ElseIf Me.cboAuditStatus = "Completed" And Me.Action_Required = "Yes" Then
          rtn = MsgBox("You have selected Completed as the Audit Status. Is this correct?",
            vbYesNo)
          If rtn = vbYes Then

ENTIRE SUB CODE:

Private Sub cboAuditStatus_AfterUpdate()
On Error GoTo Err_cboAuditStatus
Dim rtn As String
Dim rst As DAO.Recordset 'Create a variable named rst as a DAO Recordset object

Set rst = Me.frmQuality_Review_Subform.Form.RecordsetClone

DoCmd.Echo False

Me.cboAuditStatus.BackColor = RGB(224, 224, 224)

'Audit Report Completed -- No Action Required
If Me.cboAuditStatus = "Completed" And Me.Action_Required = "No" Then
rtn = MsgBox("You have selected Completed as the Audit Status. Is this correct?", vbYesNo)
  If rtn = vbYes Then
      Me.Completed_Date = Now()
      Me.Completed_Date.Requery
      'DoCmd.Save
  ' check to see if there are any errors
      rst.MoveFirst 'Set the pointer to the first record
      While Not rst.EOF  'create a loop to analyze each record
          ' Audit Report Completed -- Contains Errors -- No Action Required
          If rst![Assoc] = "Y" Or rst![Opers] = "Y" Or rst![Impact] = "Y" Then
              Call SendAuditCompletedRptErrorsNoAction
              DoCmd.GoToRecord , , acNewRec
              Me.InquiryNum.SetFocus
          ' Audit Report Completed -- Contains No Errors -- No Action Required -- send to Managers Allen Chrisman and LeaAnn Lois
          ElseIf rst![Assoc] = "N" Or rst![Opers] = "N" Or rst![Impact] = "N" _
                 And Me.cboMgrName Like "Chrisman*" Or Me.cboMgrName Like "Lois*" Then
              Call SendAuditCompletedRptNoErrorsNoAction
              DoCmd.GoToRecord , , acNewRec
              Me.InquiryNum.SetFocus
          ElseIf rst![Assoc] = "N" Or rst![Opers] = "N" Or rst![Impact] = "N" Then
              Exit Sub
          End If
       
      rst.MoveNext  'move to the next record
         
      Wend
  Else
      Me.Completed_Date = ""
      Me.Completed_Date.Requery
      Me.cboAuditStatus.SetFocusElseIf Me.cboAuditStatus = "Completed" And Me.Action_Required = "Yes" Then
rtn = MsgBox("You have selected Completed as the Audit Status. Is this correct?", vbYesNo)
  If rtn = vbYes Then

      Me.cboAuditStatus = "Draft"
 
  End If
 
      Me.Completed_Date = Now()
      Me.Completed_Date.Requery
  ' check to see if there are any errors
      rst.MoveFirst 'Set the pointer to the first record
      While Not rst.EOF  'create a loop to analyze each record (EOF means End Of File)
      ' Audit Report Completed -- Contains Errors - Action Required
          If rst![Assoc] = "Y" Or rst![Opers] = "Y" Or rst![Impact] = "Y" Then
              Call SendAuditCompletedRptActionReqWithErrors
              DoCmd.GoToRecord , , acNewRec
              Me.InquiryNum.SetFocus
          Else
              Exit Sub
              DoCmd.GoToRecord , , acNewRec
              Me.InquiryNum.SetFocus
          End If

      rst.MoveNext  'move to the next record
     
      Wend
  Else
      Me.Completed_Date = ""
      Me.Completed_Date.Requery
      Me.cboAuditStatus.SetFocus
      Me.cboAuditStatus = "Draft"
  End If
End If

DoCmd.GoToRecord , , acNewRec
Me.InquiryNum.SetFocus

rst.Close  'Close the recordset object
Set rst = Nothing 'Clear the variable to nothing
   
DoCmd.Echo True

Exit_cboAuditStatus:
    Exit Sub

Err_cboAuditStatus:
'    DoCmd.CancelEvent
'    Resume Exit_cboAuditStatus
    MsgBox Err.Description
    Resume Exit_cboAuditStatus
End Sub
ASKER CERTIFIED SOLUTION
NorieAnalyst Assistant
Join our community to see this answer!
Unlock 1 Answer and 110 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 110 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros