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

Have you tried stepping through the code to check out what's going on.

To do that, first add a breakpoint (F9) on the first line.

Now when the code is triggered execution will stop on that first line and you can step through it using F8.

As you do that check the values of all the controls involved.

You should also check the expressions in the If statements to see if anything is wrong with their logic.

I figured out why it isn't triggering the correct code, because it isn't looping through the recordset.  If the first record in the recordset has a "Y" then it works, but if the record in the recordset that has a "Y" is further down, then it never gets to that record to check it.

Now that I figured out why it isn't working, not sure how to correct it.

Thanks,
gdunn59

Yes, I put in a breakpoint and then did F* and it goes to the following line:

 ElseIf rst![Assoc] = "N" Or rst![Opers] = "N" Or rst![Impact] = "N" _

Thanks,
gdunn

Well, I thought it was something to do with the logic in the expressions but I may have just misread them - I tend to add parentheses to make things clearer.

I can't see anything wrong with the recordset, unless it isn't returning any records of course.

The record clearly has a "Y" in one of those fields, so not sure what's going on.

Thanks,
gdunn59

Perhaps it's the way VBA is interpreting the expression.

Have you tried addding parentheses around the sub-expressions either side of the And?

ElseIf (rst![Assoc] = "N" Or rst![Opers] = "N" Or rst![Impact] = "N") _
                 And (Me.cboMgrName Like "Chrisman*" Or Me.cboMgrName Like "Lois*") Then

PS That is the right piece of code?

No, it is actually this code, and I put parentheses around it and it still not working properly:

' 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

It never loops.

1. Comment out your error handler, so you can actually see what line it is failing on.
2. Comment out the: DoCmd.Echo..., Lines also.  They really have no relevance as far as the code goes.
(Besides you have no provision to turn the echo back on in your error handler anyway...)
3. Remove the "Calls" and insert the actual other sub/function(s) in this code
This might be where your issue is (In the "Called" sub/function)

4. Your next line of code after the call is to go to a new record... :
    Call SendAuditCompletedRptActionReqWithErrors
    DoCmd.GoToRecord , , acNewRec
So if the first condition is met, then the other code is "Called"
Then you immediately move to a new record...
If you are moving to a new record, then it can never go back and check the other records...
You may have to re-evaluate this code segment.

4. You did not post the code for:
    SendAuditCompletedRptActionReqWithErrors
So here again, it is hard to get the whole picture.


Also, FWIW, In reading through your code, I see a lot of things in there that are not normally part of a recordset loop.

So it may help if you took a step back and told us what this code was supposed to be doing.  in case there is a more efficient method.

JeffCoachman

JeffCoachman,

Ok, what for one scenario, the code should be doing this:

1.  Checks the field cboAuditStatus on the main form to see if it is marked "Completed" and also checks the field Action_Required on the main form to see if it is marked "No"; NEXT

2.  If the fields in step 1 are both TRUE then it moves the focus to the subform (frmQuality_Review_Subform);  NEXT

3.  It should loop through the subform and check the following 3 fields to see if any of them contain a "Y" for Yes:
    a.  rst![Assoc]
    b.  rst![Opers]
    c.  rst![Impact]

4.  If any of the three fields listed in Step 3 above contain a "Y", then it Calls the Function SendAuditCompletedRptErrorsNoAction.  This function creates a PDF of the current record on the screen and then creates an email and attaches the PDF to the email and sends out;

5.  After the email is sent, then the form moves to a new blank record and allows the user to input the next record, and the cursor's focus is set to the first field in the new record [InquiryNum].

There are 2 other scenarios in this code:

  1.  If the field cboAuditStatus on the main form is marked "Completed" And the field Action_Required is marked "No" and the 3 fields (Assoc, Opers, Impact) are marked "N", then run this code:

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

2.  If  the field boAuditStatus on the main form is marked "Completed" and the field Action_Required on the main form is marked "Yes", and the 3 fields (Assoc, Opers, Impact) contain a "Y", then do this code:

 If rst![Assoc] = "Y" Or rst![Opers] = "Y" Or rst![Impact] = "Y" Then
              Call SendAuditCompletedRptActionReqWithErrors
              DoCmd.GoToRecord , , acNewRec
              Me.InquiryNum.SetFocus


I have attached a document that contains the code for the Function "SendAuditCompletedRptErrorsNoAction".

I have also attached a document that contains the code for SendEmailCompletedRptErrorsNoAction, which is called from the first Function SendAuditCompletedRptErrorsNoAction.

Hope this gives you some clarification as to what I'm trying to accomplish, and if there is a better where to write the code, could you please share that with me.

Thanks,
gdunn
First-Function-that-is-called--S.docx
Second-Function-that-is-called-f.docx

JeffCoachman,

Once the first occurence of a "Y" is found in any of those 3 fields then it should call the first Function, then from the First Function it should call the second Function, then after the second function is completed and the email is sent, it should exit.

Thanks,
gdunn59

Have you tried splitting things up a bit?

Or re-arranging things?

For example if the first check indicates that you should go no further exit the sub.

Ok, I changed the code around.  

The second half of the code is working - starting at this line of code:
 
'Audit Report Completed -- CONTAINS ERRORS -- Action Required  ---- WORKING
If Me.cboAuditStatus = "Completed" And Me.Action_Required = "Yes" Then
rtn = MsgBox("You have selected Completed as the Audit Status. Is this correct?", vbYesNo)



