Loop issue

[onlylonely]

Hi guy,

Anyone know what's the code for reading the last records?
I think my problem is (Status = "Fail") then the program will screen from beginning to the last records.


But what i want is the program only screen the last records.

Any idea?

[ssanfu]

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

An autonumber field is only guaranteed to be unique, not sequential.
You don't have a field for date of Pass/Fail?

Maybe try this:

Function CheckPass()

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSQL As String
    Dim PassCount As Integer
    Dim FailSent As Boolean   ' flag for fail email has been sent?

    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
        FailSent = False

        With rst
            Do Until .EOF
                Select Case ![Status]
                    Case "Fail"
                        If Not FailSent Then      'if FailSent = False
                            '    MsgBox ("Fail")  '<<-- for testing
                            Call Email3           'send Fail email
                            FailSent = True
                        End If
                        PassCount = 0
                    Case "Pass"
                        PassCount = PassCount + 1
                        If PassCount = 3 Then
                            MsgBox ("Product Number: " & PartNumber & " " & cmbID & " has at least 3 Pass!")
                            Call Update
                            Call Email2
                            Exit Do   'pass 3 times in a row. No need to check rest
                        End If
                End Select

                .MoveNext
            Loop
        End With
    End If


    'Clean up
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing
End Function

But what i want is the program only screen the last records.

Depends on what you mean by "Last records". Last 3 records, last 5 records, ...?
If you mean from the last record in the data set to the beginning record, you could try code like this:
(this is just an outline)

   'declarations here

    Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
    If Not rst.BOF And Not rst.EOF Then   'are there records??
        rst.MoveLast
        With rst
            Do Until .BOF

                ' some code
                'more code
                .MovePrevious
            Loop
        End With
    End If

Without a field for "Date of Pass/Fail" or a Test number field (Test 1, Test 2,...) to set an order (sort), I would be worried about the order of tests being out of order.

But maybe I misunderstood. I thought that there had to be 3 Pass tests in a row (sequentially) to send the Pass email.
If that is not the case, couldn't you get just the records where [Status] = Pass and count them? If 3 or more Pass, then send Pass email?

[onlylonely]

[QUOTE=ssanfu;363984]An autonumber field is only guaranteed to be unique, not sequential.
You don't have a field for date of Pass/Fail?

Maybe try this:

Function CheckPass()

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSQL As String
    Dim PassCount As Integer
    Dim FailSent As Boolean   ' flag for fail email has been sent?

    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
        FailSent = False

        With rst
            Do Until .EOF
                Select Case ![Status]
                    Case "Fail"
                        If Not FailSent Then      'if FailSent = False
                            '    MsgBox ("Fail")  '<<-- for testing
                            Call Email3           'send Fail email
                            FailSent = True
                        End If
                        PassCount = 0
                    Case "Pass"
                        PassCount = PassCount + 1
                        If PassCount = 3 Then
                            MsgBox ("Product Number: " & PartNumber & " " & cmbID & " has at least 3 Pass!")
                            Call Update
                            Call Email2
                            Exit Do   'pass 3 times in a row. No need to check rest
                        End If
                End Select

                .MoveNext
            Loop
        End With
    End If


    'Clean up
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing
End Function

Hi Ssanfu,

Thank for the code. But when i the status is fail. Yes, it sends me "Fail Email".
But then the 2nd records i put pass, it also send me FAIL email.
Not sure what is going on.

[onlylonely]

Hi Steve,

Please refer to below attachment.

[ssanfu]

I created a test dB (attached) and added a date field so I could sort the records and keep them in the correct order. The records in the table are from your post above.
You can open the form "frmConditions" to add/edit/delete records or statuses for testing.

