Page 1 of 3 123 LastLast
Results 1 to 15 of 36
  1. #1
    onlylonely is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    May 2017
    Posts
    110

    Question Loop issue

    Hi guys,



    I'm having issue on this code.
    What i look for is when status = fail , it will automatically send me an "FAIL Email" or when status is PASS more than 3 times it will send me an "PASS Email as well.

    Problems i'm facing:
    When the status is 3x pass, i will receive "FAIL EMAILS" as well and it will not pop out the msgbox saying that the part has 3 times passed. . Not sure what's going wrong.

    Your help is highly appreciated.


    Code:
    With rst
    
    Do Until .EOF
    
    
    If (![Status] = "Fail") Then
    
    
    Call Email3
    
    
    .MoveLast
    
    
    End If
    
    
    If (![Status] = "Pass") Then
    PassCount = PassCount + 1
    Else
    PassCount = 0
    End If
    .MoveNext
    Loop
    End With
    
    
    If (PassCount >= 3) Then
    MsgBox ("Product Number: " & PartNumber & " " & cmbID & " has more than 3 Pass!")
    
    
    Call Update
    Call Email2
    
    
    End If

  2. #2
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Not a lot of info to work with........

    What does the data look like?
    What is the SQL for the recordset rst?

    Maybe post your dB with a few records to illustrate the problem?

  3. #3
    onlylonely is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    May 2017
    Posts
    110
    Quote Originally Posted by ssanfu View Post
    Not a lot of info to work with........

    What does the data look like?
    What is the SQL for the recordset rst?

    Maybe post your dB with a few records to illustrate the problem?
    Hi Ssanfu,

    Thanks for your prompt reply.

    Code:
    Function CheckPass()
    
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSQL As String
    
    
    strSQL = "SELECT * FROM Inspection where PartNumber = '" & Me.PartNumber & "' AND IssueID = '" & Me.cmbID & "'"
    
    
    Set dbs = CurrentDb
    
    
    MsgBox (Me.PartNumber)
    
    
    Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
    
    
    rst.MoveLast
    
    
    Dim PassCount As Integer
    PassCount = 0
    
    
    rst.MoveFirst
    
    
    With rst
    
    
    Do Until .EOF
    
    
    If (![Status] = "Fail") Then
    
    
    Call Email3
    
    
    .MoveLast
    
    
    End If
    
    
    If (![Status] = "Pass") Then
    PassCount = PassCount + 1
    Else
    PassCount = 0
    End If
    .MoveNext
    Loop
    End With
    
    
    If (PassCount >= 3) Then
    MsgBox ("Product Number: " & PartNumber & " " & cmbID & " has more than 3 Pass!")
    
    
    Call Update
    Call Email2
    
    
    End If
    
    
    
    
    End Function

  4. #4
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    With the exception of not closing the record set and destroying the objects, I don't see anything wrong with the code.

    I have no idea of what your data looks like, so would need to be able to see the record set (data) to be able to step through the code to see what is happening.

    And this is what I meant about not closing the RS:
    Code:
     <snip>
        If (PassCount >= 3) Then
            MsgBox ("Product Number: " & PartNumber & " " & cmbID & " has more than 3 Pass!")
            Call Update
            Call Email2
        End If
    
        rst.Close
        Set rst = Nothing
        Set dbs = Nothing
    End Function

  5. #5
    onlylonely is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    May 2017
    Posts
    110
    Quote Originally Posted by ssanfu View Post
    With the exception of not closing the record set and destroying the objects, I don't see anything wrong with the code.

    I have no idea of what your data looks like, so would need to be able to see the record set (data) to be able to step through the code to see what is happening.

    And this is what I meant about not closing the RS:
    Code:
     <snip>
        If (PassCount >= 3) Then
            MsgBox ("Product Number: " & PartNumber & " " & cmbID & " has more than 3 Pass!")
            Call Update
            Call Email2
        End If
    
        rst.Close
        Set rst = Nothing
        Set dbs = Nothing
    End Function
    Hi Ssanfu,

    I think the problems is from

    Code:
    If (![Status] = "Fail") Then
    
    
    Call Email3
    
    
    .MoveLast
    
    
    End If
    Click image for larger version. 