THE FIRST PART OF THE CODE IS NOT WORKING (see below):
(No matter if the If Statement is a "Y" or an "N", it always skips the IF Statement with the "Y" and goes to the line with the IF Statement that has an "N":

FIRST PART OF CODE:

      Do Until rst.EOF
        Debug.Print rst![Quality_Review_Criteria]
          ' Audit Report Completed -- Contains Errors -- No Action Required
          If (rst![Assoc] = "Y") Or (rst![Opers] = "Y") Or (rst![Impact] = "Y") Then
            Call SendAuditCompletedRptErrorsNoAction
            Exit Do
          ' Audit Report Completed -- NO Errors -- No Action Required
          ElseIf (rst![Assoc] = "N") Or (rst![Opers] = "N") Or (rst![Impact] = "N") Then
            Call SendAuditCompletedRptNoErrorsNoAction
            Exit Do
          Else
            Exit Do
          End If
     
      rst.MoveNext
      Loop  'move to the next record
Private-Sub-cboAuditStatus.docx

Word Docs and code snippets are really not painting the whole picture here...

And again, you are still not saying *What* the code ultimately supposed to be doing...
Like I mentioned, ...Things like:
    DoCmd.GoToRecord , , acNewRec
...are typically not found in loops...

So, a sample DB would be better...

Follow these 14 steps:

Sample database notes:
1. Back up your database(s).
2. Combine the front and back ends into one database file.
3. Remove any startup options, unless they are relevant to the issue.
4. Remove any records unless they are relevant to the issue.
5. Delete any objects that do not relate directly to the issue.
6. Remove any references to any "linked" files (files outside of the database, Images, OLE Files, ...etc)
7. Remove any references to any third party Active-x Controls (unless they are relevant to the issue)
8. Remove, obfuscate, encrypt, or otherwise disguise, any sensitive data.
9. Compile the code. (From the VBA code window, click: Debug-->Compile)
10. Run the compact/Repair utility.
11. Remove any Passwords and/or security.
12. If a form is involved in the issue, set the Modal and Popup properties to: No
    (Again, unless these properties are associated with the issue)
13. Post the explicit steps to replicate the issue.
14. Test the database before posting.

In other words, ...post a database that we can easily open and immediately see and/or troubleshoot the issue.
And if applicable, also include a clear graphical representation of the *Exact* results you are expecting, based on the sample data.


JeffCoachman

JeffCoachman,

Steps:
1.  Open the form "frmEmployee_Audits";
2.  To replicate the issue I am having, I open the code for the "After_Update" event for the field "What is the status of the Audit (Draft or Completed)?" and put a break point on the following line:
 
     If Me.cboAuditStatus = "Completed" And Me.Action_Required = "No" Then

3.  There are 3 different scenarios that the code should be producing results for.  One out of the 3 of these are not working.  Below is the 1 scenario that is not working:

Scenario 1 (currently not working):
   Record entered has "No" in the "Action Required" field
   The field "What is the status of the Audit (Draft or Completed)?" is "Completed"
   There is at least one or more "Y" in one or more of the subform fields
      "Assoc (Y/N)", "Opers (Y/N)", or "Impact (Y/N)"

In Scenario 1, with the above selections in the fields mentioned, the following line of code should execute:

          If (rst![Assoc] = "Y") Or (rst![Opers] = "Y") Or (rst![Impact] = "Y") Then
            Call SendAuditCompletedRptErrorsNoAction
            Exit Do

But instead, it executes the following code:

          ElseIf (rst![Assoc] = "N") Or (rst![Opers] = "N") Or (rst![Impact] = "N") Then
            Call SendAuditCompletedRptNoErrorsNoAction
            Exit Do

I have Debug.Print rst![Quality_Review_Criteria] so I can see the fields as it is looping through the code.

Here is the entire code of the After_Update Event of the field "What is the status of the Audit (Draft or Completed)?" :

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 -- NOT WORKING
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
  ' check to see if there are any errors
      rst.MoveFirst 'Set the pointer to the first record
      Do Until rst.EOF
        Debug.Print rst![Quality_Review_Criteria]
'           Audit Report Completed -- Contains Errors -- No Action Required
          If (rst![Assoc] = "Y") Or (rst![Opers] = "Y") Or (rst![Impact] = "Y") Then
            Call SendAuditCompletedRptErrorsNoAction
            Exit Do
          ' Audit Report Completed -- NO Errors -- No Action Required
          ElseIf (rst![Assoc] = "N") Or (rst![Opers] = "N") Or (rst![Impact] = "N") Then
            Call SendAuditCompletedRptNoErrorsNoAction
            Exit Do
          Else
            rst.MoveNext
          End If
     
      rst.MoveNext
      Loop  'move to the next record
       
  End If
End If
     
'Audit Report Completed -- CONTAINS ERRORS -- Action Required  ---- WORKING
If 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.Completed_Date = Now()
      Me.Completed_Date.Requery
'  ' check to see if there are any errors
      rst.MoveFirst 'Set the pointer to the first record
    Do 'outer loop
      Do While rst![Assoc] = "N" Or rst![Opers] = "N" Or rst![Impact] = "N"  'create a loop to analyze each record
        Debug.Print rst![Quality_Review_Criteria]
          ' Audit Report Completed -- Contains Errors -- No Action Required
          If (rst![Assoc] = "Y") Or (rst![Opers] = "Y") Or (rst![Impact] = "Y") Then
            Call SendAuditCompletedRptActionReqWithErrors
            Exit Do
          Else
            rst.MoveNext
          End If
       
      Loop  'move to the next record
    Loop Until rst![Assoc] = "Y" Or rst![Opers] = "Y" Or rst![Impact] = "Y"
  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

JeffCoachman,

Sorry forgot to upload the database.

Here it is.

Thanks,
gdunn59
Audit-Database-Release-3.0--2-8-.accdb

In looking ove this project, I am confused as to how this form system got to this point without this issue being tested.
Because in order to trouble-shoot this I have to deconstruct the design, and work backwards

AFAICT, the code segment you are referring starts on the first record of the subform (rst.MoveFirst)
In this case, the First record is N,N,N
So the NNN segment of the code runs. (not the Y,Y,Y code)
Then this line runs:
    Call SendAuditCompletedRptNoErrorsNoAction
Then this line runs:
    Exit Do
...which exits the entire loop.
If the Loop is exited, then it never gets to a line with at least 1 "Y"

So your first step should always be to verify the condition:

'Audit Report Completed -- No Action Required -- NOT WORKING
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
  ' check to see if there are any errors
      rst.MoveFirst 'Set the pointer to the first record
      Do Until rst.EOF
      MsgBox rst![Assoc] & rst![Opers] & rst![Impact]

If the condition is not what you expected..
...Then Stop writing any more code until this is resolved...

I am also confused as to why the EmployeeQualityReviewInfo table has no primary key?
This would have made it easy to see that the code was only running for the first record.
    MsgBox rst!EQRI_ID

These are my determinations so far
...let me know...


JeffCoachman

JeffCoachman,

I did test this earlier, and it was working.  I originally got the code from EE. There has been so many changes to this database from the beginning, that I don't understand at this point what has happened.

So where do I go from here?

I need to know how to get it to loop (depending on which scenario it is using - COMPLETED -- ERRORS -- NO ACTION REQUIRED or COMPLETED -- ERRORS -- ACTION REQUIRED or COMPLETED -- NO ERRORS -- NO ACTION).

Thanks,
gdunn59

JeffCoachman,

There is no primary key on the EmployeeQualityReviewInfo because it is a many table linked to the tblEmployee_Audits table (one-to-many), linked by the InquiryID field.

Thanks,
gdunn59

I've just had a look at the code and I'm wondering why you are trying to do so much in one event.


What in words is the purpose of using the AfterUpdate event for that field?

ie what are you trying to achieve?

What I am trying to achieve is after the user chooses that the audit is complete (not a draft) then the audit record on the screen is emailed to certain people, based off of whether there are errors or not (which that is what the "Y" or "N" indicates) on the subform.

<I originally got the code from EE. There has been so many changes to this database from the beginning, that I don't understand at this point what has happened.>
This is the issue with asking a question, getting and answer, ...but and not understanding it...
As a rule, anytime you get a solution from *anybody* (me included) always ask that it be *Explained*
In other words, make sure you understand it, or don't use it.
See rule #8 here:
http://access.mvps.org/access/tencommandments.htm

I'm not trying to give you a hard time here, ..it just that in my 8 years here I have seen countless members have their projects get "out of control" because they got so many different solutions from so many different experts.

What typically happens here is that:
You ask a question and an expert gives you an answer.
It seems to work, so you accept it and move on.
Then you are asked to extend the functionality.
Then you post another question.
This time a new expert chimes in and posts another solution that "Works".
However this new modification may have caused unintended consequences in the original code.

In any event, in this case, the code is not working as expected.

<There is no primary key on the EmployeeQualityReviewInfo because it is a many table linked to the tblEmployee_Audits table (one-to-many), linked by the InquiryID field.>
Can you explain why that is a reason NOT to have a primary key?
I added in an Autonumber primary key and everything worked the same...
It is rare that a Primary key will ever hurt, if you don't need it, simply ignore it.
In this case it would have helped you indentify that the code was running for only one record in each case. (Only the first record)

Here you see why an "Explanation" of what the code is supposed to be doing is so important.

It is also not clear what your skill level with VBA, or database design is…

Like imnorie, I am wondering why so much functionality is crammed into this one event...and what the ultimate goal is here?:
To select one Parent record and update *many* (All) child records,
...or to select one Parent record and update a *specific* child record.

In this case try simply commenting out the "Exit Do’s" from both conditions:
(Again, In your code, as soon as the condition is met, a call is made and the loop is exited, and no other records will be processed)

'Audit Report Completed -- No Action Required -- NOT WORKING
    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
            ' check to see if there are any errors
            rst.MoveFirst 'Set the pointer to the first record
            Do Until rst.EOF
    '           Audit Report Completed -- Contains Errors -- No Action Required
                If (rst![Assoc] = "Y") Or (rst![Opers] = "Y") Or (rst![Impact] = "Y") Then
                    Call SendAuditCompletedRptErrorsNoAction
    '               Exit Do
                ' Audit Report Completed -- NO Errors -- No Action Required
                ElseIf (rst![Assoc] = "N") Or (rst![Opers] = "N") Or (rst![Impact] = "N") Then
                    Call SendAuditCompletedRptNoErrorsNoAction
    '               Exit Do
                Else
                    rst.MoveNext
                End If
         
                rst.MoveNext
            Loop  'move to the next record
           
        End If
    End If

Now, being that all the "Other" details of this code are unknown to me, I can’t say that this won’t fix the immediate issue, and subsequently cause another problem somewhere else.
(You may have to play around with that next to last: rstMoveNext)

It just seems to me that the logic here is *way* more complex and convoluted than it has to be…

If this does not help then I am afraid I will have to back out and let the other Experts assist further.

JeffCoachman

imnorie,

If my suggestion does not help, I will graciously step aside and leave this in your capable hands...

;-)

Jeff

Jeff,

Thanks Jeff,, but that didn't work.  Even though there was a "Y" in one of the records of the subform, it skipped over the If Statement . . . rst![Assoc] = "Y", etc., and went to the If Statement . . . rst[Assoc] = "N", and then it went into an infinite loop.

I know what I want, just don't know how to get there.

I just need it to loop through all the records in the subform until it finds a "Y" and then do the If Statement . . . rst![Assoc] = "Y", and then if it doesnt find a "Y" in any of the records in the subform, then do the If Statement . . . rst![Assoc] = "N".

Thanks,
gdunn59

Jeff

To be honest I thnk you've got a better read on this than I have.

imnorie,

You know me, points don't mean much to me.

