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?
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?
An autonumber field is only guaranteed to be unique, not sequential.Question: Do you have date field that the record set could be sorted by?
I can sort it with ID - automatically generated ID.
You don't have a field for date of Pass/Fail?
Maybe try this:
Code: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
Depends on what you mean by "Last records". Last 3 records, last 5 records, ...?But what i want is the program only screen the last 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)
Code:'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?
[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:
Hi Ssanfu,Code: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
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.
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:
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" ' 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,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:
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" ' 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
Now only i know i can attach the file. I've attach my file. Maybe you can take a look?
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"?
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 Steve,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"?
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.
Hi Moderator,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.
Yes, will definitely input inspector/inspectedBy into it.
But now the most important things is add the "FAIL" code.
Anyone can help? Keep trying but failed
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?
Hi Steve,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.
Thank you for your help. But my problem still not solve yet. I've answered your question in blue text color.
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?
Code: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
I am TOTALLY lost!
In Post #19 you show what you want to happen - there are 7 examples.
Now you change and say
But in the examples 3, 4 & 5, you show Fail Email even when the last Status is "Pass"!!!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"
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).