Name:	Partnumber.JPG 
Views:	36 
Size:	49.1 KB 
ID:	29374

    When i remove this code. My code works well.

  6. #6
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    In your code, you have
    Code:
        strSQL = "SELECT * FROM Inspection where PartNumber = '" & Me.PartNumber & "' AND IssueID = '" & Me.cmbID & "'"
    What is Me.cmbID? I know it is a combo box. What is the row source, or give me a number I can use to replicate your problem.

    Me.PartNumber will be 555
    Me.cmbID wil be ???????
    EDIT: I finally started reading and I see that cboID is IssueID.

    And in the record set, do you have an order for the records? What happens if all of the "Fails" are at the beginning of the recordset? Is there a date field?

  7. #7
    onlylonely is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    May 2017
    Posts
    110
    Quote Originally Posted by ssanfu View Post
    In your code, you have
    Code:
        strSQL = "SELECT * FROM Inspection where PartNumber = '" & Me.PartNumber & "' AND IssueID = '" & Me.cmbID & "'"
    What is Me.cmbID? I know it is a combo box. What is the row source, or give me a number I can use to replicate your problem.

    Me.PartNumber will be 555
    Me.cmbID wil be ???????
    EDIT: I finally started reading and I see that cboID is IssueID.

    And in the record set, do you have an order for the records? What happens if all of the "Fails" are at the beginning of the recordset? Is there a date field?
    Hi Ssanfu,

    This is my Issues table. If passed more than 3 times, it will turn to "CLOSE"
    The problem i'm having now is how to write a code for "FAIL" and send an email.
    You are right, what if i'm fail at beginning? < It will not "Close" and need to continue inspect.

    Click image for larger version. 