To be honest, I think either of us can solve the issue, I just don't have that much more time to dedicate to it.

You know me, I am not here for the points.
So, I figured I'd step aside and let you have some fun.
;-)

I'll perhaps play around with what I think may be a solution...
But again, at some point, I may have to move on...

Jeff

I've just had another look at the code and I can't see why you need to check in Action_Required is Yes/No.

The code for both eventualities is very similar.

The first thing you should do in the code is check if AuditStatus is 'Complete'.

If it is continue with the code, if it's not just exit the sub.

When you continue with the code that's when you should check Action_Required and take the approriate action.

You also shouldn't set the recordset until you need it, which is after you've checked AuditStatus.

Something like this perhaps, which won't solve the problem but it might make it easier to find.

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

    'Audit Report Completed -- No Action Required -- NOT WORKING
    If Me.cboAuditStatus = "Completed" Then
        rtn = MsgBox("You have selected Completed as the Audit Status. Is this correct?", vbYesNo)

        If rtn = vbYes Then
        
            Me.Completed_Date = Now()
            
            
            
            Set rst = Me.frmQuality_Review_Subform.Form.RecordsetClone

            If Me.Action_Required = "No" Then

               
                    ' check to see if there are any errors
                    rst.MoveFirst    'Set the pointer to the first record
                    Do Until rst.EOF
                        Debug.Print rst![Quality_Review_Criteria]
                        '           Audit Report Completed -- Contains Errors -- No Action Required
                        If (rst![Assoc] = "Y") Or (rst![Opers] = "Y") Or (rst![Impact] = "Y") Then
                            Call SendAuditCompletedRptErrorsNoAction
                            Exit Do
                            ' Audit Report Completed -- NO Errors -- No Action Required
                        ElseIf (rst![Assoc] = "N") Or (rst![Opers] = "N") Or (rst![Impact] = "N") Then
                            Call SendAuditCompletedRptNoErrorsNoAction
                            Exit Do
                        Else
                            rst.MoveNext
                        End If

                        rst.MoveNext
                    Loop  'move to the next record


            End If
            'Audit Report Completed -- CONTAINS ERRORS -- Action Required  ---- WORKING
            If Me.Action_Required = "Yes" Then
                rst.MoveFirst    'Set the pointer to the first record
                Do    'outer loop
                    Do While rst![Assoc] = "N" Or rst![Opers] = "N" Or rst![Impact] = "N"  'create a loop to analyze each record
                        Debug.Print rst![Quality_Review_Criteria]
                        ' Audit Report Completed -- Contains Errors -- No Action Required
                        If (rst![Assoc] = "Y") Or (rst![Opers] = "Y") Or (rst![Impact] = "Y") Then
                            Call SendAuditCompletedRptActionReqWithErrors
                            Exit Do
                        Else
                            rst.MoveNext
                        End If

                    Loop  'move to the next record
                Loop Until rst![Assoc] = "Y" Or rst![Opers] = "Y" Or rst![Impact] = "Y"
            End If
        Else
            ' rtn not vbYes
            Exit Sub
        End If
    Else
        ' AuditStatus not Completed
        Exit Sub
    End If

    DoCmd.GoToRecord , , acNewRec
    Me.InquiryNum.SetFocus

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


Exit_cboAuditStatus:
    Exit Sub

Err_cboAuditStatus:
    '    DoCmd.CancelEvent
    '    Resume Exit_cboAuditStatus
    MsgBox Err.Description
    Resume Exit_cboAuditStatus
End Sub

Select allOpen in new window

imnorie,

I pasted in your code, here is the outcome:

These scenarios worked:

COMPLETED -- CONTAINS ERRORS -- ACTION REQUIRED IS "YES"
COMPLETED -- NO ERRORS -- ACTION REQUIRED IS "NO"

This scenario did not work:

COMPLETED -- CONTAINS ERRORS -- ACTION REQUIRED IS "NO"

Any further suggestions would be greatly appreciated.

Thanks,
gdunn59

What's supposed to happen in each of these scenarios, and what doesn't work for the last one?

One thing I noticed* in the code for Action Required ="NO" was that you had rst.MoveNext twice.

That could be causing the code to skip records.

I don't know if that could be a problem - I'll have a closer look tomorrow.

imnorie:

Ok.  Thanks.

I commented out one of the rst.movenext, and still not working properly.

Please let me know if you figure out anything tomorrow.  I will continue to try and figure it out also.

I really need to get this working before Monday, if possible.

Thanks,
gdunn

I think I really need to know what's supposed to happen and what doesn't happen in the code.

For example what isn't happening when COMPLETED -- CONTAINS ERRORS -- ACTION REQUIRED IS "NO".

Also, what are the 'ERRORS'?

Is it the YES/NO fields in the subform?

inmorie:

Please see my posting ID: 37575383 above.  It tells in detail what should be happening.

I don't get any errors, it just isn't going to the correct line of code when the specific criteria is met for the following:

  COMPLETED -- CONTAINS ERRORS -- NO ACTION REQUIRED

The other two criterias are working:

  COMPLETED -- CONTAINS ERRORS -- ACTION REQUIRED
  COMPLETED -- CONTAINS NO ERRORS -- NO ACTION REQUIRED

The code that I am currently using is the code that you posted in ID: 37582977.

Hope this helps.

Thanks,
gdunn59

That only seems to explain one scenario and you've still not said what doesn't happen in the scenario you say doesn't work.

As for 'errors' I didn't mean errors in the code, I was wondering what something like 'CONTAINS ERRORS' means.

There's nothing on the form (or sub form) named or labelled 'ERROR' or 'ERRORS'.

I mentioned in the last post what I think these 'ERRORS' are.

Anyway, how can I replicate the problem?

For example what fields should I set to what?