The actual code for you is:

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"
                        '        MsgBox ("Fail")    '<<-- for testing
                        Call Email3        'send Fail email
                        PassCount = 0
                    Case "Pass"
                        PassCount = PassCount + 1
                        If PassCount = 3 Then
                            MsgBox ("Product Number: " & PartNumber & " " & cmbID & " has at least 3 Pass!")
                            Call Update
                            Call Email2
                            Exit Do   'pass 3 times in a row. no need to check rest
                        End If
                End Select

                .MoveNext
            Loop
        End With
    End If


    'Clean up
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing
End Function

[onlylonely]

I created a test dB (attached) and added a date field so I could sort the records and keep them in the correct order. The records in the table are from your post above.
You can open the form "frmConditions" to add/edit/delete records or statuses for testing.

The actual code for you is:

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"
                        '        MsgBox ("Fail")    '<<-- for testing
                        Call Email3        'send Fail email
                        PassCount = 0
                    Case "Pass"
                        PassCount = PassCount + 1
                        If PassCount = 3 Then
                            MsgBox ("Product Number: " & PartNumber & " " & cmbID & " has at least 3 Pass!")
                            Call Update
                            Call Email2
                            Exit Do   'pass 3 times in a row. no need to check rest
                        End If
                End Select

                .MoveNext
            Loop
        End With
    End If


    'Clean up
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing
End Function

Hi Steve,

Now only i know i can attach the file. I've attach my file. Maybe you can take a look?

[ssanfu]

OK, now I have a little better understanding, but more questions. I now see why there might be issues with the code.

Please explain what (not how) you are wanting to do with the database.

Something like:
"We use many parts putting together widgets. Every widget gets inspected. We want to track the inspection issues."



So how would you do what you want to do manually?
Are the partnumbers and Issues entered into table "IssuesTbl" first, then inspections are performed and inspection data entered into table "Inspection"?

[orange]

I just did a quick look at the database provided by onlylonely and note the following:

there seems to be tables missing?????

parts
inspections
issues
inspector/inspectedBy
acceptedCodes

There isn't much info on the business so my comments may be way off.

Seems generally there would be

Part ---Inspection--- Status --- Issue(if failure)

need more info on the business/processes involved.

[onlylonely]

OK, now I have a little better understanding, but more questions. I now see why there might be issues with the code.

Please explain what (not how) you are wanting to do with the database.

Something like:
"We use many parts putting together widgets. Every widget gets inspected. We want to track the inspection issues."



So how would you do what you want to do manually?
Are the partnumbers and Issues entered into table "IssuesTbl" first, then inspections are performed and inspection data entered into table "Inspection"?

Hi Steve,

This database is use for record 100% inspection parts.
Whenever 100% inspection parts, inspector shall go to "Inspection Form" to key in the relevant information.
Human being tends to forget, with this database and the link, will let them easily find out what is the previous issue and why need to perform 100% inspection.
If more than 3 times passed. Then i will remove it from 100% inspection else, they need to continue to perform 100% inspection.

For inspector, they are not allowed to edit and view the tables. I will hide the tables.
Inspector only allow to use the form.

Now my issue is , once FAIL then send email to me.
This is the last step. But i FAIL to do it.

[onlylonely]

I just did a quick look at the database provided by onlylonely and note the following:

there seems to be tables missing?????

parts
inspections
issues
inspector/inspectedBy
acceptedCodes

There isn't much info on the business so my comments may be way off.

Seems generally there would be

Part ---Inspection--- Status --- Issue(if failure)

need more info on the business/processes involved.

Hi Moderator,

Yes, will definitely input inspector/inspectedBy into it.
But now the most important things is add the "FAIL" code.

[onlylonely]

Anyone can help? Keep trying but failed

[ssanfu]

OK, lets try this another way.

