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