When I say errors, I mean if any of the following fields in the subform records contain a "Y" for Yes (meaning yes there are errors contained in the Audit):

  If (rst![Assoc] = "Y") Or (rst![Opers] = "Y") Or (rst![Impact] = "Y") Then (this line means that there are errors)

  If (rst![Assoc] = "N") Or (rst![Opers] = "N") Or (rst![Impact] = "N") Then (this line means there aren't errors)

So what is happening with the one scenario that is not working ( COMPLETED -- CONTAINS ERRORS -- NO ACTION REQUIRED), is it is going to the line that states the record is marked COMPLETED -- CONTAINS NO ERRORS (even though there is an error) and NO ACTION REQUIRED (which is correct):

So it should be processing this line of code for that scenario:

                'Audit Report Completed -- Contains Errors -- No Action Required
                If (rst![Assoc] = "Y") Or (rst![Opers] = "Y") Or (rst![Impact] = "Y") Then
                    Call SendAuditCompletedRptErrorsNoAction
                    Exit Do

But instead, it is processing this line of code for that scenario:

                    ' Audit Report Completed -- NO Errors -- No Action Required
                ElseIf (rst![Assoc] = "N") Or (rst![Opers] = "N") Or (rst![Impact] = "N") Then
                    Call SendAuditCompletedRptNoErrorsNoAction
                    Exit Do


To replicate the issue, mark the record "COMPLETED" for "What is the status of the Audit (Draft or Completed)?", Action Required should be "NO", and go to any record in the subform except the first record and change the Assoc (Y/N) field to a "Y".  The reason I say go to any record in the subform other than the first record because that is how I originally checked the code and thought it was working because it would get to the first record in the subform, find a "Y" and then exit out of the Sub, but it needs to loop the entire recordset of the subform looking for the first "Y" (in any of the 3 fields Assoc (Y/N), Opers (Y/N) or Impact (Y/N).

Hope this helps.

Thanks,
gdunn59

So basically if it finds one or more "Y" in any of the 3 fields mentioned above, in the subform recordset, then it is considered to contain an error.

Thanks,
gdunn59

So because the code encounters an "N" first in the subform recordset, it automatically processes the code for NO Errors, even though in one of the records in the subform recordset other than the first record contains a "Y", it skips over the line of code that specifies there are errors.

Thanks,
gdunn59

What happens if you delete the Exit Dos?

That's executed when you return from the subs being called, so the loop stops and no other records get checked.

Do you want to check all records?

I just commented out the Exit Do statements, and still the same issue plus it goes into an infinite loop and I had to Ctrl-Break to get out.

Yes, I need to loop through the entire subform recordset to check for either "Y" or "N".

Thanks,
gdunn59

I'm just trying it myself - it might not be as simple as that.

Why do you have 2 loops if Action_Required  is YES?

Is it there that the the code loops infinitely?

If it is that could be because of the criteria you are using to end the loop.

The outer loop won't stop until all three fields (Assoc, Opers, Impact) are "Y" on the subform, which they aren't in the data.

I'm not sure what you're talking about, I only see one loop that contains if Action_Required is "Yes".

I removed the Exit Do from both:

            ' check to see if there are any errors
            rst.MoveFirst    'Set the pointer to the first record
            Do Until rst.EOF
                Debug.Print rst![Quality_Review_Criteria]
                'Audit Report Completed -- Contains Errors -- No Action Required
                If (rst![Assoc] = "Y") Or (rst![Opers] = "Y") Or (rst![Impact] = "Y") Then
                    Call SendAuditCompletedRptErrorsNoAction
                    'Exit Do
                    ' Audit Report Completed -- NO Errors -- No Action Required
                ElseIf (rst![Assoc] = "N") Or (rst![Opers] = "N") Or (rst![Impact] = "N") Then
                    Call SendAuditCompletedRptNoErrorsNoAction
                    'Exit Do
                Else
                    rst.MoveNext
                End If

                rst.MoveNext
            Loop  'move to the next record

    End If

Weird.

If you look at lines 42-57 in the last code I posted, which is what I thought you were using there are 2 loops.

One starts at line 44 and (should end?) ends at line 56, the other starts at line 45 and (should end?)ends at line 55.

Yes, I looked at your lines of code 42-57, and I am using the last code you posted (see below), but I still only see one line that says "Action_Required" = "Yes". Yes there are 2 loops the first one is  If Me.Action_Required = "No" Then, and the second one is  If Me.Action_Required = "Yes" Then:

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

'Audit Report Completed -- No Action Required -- NOT WORKING
If Me.cboAuditStatus = "Completed" Then
rtn = MsgBox("You have selected Completed as the Audit Status. Is this correct?", vbYesNo)

If rtn = vbYes Then

    Me.Completed_Date = Now()
   
    Set rst = Me.frmQuality_Review_Subform.Form.RecordsetClone

    If Me.Action_Required = "No" Then
       
            ' check to see if there are any errors
            rst.MoveFirst    'Set the pointer to the first record
            Do Until rst.EOF
                Debug.Print rst![Quality_Review_Criteria]
                'Audit Report Completed -- Contains Errors -- No Action Required
                If (rst![Assoc] = "Y") Or (rst![Opers] = "Y") Or (rst![Impact] = "Y") Then
                    Call SendAuditCompletedRptErrorsNoAction
                    'Exit Do
                    ' Audit Report Completed -- NO Errors -- No Action Required
                ElseIf (rst![Assoc] = "N") Or (rst![Opers] = "N") Or (rst![Impact] = "N") Then
                    Call SendAuditCompletedRptNoErrorsNoAction
                    'Exit Do
                Else
                    rst.MoveNext
                End If

                rst.MoveNext
            Loop  'move to the next record


    End If
    'Audit Report Completed -- CONTAINS ERRORS -- Action Required  ---- WORKING
    If Me.Action_Required = "Yes" Then
        rst.MoveFirst    'Set the pointer to the first record
        Do    'outer loop
            Do While rst![Assoc] = "N" Or rst![Opers] = "N" Or rst![Impact] = "N"  'create a loop to analyze each record
                Debug.Print rst![Quality_Review_Criteria]
                ' Audit Report Completed -- Contains Errors -- No Action Required
                If (rst![Assoc] = "Y") Or (rst![Opers] = "Y") Or (rst![Impact] = "Y") Then
                    Call SendAuditCompletedRptActionReqWithErrors
                    Exit Do
                Else
                    rst.MoveNext
                End If

            Loop  'move to the next record
        Loop Until rst![Assoc] = "Y" Or rst![Opers] = "Y" Or rst![Impact] = "Y"
    End If
Else
        ' rtn not vbYes
        Exit Sub
    End If
Else
    ' AuditStatus not Completed
    Exit Sub
End If

DoCmd.GoToRecord , , acNewRec
Me.InquiryNum.SetFocus

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


Exit_cboAuditStatus:
    Exit Sub

Err_cboAuditStatus:
    '    DoCmd.CancelEvent
    '    Resume Exit_cboAuditStatus
    MsgBox Err.Description
    Resume Exit_cboAuditStatus
End Sub

There are two loops one labelled, by yourself 'Outer Loop'.

 Do    'outer loop
            Do While rst![Assoc] = "N" Or rst![Opers] = "N" Or rst![Impact] = "N"  'create a loop to analyze each record
                Debug.Print rst![Quality_Review_Criteria]
                ' Audit Report Completed -- Contains Errors -- No Action Required
                If (rst![Assoc] = "Y") Or (rst![Opers] = "Y") Or (rst![Impact] = "Y") Then
                    Call SendAuditCompletedRptActionReqWithErrors
                    Exit Do
                Else
                    rst.MoveNext
                End If

            Loop  'move to the next record
        Loop Until rst![Assoc] = "Y" Or rst![Opers] = "Y" Or rst![Impact] = "Y"

Select allOpen in new window

If we remove the code within the loops perhaps it's clearer:

 Do    'outer loop
            Do While rst![Assoc] = "N" Or rst![Opers] = "N" Or rst![Impact] = "N"  'create a loop to analyze each record
            ' code
            Loop  'move to the next record
 Loop Until rst![Assoc] = "Y" Or rst![Opers] = "Y" Or rst![Impact] = "Y"

Select allOpen in new window

Yes, I did the outer loop to see if it would loop through the entire subform recordset until it comes across the first "Y", and if so, then run the inner loop for "Y".

Thanks,
gdunn59

But the loop will never end unless it finds a Y.

Yes, I know it isn't correct.  That is why I am trying to get some assistance from EE.

Thanks,
gdunn59

I'm trying to help here but to do so I need to understand what's going on.

If that code is wrong remove it.

As I mentioned in other postings, there are 3 different scenarios, and two out of the three work like I need them to, it is the 3rd one that is not working.  So I don't want to remove any code because of that.  One of the ones that is working is in the first part that includes the two loops.

Thanks,
gdunn59

Also out of the first one with the 2 loops where one is working, the other scenario is not working that is included in this 2 loops.

Thanks,
gdunn59

Were you able to F8 through the code and replicate the issue I am referring to where even though there is an error, it automatically goes to the If statement that says there are no errors?

Thanks,
gdunn59

The part of the code that says to loop basically until it finds a "Y" is the 3rd part of the code, which is also working fine (see code below).

The code where the 2nd part isn't working does not include an outer loop.

    'Audit Report Completed -- CONTAINS ERRORS -- Action Required  ---- WORKING
    If Me.Action_Required = "Yes" Then
        rst.MoveFirst    'Set the pointer to the first record
        Do    'outer loop
            Do While rst![Assoc] = "N" Or rst![Opers] = "N" Or rst![Impact] = "N"  'create a loop to analyze each record
                Debug.Print rst![Quality_Review_Criteria]
                ' Audit Report Completed -- Contains Errors -- No Action Required
                If (rst![Assoc] = "Y") Or (rst![Opers] = "Y") Or (rst![Impact] = "Y") Then
                    Call SendAuditCompletedRptActionReqWithErrors
                    Exit Do
                Else
                    rst.MoveNext
                End If

            Loop  'move to the next record
        Loop Until rst![Assoc] = "Y" Or rst![Opers] = "Y" Or rst![Impact] = "Y"
    End If
Else
        ' rtn not vbYes
        Exit Sub
    End If
Else
    ' AuditStatus not Completed
    Exit Sub
End If

If you don't want to remove any code then I don't know how I can help you further.

I've tried to explain why things aren't working, for example the Exit Dos stopping the code going through the whole recordset.

Part of the problem you mentioned involved going into an infinite loop and I've tried to explain why that is too.

It only went into an infinite loop if I removed the Exit Do statement.

Isn't there a way to capture the Y or N value in a variable and then once that variable is a "Y" then run the one code, otherwise run the other code?

Thanks,
gdunn59

That would be possible and that's probably the way I would have suggested doing this in the first place.

It would involve altering the code a lot though.

Would you be able to assist me with that because I'm not sure exactly how to do it?

Thanks,
gdunn59

imnorie:

Can you please let me know if you are going to be able to assist me with this, so if not, I can try to find someone who can assist me.

Thanks,
gdunn59

I'm sorry I can't, it's past midnight here.

Thank you!

I've had another look and came up with this.

I think it works correctly but you'll need to check that.

I've included debug statements that will print the criteria and the values in Assoc, Opers and Impact.

If one of the subs would be called I've added a debug to print which one.

I've tried it with various values in the fields and as far as I can see the correct sub would be getting called.

Note I've commented out the calls to the subs and a few other things for debugging/testing.



Anyway, here's the code.

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

    'Audit Report Completed -- No Action Required -- NOT WORKING
    If Me.cboAuditStatus = "Completed" Then
        rtn = MsgBox("You have selected Completed as the Audit Status. Is this correct?", vbYesNo)

        If rtn = vbYes Then

            Me.Completed_Date = Now()



            Set rst = Me.frmQuality_Review_Subform.Form.RecordsetClone

            If Me.Action_Required = "No" Then


                ' check to see if there are any errors
                rst.MoveFirst    'Set the pointer to the first record
                Do Until rst.EOF
                    Debug.Print rst![Quality_Review_Criteria] & vbCrLf & " Assoc-" & rst![Assoc] & " Opers-" & rst![Opers] & " Impact-" & rst![Impact]

                    '           Audit Report Completed -- Contains Errors -- No Action Required
                    If (rst![Assoc] = "Y") Or (rst![Opers] = "Y") Or (rst![Impact] = "Y") Then
                        Debug.Print "Call SendAuditCompletedRptErrorsNoAction"
                        ' Call SendAuditCompletedRptErrorsNoAction
                        ' Audit Report Completed -- NO Errors -- No Action Required
                    ElseIf (rst![Assoc] = "N") Or (rst![Opers] = "N") Or (rst![Impact] = "N") Then
                        Debug.Print "Call SendAuditCompletedRptNoErrorsNoAction"
                        ' Call SendAuditCompletedRptNoErrorsNoAction
                    End If

                    rst.MoveNext
                Loop  'move to the next record


            End If
            'Audit Report Completed -- CONTAINS ERRORS -- Action Required  ---- WORKING
            If Me.Action_Required = "Yes" Then
                rst.MoveFirst    'Set the pointer to the first record
                Do Until rst.EOF

                    Debug.Print rst![Quality_Review_Criteria] & vbCrLf & " Assoc-" & rst![Assoc] & " Opers-" & rst![Opers] & " Impact-" & rst![Impact]
                    ' Audit Report Completed -- Contains Errors -- No Action Required
                    If (rst![Assoc] = "Y") Or (rst![Opers] = "Y") Or (rst![Impact] = "Y") Then

                        Debug.Print "Call SendAuditCompletedRptActionReqWithErrors"
                        ' Call SendAuditCompletedRptActionReqWithErrors
                    End If
                    rst.MoveNext
                Loop
            End If
        Else
            ' rtn not vbYes
            Exit Sub
        End If
    Else
        ' AuditStatus not Completed
        Exit Sub
    End If

    'DoCmd.GoToRecord , , acNewRec
    'Me.InquiryNum.SetFocus

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


Exit_cboAuditStatus:
    Exit Sub

Err_cboAuditStatus:

    MsgBox Err.Description
    Resume Exit_cboAuditStatus
End Sub

Select allOpen in new window

Thanks.  I will check in in about an hour or so.

imnorie:

I pasted in your lastest code and tried it and here are my results:

1.  COMPLETED -- ACTION REQUIRED -- ERRORS (this worked okay).

2.  COMPLETED -- NO ACTION REQUIRED -- NO ERRORS (this worked okay).

3.  COMPLETED -- NO ACTION RQUIRED -- ERRORS (this does not work).

No. 3 that is not working skips over the following line of Code:

                      'Audit Report Completed -- Contains Errors -- No Action Required
                    If (rst![Assoc] = "Y") Or (rst![Opers] = "Y") Or (rst![Impact] = "Y") Then
                        Debug.Print "Call SendAuditCompletedRptErrorsNoAction"
                        'Call SendAuditCompletedRptErrorsNoAction
                        ' Audit Report Completed -- NO Errors -- No Action Required

and goes to this line of Code instead:
                    ElseIf (rst![Assoc] = "N") Or (rst![Opers] = "N") Or (rst![Impact] = "N") Then
                        Debug.Print "Call SendAuditCompletedRptNoErrorsNoAction"
                        'Call SendAuditCompletedRptNoErrorsNoAction


So it is still doing exactly what I mentioned in my posting ID: 37584857 above.  So nothing has changed as far as the way things are working.  Two out of the three scenarios are working.

I had asked you about changing the code so that there is a variable that collects the information (Y or N) and stores it in that variable and while its looping once it comes across the first "Y" in one of the records of the subform, that it processes the first scenario:

                      'Audit Report Completed -- Contains Errors -- No Action Required
                    If (rst![Assoc] = "Y") Or (rst![Opers] = "Y") Or (rst![Impact] = "Y") Then
                        Debug.Print "Call SendAuditCompletedRptErrorsNoAction"
                        'Call SendAuditCompletedRptErrorsNoAction
                        ' Audit Report Completed -- NO Errors -- No Action Required

otherwise if it doesn't find a "Y" while looping through the subform's recordset then it processes the 2nd scenario:
                   
                  'Audit Report Completed -- Contains NO Errors -- No Action Required
                   ElseIf (rst![Assoc] = "N") Or (rst![Opers] = "N") Or (rst![Impact] = "N") Then
                        Debug.Print "Call SendAuditCompletedRptNoErrorsNoAction"
                        'Call SendAuditCompletedRptNoErrorsNoAction

Would you be able to help with the code that captures the "Y or N" in a variable?

Thanks,
gdunn

After thinking about it I don't see how capturing the field value in a variable would help.

How did you check that the code wasn't working?

Try changing the code to this

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

    'Audit Report Completed -- No Action Required -- NOT WORKING
    If Me.cboAuditStatus = "Completed" Then
        rtn = MsgBox("You have selected Completed as the Audit Status. Is this correct?", vbYesNo)

        If rtn = vbYes Then

            Me.Completed_Date = Now()



            Set rst = Me.frmQuality_Review_Subform.Form.RecordsetClone

            If Me.Action_Required = "No" Then


                ' check to see if there are any errors
                rst.MoveFirst    'Set the pointer to the first record
                Do Until rst.EOF
                    Debug.Print rst![Quality_Review_Criteria] & " - Assoc-" & rst![Assoc] & " Opers-" & rst![Opers] & " Impact-" & rst![Impact]

                    '           Audit Report Completed -- Contains Errors -- No Action Required
                    If (rst![Assoc] = "Y") Or (rst![Opers] = "Y") Or (rst![Impact] = "Y") Then
                        Debug.Print "Call SendAuditCompletedRptErrorsNoAction"
                        Debug.Print
                        ' Call SendAuditCompletedRptErrorsNoAction
                        ' Audit Report Completed -- NO Errors -- No Action Required
                    Else
                        Debug.Print "Call SendAuditCompletedRptNoErrorsNoAction"
                        Debug.Print
                        ' Call SendAuditCompletedRptNoErrorsNoAction
                    End If

                    rst.MoveNext
                Loop  'move to the next record


            End If
            'Audit Report Completed -- CONTAINS ERRORS -- Action Required  ---- WORKING
            If Me.Action_Required = "Yes" Then
                rst.MoveFirst    'Set the pointer to the first record
                Do Until rst.EOF

                    Debug.Print rst![Quality_Review_Criteria] & vbCrLf & " Assoc-" & rst![Assoc] & " Opers-" & rst![Opers] & " Impact-" & rst![Impact]
                    ' Audit Report Completed -- Contains Errors -- No Action Required
                    If (rst![Assoc] = "Y") Or (rst![Opers] = "Y") Or (rst![Impact] = "Y") Then

                        Debug.Print "Call SendAuditCompletedRptActionReqWithErrors"
                        
                        ' Call SendAuditCompletedRptActionReqWithErrors
                    End If
                    rst.MoveNext
                Loop
            End If
        Else
            ' rtn not vbYes
            Exit Sub
        End If
    Else
        ' AuditStatus not Completed
        Exit Sub
    End If

    'DoCmd.GoToRecord , , acNewRec
    'Me.InquiryNum.SetFocus

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


Exit_cboAuditStatus:
    Exit Sub

Err_cboAuditStatus:

    MsgBox Err.Description
    Resume Exit_cboAuditStatus
End Sub

Select allOpen in new window

When run that with status completed and  action no I get these results in the Immediate window.

For each record in the subform it shows the criteria followed by the values in Assoc, Opers and Impact and then the name of the sub that would be called.

21.12 - RX PCN entered or updated correctly - Assoc-Y Opers-N Impact-Y
Call SendAuditCompletedRptErrorsNoAction

4.01 - Inquiry attached to the correct member. - Assoc-Y Opers-N Impact-Y
Call SendAuditCompletedRptErrorsNoAction

4.02 - Inquiry forwarded to correct queue or department. - Assoc-N Opers-Y Impact-N
Call SendAuditCompletedRptErrorsNoAction

4.03 - Reason for transaction appropriately documented. - Assoc-N Opers-N Impact-N
Call SendAuditCompletedRptNoErrorsNoAction

4.04 - Followed guidelines for documentation. - Assoc-N Opers-N Impact-N
Call SendAuditCompletedRptNoErrorsNoAction

4.05 - Documented systems and resources used - Assoc-N Opers-N Impact-N
Call SendAuditCompletedRptNoErrorsNoAction

7.01 - Member contacted to obtain additional information. - Assoc-Y Opers-N Impact-N
Call SendAuditCompletedRptErrorsNoAction

7.02 - Corporate VIA guidelines performed and documented. - Assoc-N Opers-N Impact-N
Call SendAuditCompletedRptNoErrorsNoAction

7.03 - CMS HIPAA guidelines correctly applied and documented. - Assoc-N Opers-N Impact-N
Call SendAuditCompletedRptNoErrorsNoAction

16.01 - System updated correctly. - Assoc-N Opers-Y Impact-N
Call SendAuditCompletedRptErrorsNoAction

16.02 - Completed timely. - Assoc-N Opers-N Impact-N
Call SendAuditCompletedRptNoErrorsNoAction

16.03 - Appropriate correspondence sent. - Assoc-N Opers-N Impact-N
Call SendAuditCompletedRptNoErrorsNoAction

21.01 - Correctly determined if COB response is complete - Assoc-N Opers-N Impact-N
Call SendAuditCompletedRptNoErrorsNoAction

21.02 - Correct action taken on COB record(s) - Assoc-N Opers-N Impact-N
Call SendAuditCompletedRptNoErrorsNoAction

21.03 - COB Source entered or updated correctly - Assoc-N Opers-N Impact-N
Call SendAuditCompletedRptNoErrorsNoAction

21.04 - REC type entered or updated correctly - Assoc-N Opers-N Impact-N
Call SendAuditCompletedRptNoErrorsNoAction

21.05 - MSP type entered or updated correctly - Assoc-N Opers-N Impact-N
Call SendAuditCompletedRptNoErrorsNoAction

21.06 - Policy # entered or updated correctly - Assoc-N Opers-N Impact-N
Call SendAuditCompletedRptNoErrorsNoAction

21.07 - Effective date entered or updated correctly - Assoc-N Opers-N Impact-N
Call SendAuditCompletedRptNoErrorsNoAction

21.08 - Term date entered or updated correctly - Assoc-N Opers-N Impact-N
Call SendAuditCompletedRptNoErrorsNoAction

21.09 - RX ID entered or updated correctly - Assoc-N Opers-N Impact-N
Call SendAuditCompletedRptNoErrorsNoAction

21.10 - RX Group # entered or updated correctly - Assoc-N Opers-N Impact-N
Call SendAuditCompletedRptNoErrorsNoAction

21.11 - RX Bin entered or updated correctly - Assoc-N Opers-N Impact-N
Call SendAuditCompletedRptNoErrorsNoAction

As you can see where one of Assoc, Opers and Impact = Y then SendAuditCompletedRptErrorsNoAction will be called (5 times with this recordset)

When Assoc, Opers and Impact all = N then SendAuditCompletedRptErrorsNoAction would be called.

I checked by putting in a Break Point and F8 through the code.

I will try the lastest code that you posted (ID: 37590795) and let you know.

Thanks,
Gina

Gina

When you stepped through the code were you checking the values in the fields as you went?

Yes.

Ok.  I tried your last code, and now two out of the three are not working.

Here are my results:

1)  WORKS:  Debug.Print Results for:  COMPLETED – ACTION REQUIRED -- ERRORS

2)  WORKS SOMEWHAT:  Debug.Print Results for:  COMPLETED – NO ACTION REQUIRED – NO ERRORS (this one calls the right function and emails the correct Audit report, but it goes into an infinite LOOP).