Lets say there is a part number "Wiper409". I am guessing there is a form to be able to enter data into "IssuesTbl" table.
So "Wiper409" is entered into the field "PartNumber".
There is a memo field "Issues". What typically gets entered into this field?
At this point, I think the field "Complete" has "Open" entered.
(I'm going to ignore the fiel "Link" at this point)


At some point, inspections are performed on the part. These inspections are entered into the table "Inspection" using the form "InspectionF".

Looking at the code, when you click the ADD button, the code calls the function "CheckPass".
In table "IssuesTbl", there is a part number "888". In table "Inspection", there are 10 records for partnumber "888". Does this mean the function "CheckPass" will/should run after EACH inspection record is entered into table "Inspection"???


Are the inspections done on the same day?
Will every record for partnumber "888" in table "Inspection" be checked every time a new inspection record is entered into table "Inspection"??
For example, if a record for partnumber "888" was entered for 15 consecutive days, would all of the previous records be used when executing the "CheckPass" code?

[onlylonely]

OK, lets try this another way.

Lets say there is a part number "Wiper409". I am guessing there is a form to be able to enter data into "IssuesTbl" table. "YES, there is a form able enter data into "IssuesTbl"
So "Wiper409" is entered into the field "PartNumber".
There is a memo field "Issues". What typically gets entered into this field? "Is actually what quality issue is having on this part number, dimensional issue or cosmetic issue"
At this point, I think the field "Complete" has "Open" entered. "I've set it auto, if i enter a new issue , it will automatically become "OPEN" at this column"
(I'm going to ignore the fiel "Link" at this point)


At some point, inspections are performed on the part. These inspections are entered into the table "Inspection" using the form "InspectionF".

Looking at the code, when you click the ADD button, the code calls the function "CheckPass".
In table "IssuesTbl", there is a part number "888". In table "Inspection", there are 10 records for partnumber "888". Does this mean the function "CheckPass" will/should run after EACH inspection record is entered into table "Inspection"??? Same part number may have different issue, i've differentiate them in "ID" and my form need them to choose the "ID". Check pass is only check them for the same ID. EXAMPLE If 888 "ID 2" is more pass more than 3 times consecutive, then send me email. If fail 1 time at the latest result also send me 1 email. The problems i'm facing is... If Pass - FAIL - PASS - FAIL (latest result) , it will send me 2 times fail email.
Or if the result is FAIL - PASS (latest result) , not sure why it will send me one FAIL email as well.

Are the inspections done on the same day? (Sometimes not)
Will every record for partnumber "888" in table "Inspection" be checked every time a new inspection record is entered into table "Inspection"?? (No)
For example, if a record for partnumber "888" was entered for 15 consecutive days, would all of the previous records be used when executing the "CheckPass" code?
If the record PartNumber 888 with "ID 2" x 3 consecutive pass. It will need not to inspect again. But if pass - fail - pass - fail ...then it needs to be inspect.

Hi Steve,

Thank you for your help. But my problem still not solve yet. I've answered your question in blue text color.

[onlylonely]

Hi Steve,

I think this code works but only 1 thing is if Pass - Fail - Pass (Latest result) , it will still send me FAIL email. I want it not to send me the FAIL email when the latest result is "Pass" . Any idea?

With rst

Do Until .EOF


If (![Status] = "Pass") Then
PassCount = PassCount + 1
Else
.MoveLast
PassCount = 0
Call Email3           '<< If pass - fail - pass, it still send me an fail email. How to solve this?


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

[ssanfu]

I am TOTALLY lost!
In Post #19 you show what you want to happen - there are 7 examples.
Now you change and say

I think this code works but only 1 thing is if Pass - Fail - Pass (Latest result) , it will still send me FAIL email. I want it not to send me the FAIL email when the latest result is "Pass"

But in the examples 3, 4 & 5, you show Fail Email even when the last Status is "Pass"!!!


Do you want to use/check only the last 3 or 10 (if there are that many inspections)??
But again, your table "Inspection" has no field to sort by other than the autonumber field, which is an extremely poor choice. You need a Test or Inspection date/time, an Inspection number (like an invoice number),... something!

The purpose of an AutoNumber field is to provide a unique value that serves no other purpose than to make each record unique. It is only guaranteed to unique, NOT sequential, nor increasing positive (meaning it can go negative).