Name:	Issues.JPG 
Views:	37 
Size:	32.2 KB 
ID:	29375

  8. #8
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    So here is what I see happening:
    Me.PartNumber = 555
    Me.cboID = 11
    (Question: why are the numbers in a text type field instead of a number type field???)

    Opening the record set has the records like the image in Post #5.
    The first record has a status of "Fail".
    (Status = Fail) is TRUE
    So Email3 is called
    Next the bookmark (current record) is moved to the last record in the record set "rst".
    Status is read again, but since the current record is the LAST record and here Status = TRUE, the counter PassCount is incremented.
    Now MoveNext in the recordset
    Since we were at the last record, moving next puts us at the EOF, so exit the loop.
    PassCount = 1, so do not display the message box.
    Exit the function......





    I re-arranged & modified the lines of code.
    This is how I would have the lines for ease of reading:
    The code checks every record. Note that since there is NO order to the records in the record set, the results can vary. If there is a date field where the part passed or failed, I would order by that field.
    Code:
    Function CheckPass()
    
        Dim dbs As DAO.Database
        Dim rst As DAO.Recordset
        Dim strSQL As String
        Dim PassCount As Integer
    
        Set dbs = CurrentDb
    
        strSQL = "SELECT *"
        strSQL = strSQL & " FROM Inspection"
        strSQL = strSQL & " WHERE PartNumber = '" & Me.PartNumber & "' AND IssueID = '" & Me.cmbID & "'"
        '    Debug.Print strSQL
        MsgBox (Me.PartNumber)
    
        Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
        If Not rst.BOF And Not rst.EOF Then   'are there records??
            rst.MoveLast
            rst.MoveFirst
    
            PassCount = 0
    
            With rst
                Do Until .EOF
                    Select Case ![Status]
                        Case "Fail"
                            Call Email3
                            PassCount = 0
                        Case "Pass"
                            PassCount = PassCount + 1
                    End Select
    
                    .MoveNext
                Loop
            End With
        End If
    
        If (PassCount >= 3) Then
            MsgBox ("Product Number: " & PartNumber & " " & cmbID & " has more than 3 Pass!")
            Call Update
            Call Email2
        End If
    
        rst.Close
        Set rst = Nothing
        Set dbs = Nothing
    End Function

  9. #9
    onlylonely is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    May 2017
    Posts
    110
    Quote Originally Posted by ssanfu View Post
    So here is what I see happening:
    Me.PartNumber = 555
    Me.cboID = 11
    (Question: why are the numbers in a text type field instead of a number type field???)

    Opening the record set has the records like the image in Post #5.
    The first record has a status of "Fail".
    (Status = Fail) is TRUE
    So Email3 is called
    Next the bookmark (current record) is moved to the last record in the record set "rst".
    Status is read again, but since the current record is the LAST record and here Status = TRUE, the counter PassCount is incremented.
    Now MoveNext in the recordset
    Since we were at the last record, moving next puts us at the EOF, so exit the loop.
    PassCount = 1, so do not display the message box.
    Exit the function......





    I re-arranged & modified the lines of code.
    This is how I would have the lines for ease of reading:
    The code checks every record. Note that since there is NO order to the records in the record set, the results can vary. If there is a date field where the part passed or failed, I would order by that field.
    Code:
    Function CheckPass()
    
        Dim dbs As DAO.Database
        Dim rst As DAO.Recordset
        Dim strSQL As String
        Dim PassCount As Integer
    
        Set dbs = CurrentDb
    
        strSQL = "SELECT *"
        strSQL = strSQL & " FROM Inspection"
        strSQL = strSQL & " WHERE PartNumber = '" & Me.PartNumber & "' AND IssueID = '" & Me.cmbID & "'"
        '    Debug.Print strSQL
        MsgBox (Me.PartNumber)
    
        Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
        If Not rst.BOF And Not rst.EOF Then   'are there records??
            rst.MoveLast
            rst.MoveFirst
    
            PassCount = 0
    
            With rst
                Do Until .EOF
                    Select Case ![Status]
                        Case "Fail"
                            Call Email3
                            PassCount = 0
                        Case "Pass"
                            PassCount = PassCount + 1
                    End Select
    
                    .MoveNext
                Loop
            End With
        End If
    
        If (PassCount >= 3) Then
            MsgBox ("Product Number: " & PartNumber & " " & cmbID & " has more than 3 Pass!")
            Call Update
            Call Email2
        End If
    
        rst.Close
        Set rst = Nothing
        Set dbs = Nothing
    End Function
    Hi Ssanfu,

    I have tried your code. When the status is FAIL or PASS. It will send me "FAIL" EMails.
    From the beginning fail to the last fail. I'm like getting 20 "FAIL emails"

    EDIT: Should i remove this?

    .MoveNext
    Loop
    End With
    End If

  10. #10
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You have not been specific on what you want to happen.

    Given the data set in the image in Post #7, what do you want to happen where the Status = Fail?
    Exit the code?

    There are 15 records - when should the FAIL email be sent?

  11. #11
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Is the order of the records in the records set important? For instance, if I changed the SQL string to
    Code:
        strSQL = "SELECT *"
        strSQL = strSQL & " FROM Inspection"
        strSQL = strSQL & " WHERE PartNumber = '" & Me.PartNumber & "' AND IssueID = '" & Me.cmbID & "'"
        strSQL = strSQL & "ORDER BY Status Desc"
    All of the PASSes would be at the beginning of the record set and the FAILS at the end. Is that OK?

    Do you have date field that the record set could be sorted by?

    A table has no inherent order. It is just a bit bucket. The order a record is entered is not necessarily the order it will be displayed without a sorting order.
    Setting a sort order is important!


    Looking at the data set in Post #7, should you get a fail email or not?

    Given the order below
    Code:
    Fail
    Pass
    Pass
    Pass
    Fail
    Should you get a Pass or fail email?


    Given the order below
    Code:
    Fail
    Pass
    Pass
    Pass
    Fail
    Pass
    Pass
    Should you get a Pass or fail email?

  12. #12
    onlylonely is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    May 2017
    Posts
    110
    Quote Originally Posted by ssanfu View Post
    Is the order of the records in the records set important? For instance, if I changed the SQL string to
    Code:
        strSQL = "SELECT *"
        strSQL = strSQL & " FROM Inspection"
        strSQL = strSQL & " WHERE PartNumber = '" & Me.PartNumber & "' AND IssueID = '" & Me.cmbID & "'"
        strSQL = strSQL & "ORDER BY Status Desc"
    All of the PASSes would be at the beginning of the record set and the FAILS at the end. Is that OK?

    Do you have date field that the record set could be sorted by?

    A table has no inherent order. It is just a bit bucket. The order a record is entered is not necessarily the order it will be displayed without a sorting order.
    Setting a sort order is important!


    Looking at the data set in Post #7, should you get a fail email or not?

    Given the order below
    Code:
    Fail
    Pass
    Pass
    Pass
    Fail
    Should you get a Pass or fail email?


    Given the order below
    Code:
    Fail
    Pass
    Pass
    Pass
    Fail
    Pass
    Pass
    Should you get a Pass or fail email?
    Hi Steve,

    My apologize because i'm not expert in this programming. I'm learning from youtube and everywhere.
    What i wish to get is the result from the latest records.

    If FAIL - send me email.
    If Pass 3x - send me email.

    If Pass - Pass - FAIL , send me FAIL email.
    If Pass - Fail , send me FAIL email.
    If Pass - Fail - Pass , do not need to send me email as it already send me earlier.


    The latest records is the important.

  13. #13
    onlylonely is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    May 2017
    Posts
    110
    Quote Originally Posted by ssanfu View Post
    Is the order of the records in the records set important? For instance, if I changed the SQL string to
    Code:
        strSQL = "SELECT *"
        strSQL = strSQL & " FROM Inspection"
        strSQL = strSQL & " WHERE PartNumber = '" & Me.PartNumber & "' AND IssueID = '" & Me.cmbID & "'"
        strSQL = strSQL & "ORDER BY Status Desc"
    All of the PASSes would be at the beginning of the record set and the FAILS at the end. Is that OK?

    Do you have date field that the record set could be sorted by?

    A table has no inherent order. It is just a bit bucket. The order a record is entered is not necessarily the order it will be displayed without a sorting order.
    Setting a sort order is important!


    Looking at the data set in Post #7, should you get a fail email or not?

    Given the order below
    Code:
    Fail
    Pass
    Pass
    Pass
    Fail
    Should you get a Pass or fail email?


    Given the order below
    Code:
    Fail
    Pass
    Pass
    Pass
    Fail
    Pass
    Pass
    Should you get a Pass or fail email?
    Good question !!!
    If 3 times Pass - then we no longer need to worried that portion as i have "CLOSE" the portion , no longer need to inspect.

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


    Question: Do you have date field that the record set could be sorted by?




    Question: Given the order below
    Fail
    Pass
    Pass
    Pass

    Fail
    Should you get a Pass or fail email?



    Question: Given the order below
    Fail
    Pass
    Pass
    Pass

    Fail
    Pass
    Pass

    Should you get a Pass or fail email?


    Since I am guessing at what should happen,
    maybe try this:
    Code:
            With rst
                Do Until .EOF
                    Select Case ![Status]
                        Case "Fail"
                            PassCount = 0
                        Case "Pass"
                            PassCount = PassCount + 1
                    End Select
    
                    .MoveNext
                Loop
            End With
        End If
    
        If (PassCount >= 3) Then
            MsgBox ("Product Number: " & PartNumber & " " & cmbID & " has more than 3 Pass!")
            Call Update
            Call Email2
        Else
            Call Email3
        End If

  15. #15
    onlylonely is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    May 2017
    Posts
    110
    Quote Originally Posted by ssanfu View Post


    Question: Do you have date field that the record set could be sorted by?
    I can sort it with ID - automatically generated ID.




    Question: Given the order below
    Fail >> Send me email
    Pass >> No send (count 1)
    Pass
    >> No send (count 2)
    Pass
    >> Send me email (Update table - post 7 to "Close")
    Fail >> After 3 times pass, need not to check again. (It wont happen - eliminate this)
    Should you get a Pass or fail email?



    Question: Given the order below
    Fail >> Send me email
    Pass >> No send (count 1)
    Pass
    >> No send (count 2)
    Pass
    >> Send me email "Pass" , (Update table - post 7 to "Close")
    Fail >> No checking needed. It wont show up anymore after 3 times pass
    Pass >> No checking needed. It wont show up anymore after 3 times pass
    Pass
    >> No checking needed. It wont show up anymore after 3 times pass
    Should you get a Pass or fail email?


    Since I am guessing at what should happen,
    maybe try this:
    Code:
            With rst
                Do Until .EOF
                    Select Case ![Status]
                        Case "Fail"
                            PassCount = 0
                        Case "Pass"
                            PassCount = PassCount + 1
                    End Select
    
                    .MoveNext
                Loop
            End With
        End If
    
        If (PassCount >= 3) Then
            MsgBox ("Product Number: " & PartNumber & " " & cmbID & " has more than 3 Pass!")
            Call Update
            Call Email2
        Else
            Call Email3
        End If
    I have use this code. When status = 1 time pass , it will send me Email3.
    If status = fail, it will send me Email3 as well. Not sure why it happens.

Page 1 of 3 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 13
    Last Post: 06-06-2017, 01:43 PM
  2. Replies: 9
    Last Post: 03-07-2017, 02:49 PM
  3. Replies: 12
    Last Post: 06-05-2015, 04:27 PM
  4. Loop issue
    By dragon_sun252 in forum Programming
    Replies: 25
    Last Post: 04-29-2012, 12:27 AM
  5. Issue with while loop and Access 2007 datasheet
    By jermaine123 in forum Programming
    Replies: 2
    Last Post: 01-17-2010, 10:09 AM

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