DOESN'T WORK:  Debug.Print Results for:  COMPLETED – NO ACTION REQUIRED -- ERRORS (this one processes the same code that the #2 does above, even though there is a "Y" in the that has the quality review criteria 6.07).  THIS ONE ALSO GOES INTO AN INFINITE LOOP - since it is using the same code as 2 above.

I have also attached a word document that contains all the Debug.Prints for all three scenarios.

Thanks,
gdunn
Debug-Print-Results-for-EE.docx

Where are the errors here?

6.01 - Inquiry attached to the correct member
 Assoc-N Opers-N Impact-N

And here?

6.02 - Documented resource and systems used
 Assoc-N Opers-N Impact-N

There are only 3 records in the Word document that don't have Assoc-N, Opers-N and Impact-N.

All along I've ben assuming that meant there were no errors.

The 3 records have Assoc N/A.

As for the infinite loop, can you tell me which loop that happens in, what the value of Criteria, Assoc, Opers and Impact when it happens?

Forget the first part of the last post - I hadn't looked through the whole document.

Still like to know the values when the code goes into an infinite loop.

By the way, are you actually calling the subs in the code?

The only time it is considered an error is if there is a "Y", which I have mentioned throughout my postings.  

Could the problem be because there are "N/A" in some of the records?

I tried all the scenarios in the Word document and as far as I can see they worked.

Not at any time did it end up in an infinite loop.

Can you post a few of examples of the values when the code isn't working?

Just 2 or 3 rather than a whole load.

Perhaps highlight the ones that didn't work in the Word document and repost it?

Ok.  I have attached another database.  The one that I'm using with 3 records.  One for each scenario.

Record 1:  COMPLETED -- ACTION REQUIRED = "Yes" --  CONTAINS ERRORS ("Y")
Record 2:  COMPLETED -- ACTION REQUIRED = "No"  --  CONTAINS NO ERRORS ("N")
Record 3:  COMPLETED -- ACTION REQUIRED = "No"  --  CONTAINS ERRORS ("Y")

I've already selected all the necessary criteria for each record, so all you need to do its put in a break point and F8 through the code to see what I'm talking about.

Maybe if you can see exactly what I'm using and what I'm getting, you can understand better whats going on for me.

Thanks,
gdunn59
Audit-Database-Release-3.0--2-13.accdb

Thanks, that's a good idea at least we'll know that we both have the same one with the same code.

I think rather than just posting the code I'll upload a revised version with any changes I make.

Ok.  Thanks!

I really don't get it.

The code in the uploaded database seems to work fine when I test it.

As for the infinte loops, at no point does it go into an infinite loop and I'm pretty sure it's impossible for the code to go into one.

By the way, by a few records I really meant records in the subform.

It would also be very useful if you could point me to specific instances when the code doesn't work

Give the values of all the relevant fields, how the code doesn't work and how it should work.

I'll record some of the results and attach them later - Excel OK for that?

Here's a file with the debug output for each record for both Completed YES an Completed NO.

Can you go through this and tell me where there's problems?
Debug---14Feb2012.xlsx

Ok.  Thanks!

Ok, so here is what is happening.

When you were stepping through the code or running the code did you have the actual "Call SendAuditCompletedRptNoErrorsNoAction" commented out?

The reason I ask is this seems to make a difference as far as the ways things come out.

What I found out is if "Call SendAuditCompletedRptNoErrorsNoAction" is commented out it seems to work fine, but if I comment Call SendAuditCompletedRptNoErrorsNoAction back in, and this is the reason why I thought it was going into an infinite loop is because it gets to the first record (for 8080_021020120939, Quality Review Criteria on the subform 4.01) and then the cursor goes to the line that calls the Function Call SendAuditCompletedRptNoErrorsNoAction and sends the email, then it loops to the second record, etc., and does the same thing.  So the loop is stopping after each row/record in the subform and processes the Function Call SendAuditCompletedRptNoErrorsNoAction.  So for that particular record 8080_021020120939 there ends up being processed for Subform Quality Review Criteria 4.01 - 21.12.

Thanks,
gdunn59

What happens with the first record (9999_021220122050) when the "Call SendAuditCompletedRptErrorsNoAction" is commented back in, is it first goes to the code "If (rst![Assoc] = "Y") Or (rst![Opers] = "Y") Or (rst![Impact] = "Y") Then" and debug.prints "4.01 - Inquiry attached to the correct member. - Assoc-N Opers-N Impact-N" and then it moves to the code:

"Else
 Debug.Print "Call SendAuditCompletedRptNoErrorsNoAction"
 Debug.Print
'Call SendAuditCompletedRptNoErrorsNoAction

and it continues to loop until it comes to Quality Review Criteria 4.05 in the subform that contains a "Y".  At this point this is what is in the immediate window:

Call SendAuditCompletedRptNoErrorsNoAction

4.02 - Inquiry forwarded to correct queue or department. - Assoc-N Opers-N Impact-N
Call SendAuditCompletedRptNoErrorsNoAction

4.03 - Reason for transaction appropriately documented. - Assoc-N Opers-N Impact-N
Call SendAuditCompletedRptNoErrorsNoAction

4.04 - Followed guidelines for documentation. - Assoc-N Opers-N Impact-N
Call SendAuditCompletedRptNoErrorsNoAction

4.05 - Documented systems and resources used - Assoc-Y Opers-N Impact-N
4.01 - Inquiry attached to the correct member. - Assoc-N Opers-N Impact-N

and the cursor is sitting on this line of code:

Debug.Print "Call SendAuditCompletedRptErrorsNoAction"

Next, I continue to F8 through code and then the following line is added in the immediate window:

Call SendAuditCompletedRptErrorsNoAction

Then it goes to the above function and proceeds with the email processing (Function SendAuditCompletedRptErrorsNoAction()).

Then from the above Function the following Function is called:

Public Function SendEmailCompletedRptErrorsNoAction(ByVal varMgrEmail, varEmpEmail, varOE, varAddlEmail As String)

I continue to F8 through the  Public Function SendEmailCompletedRptErrorsNoAction(ByVal varMgrEmail, varEmpEmail, varOE, varAddlEmail As String), and then it goes back to the (Function SendAuditCompletedRptErrorsNoAction()), and end if, then it goes to the function Public Function SendEmailCompletedRptErrorsNoAction(ByVal varMgrEmail, varEmpEmail, varOE, varAddlEmail As String) and Exits, then it goes back to the Private Sub cboAuditStatus_AfterUpdate() to the End if in the following code:

                    'Audit Report Completed -- Contains Errors -- No Action Required
                    If (rst![Assoc] = "Y") Or (rst![Opers] = "Y") Or (rst![Impact] = "Y") Then
                        'Exit Do
                        Debug.Print "Call SendAuditCompletedRptErrorsNoAction"
                        Debug.Print
                        Call SendAuditCompletedRptErrorsNoAction
                        ' Audit Report Completed -- NO Errors -- No Action Required
                    Else
                        Debug.Print "Call SendAuditCompletedRptNoErrorsNoAction"
                        Debug.Print
                        'Call SendAuditCompletedRptNoErrorsNoAction
                    End If

Then it loops back to the code Do Until rst.EOF.

Then it goes to the ELSE and loops again, then it finally ends.

After it is all said and done with Record (9999_021220122050) , this is what is in the immediate window:

Call SendAuditCompletedRptNoErrorsNoAction

4.02 - Inquiry forwarded to correct queue or department. - Assoc-N Opers-N Impact-N
Call SendAuditCompletedRptNoErrorsNoAction

4.03 - Reason for transaction appropriately documented. - Assoc-N Opers-N Impact-N
Call SendAuditCompletedRptNoErrorsNoAction

4.04 - Followed guidelines for documentation. - Assoc-N Opers-N Impact-N
Call SendAuditCompletedRptNoErrorsNoAction

4.05 - Documented systems and resources used - Assoc-Y Opers-N Impact-N
4.01 - Inquiry attached to the correct member. - Assoc-N Opers-N Impact-N

Call SendAuditCompletedRptErrorsNoAction

7.03 - CMS HIPAA guidelines correctly applied and documented. - Assoc-N Opers-N Impact-N

Call SendAuditCompletedRptNoErrorsNoAction

16.01 - System updated correctly. - Assoc-N Opers-N Impact-N
Call SendAuditCompletedRptNoErrorsNoAction

16.02 - Completed timely. - Assoc-N Opers-N Impact-N
Call SendAuditCompletedRptNoErrorsNoAction

16.03 - Appropriate correspondence sent. - Assoc-N Opers-N Impact-N
Call SendAuditCompletedRptNoErrorsNoAction

21.01 - Correctly determined if COB response is complete - Assoc-N Opers-N Impact-N
Call SendAuditCompletedRptNoErrorsNoAction

21.02 - Correct action taken on COB record(s) - Assoc-N Opers-N Impact-N
Call SendAuditCompletedRptNoErrorsNoAction

21.03 - COB Source entered or updated correctly - Assoc-N Opers-N Impact-N
Call SendAuditCompletedRptNoErrorsNoAction

21.04 - REC type entered or updated correctly - Assoc-N Opers-N Impact-N
Call SendAuditCompletedRptNoErrorsNoAction

21.05 - MSP type entered or updated correctly - Assoc-N Opers-N Impact-N
Call SendAuditCompletedRptNoErrorsNoAction

21.06 - Policy # entered or updated correctly - Assoc-N Opers-N Impact-N
Call SendAuditCompletedRptNoErrorsNoAction

21.07 - Effective date entered or updated correctly - Assoc-N Opers-N Impact-N
Call SendAuditCompletedRptNoErrorsNoAction

21.08 - Term date entered or updated correctly - Assoc-N Opers-N Impact-N
Call SendAuditCompletedRptNoErrorsNoAction

21.09 - RX ID entered or updated correctly - Assoc-N Opers-N Impact-N
Call SendAuditCompletedRptNoErrorsNoAction

21.10 - RX Group # entered or updated correctly - Assoc-N Opers-N Impact-N
Call SendAuditCompletedRptNoErrorsNoAction

21.11 - RX Bin entered or updated correctly - Assoc-N Opers-N Impact-N
Call SendAuditCompletedRptNoErrorsNoAction

21.12 - RX PCN entered or updated correctly - Assoc-N Opers-N Impact-N
Call SendAuditCompletedRptNoErrorsNoAction

7.01 - Member contacted to obtain additional information. - Assoc-N Opers-N Impact-N
Call SendAuditCompletedRptNoErrorsNoAction

7.02 - Corporate VIA guidelines performed and documented. - Assoc-N Opers-N Impact-N
Call SendAuditCompletedRptNoErrorsNoAction


Thanks,
gdunn59

Of course I had the subs commented out, we are just checking this code aren't we?

It sounds like the 'problem' you have is something to do with the subs.

If I uncoment the calls I either end up with a whole load of emails in my Drafts folder or Outlook will have to be shut down because it freezes.

If we forget about the subs is the code for the after update working or not?

I don't think it is the subs (functions) because when I comment back in those lines, the loop stops after only looping once and then it wants to run the subs (functions), so the stopping of the loop is happening before it even starts running the function.

So when the subs are commented out the code works fine but when they are uncommented it doesn't?

So I reckon it's the subs causing the problem.

When the Functions (not subs) are commented in the entire process runs and there are no errors, but it does not go to the correct line of code when it is commented in.

Have you tried to replicate what I am talking about when the Functions are commented back in.  It won't actually try to email because I have a Save there and the .Send is commented out.

Could you please try it with the Functions commented back in and F8 through the code so you can't get an understanding of what I am seeing.  Its just sort of difficult to explan. I've tried my best to explain it, but not sure if you still understand what exactly is happening.

Thank you for hanging in there with me on this.

gdunn59

Maybe a better way to put it is when it is commented out, it loops through every row of the subform until it meets the criteria, but when it is commented back in, it loops once and then wants to run the Function (does the Call), when it should be looping through the entire Subform's recordset to determine which Call it should process.

Thanks,
gdunn59

Why don't you post the code where you've commented out and the code we're you've commented in?

All I did was comment back in the actual "Calls" (not referring to the debug.print code):

These two lines (lines 29 and 34 from your last code posting - ID: 37590795):

                        ' Call SendAuditCompletedRptErrorsNoAction
                        ' Call SendAuditCompletedRptNoErrorsNoAction

Thanks,
gdunn59

I've just done that and I now have 100 plus draft emails in Outlook.

They all seem to have the correct subject etc

So it seems to be working.

Exactly, that is what I stated in one of my other posts is that it keeps looping and creating an email for every Quality Review Criteria line (ie 4.01 - 21.12) of the subform.

So my question, when the Functions are commented in, shouldn't the code loop through the subform's recordset all at once and then move to the line that says to run the Function?

It seems to me that the loop isn't working properly, not the functions.

Thanks,
gdunn59

Are you saying there should only be one email every time the code has run

If that's the case the code to call the subs should not be in the loops.

In fact I don't think you even need a loop.

All you need to do is check the whole if there is a Y anywhere to see if there are errors, then based on that and the Action value send the appropriate email.

By the way, I did ask you about this early but perhaps didn't explain what I meant.

Anyway those calls should never have been in the loops in the first place.

Don't think I explained that first bit very well.

There should only be one email for each record in the main form regardless of how many records there are in the subform.

Yes, you are correct.  Once it checks for "Y" in the subform, if it finds one or more "Y" and it is marked COMPLETED and Action Required = "YES", then it should run the code for "COMPLETED -- ERRORS -- ACTION REQUIRED", and then send out ONE email with the attached Audit Report for "Completed -- Contains Errors -- Action Required".

If it finds one or more "Y" in the subform and it is also marked COMPLETED but the Action Required = NO, then it should run the code for "COMPLETED -- ERRORS - NO ACTION REQUIRED", and then send out ONE email with the attached Audit report for "Completed -- Contains Errors -- No Action Required".

If it doesn't find any "Y" (just has all "N" or "N/A") in the subform and it is also marked COMPLETED and the Action Required = NO, then it should run the code for "COMPLETED -- NO ERRORS -- NO ACTION REQUIRED", and then send out ONE email with the attached Audit Report for "Completed -- No Errors -- No Action Required".

When I say send out ONE email with the attached Audit Report, it basically sends out a report that contains the information on the main/subform for the current record on the screen.

Thanks,
gdunn59

I think that can be done without looping - I'll check it out and get back to you.

Ok.  Thanks!

Still waiting for a solution on this.

Can someone please HELP!!!!

Thanks,
gdunn59

Well, I think I've got one.

If you give me a couple of minutes I'll post it.

Here you are:

Private Sub cboAuditStatus_AfterUpdate()
Dim strSQL As String
Dim cntAssoc As Long
Dim cntOpers As Long
Dim cntImpact As Long
Dim rtn As Variant

    cntAssoc = DCount("Assoc", "tblEmployee_Quality_Review_Info", "InquiryID ='" & Me.InquiryNum & "' And Assoc='Y'")
    cntOpers = DCount("Opers", "tblEmployee_Quality_Review_Info", "InquiryID ='" & Me.InquiryNum & "' And Opers='Y'")
    cntImpact = DCount("Impact", "tblEmployee_Quality_Review_Info", "InquiryID ='" & Me.InquiryNum & "' And Impact='Y'")


    If Me.cboAuditStatus = "Completed" Then
            rtn = MsgBox("You have selected Completed as the Audit Status. Is this correct?", vbYesNo)
            
            If rtn <> vbYes Then Exit Sub
            
            Select Case Me.Action_Required
                Case "No"
                        If cntAssoc > 0 Or cntOpers > 0 Or cntImpact > 0 Then
                    
                            Call SendAuditCompletedRptErrorsNoAction
                        Else
                            Call SendAuditCompletedRptNoErrorsNoAction
                        End If
                Case "Yes"
                        If cntAssoc > 0 Or cntOpers > 0 Or cntImpact > 0 Then
                            SendAuditCompletedRptActionReqWithErrors
                        Else
                            Call SendAuditCompletedRptNoErrorsNoAction
                        End If                                       
            End Select
    End If
End Sub

Select allOpen in new window

Thank you.  I will give it a shot and let you know.

Much appreciated,
gdunn59

BINGO -- It appears to be working!!!!!!!!!!!!  OMG - You just don't know how excited I am right now - LOL.

I will continue to test it (to make SURE this time -- that it is working completely), and let you know either later this afternoon/night, or tomorrow.

Thank you -- Thank you -- Thank you (I can't say it enough).

gdunn59

Ok.  After doing much testing, it does appear to be working.  The only thing is there are a couple of things that I had it doing before based on the message box for the cboAuditStatus.

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

Also, would like if the user's response to rtn is vbYes, then also change the forecolor to vbRed, else if user's response to rtn is vbNo, then not only do the following (but also change the forecolor to green, and then go to a new record and set the focus to the InquiryNum field):

THIS CODE IS FURTHER DOWN (for if rtn = vbNo):

  else ' if rtn = vbNo
      Me.Completed_Date = ""
      Me.Completed_Date.Requery
      Me.cboAuditStatus.SetFocus
      Me.cboAuditStatus = "Draft"

I'm afraid to attempt inserting this code where necessary because I'm afraid I will mess up the code that you gave me (that replaced the loop code).  

This code was included with the loop code that I initially posted.

Can you assist with this?

Thanks,
gdunn59

I have this code for moving to a new record and setting focus to the InquiryNum field, so I am not sure if I need it in the After_Update code mentioned in my last posting, or if it should be removed from here and included in the After_Update Code listed in my last posting.

Private Sub cboAuditStatus_Exit(Cancel As Integer)

DoCmd.GoToRecord , , acNewRec
Me.InquiryNum.SetFocus

End Sub

Sorry, but can't you do that yourself?

I've answered, or at least found a solution, for the original problem.

I know some of the code that was there originally isn't there now but I wonder if you actually need all of that code?

For example, why are you requerying after you've put the date in?

Also, why do you want to go immediately to a new record on leaving particular fields?

And the set focus stuff?

I'd be changing something simple on the form, then get automatically transferred to a new record or a completely unrelated field.

So then I would have to go back and repeat what I was doing.

I eventually took most of that code out as it was so annoying.

For new records could you not have a button that allows the user to goto a new record when they want.

If I get a chance I'll put the code back in but you should think about what's actually needed and not just for this part of the code.

PS Why do you keep on turning Echo on/off? You aren't doing anything that I would think that would make any difference to.

The Audit Status field is the very last field that the user enters, so regarding your comment . . . "I'd be changing something simple on the form, then get automatically transferred to a new record or a completely unrelated field.", it wouldn't matter since that is the last field.  This is the way the people I'm designing this for want it to be.

Also, as far as Echo . . . someone in the past told me I should always put that in.

I do have an "Add New Record" button. In regards to:

 else ' if rtn = vbNo
      Me.Completed_Date = ""
      Me.Completed_Date.Requery"

I was told by someone on EE to requery because the Completed Date changed.

I attempted to add the code back in last night, and it worked, but then the Calls weren't working.  Maybe I was just tired.  I will give it a shot again today.

Thanks,
gdunn59

I'll have a look at it later, it should be straightforward to slot the code in.

I still don't quite understand why you want to move to a new record as soon as the last field has been filled in/exited.

What if the user wants to review their input before committing it?

If you do have a new record button why not add an accelarator button to it.

Then the user would only need to trigger that to move to a new record.

As I mentioned, I'm not the one that wants it to automatically move to a new record.  Also the user can click the Add New Record button if they want to move to a new record.

I attempted to insert the code again, but apparently don't have it inserted correctly because it is not doing the Calls.

Thanks,
gdunn59

Ok.  I thought that is how I did, but I will paste in and see what happens.

Thanks much!
gdunn59

Don't thank me yet, I think adding that code in might have stopped things working properly.

Yeah because I think I had it like you did, and it wasn't getting to the line of code with the Calls.

Well I'm thanking you for the quick turnaround and for being so patient with me on this issue.

I will let you know.

Thanks,
gdunn

Ok. I tested.

The two with NO Action Required works (runs the Call Functions):

COMPLETED -- NO ERRORS -- NO ACTION REQUIRED
COMPLETED -- ERRORS -- NO ACTION REQUIRED

But the one with Action Required is not working (does not run the Call Functions):

COMPLETED -- ERRORS -- ACTION REQUIRED

Thanks,
gdunn59

Could you please check if it actually runs the call or not?

Because when I step through the code it definitely get's called.

Doesn't seem to create an email though, but it does get called.

Just stepped through SendAuditCompletedRptActionRequiredErrors and it goes straight through to DoCmd.CancelEvent skipping SendEmailCompletedRptActionReqWithErrors which is the code that actually creates the email.

So it is being called but it's not working, which means the code I posted works and you have a problem somewhere else.

Ok.  My apologies.  It is working.  I forgot that the emails are generated based off of what Manager and Dept are chosen, and the Manager I had chosen I didn't have the correct Dept or Region chosen.

So the way you have the code is exactly the way I had initially did it last night but because it wasn't generating the emails I thought it wasn't correct.  I should have stepped through the code.

I changed the line of code "me.requery" to "me.completed_date.requery" because I get the following error:

  The object you referenced in the Visual Basic Procedure as an OLE object isn't an OLE
  object.

I would like to test more and more and more, just to make sure everything is really working properly before I close out.

Sorry for bothering you about this.  Valuable lesson learned -- if I would have just stepped through the code that I inserted last night I would have realized that it was actually processing the Call Function just wasn't producing the email because of the Manager/Dept/Region criteria that I had for the record.

Thanks,
gdunn59