If FAIL then update FAIL. (1 Viewer)

Onlylonely · Feb 15, 2019

Hi All,

Appreciate if you could help me out.

Below is the result i wish to get.

SN Step Result Summary_Result
1 1 Pass Fail
1 2 Fail Fail
1 3 Pass Fail
1 4 Pass Fail
1 5 Pass Fail
1 6 Pass Fail

But with using code below.

Code:

sSQL = "Update Issuetbl Set [Issuetbl].[Summary_Result] = 'FAIL'  WHERE [Issuetbl].[Serial_Number] = '" & Forms!checklist!txtSN & "' AND [Issuetbl].[Result] ='FAIL'"
CurrentDb.Execute (sSQL)

SN Step Result Summary_Result
1 1 Pass
1 2 Fail Fail
1 3 Pass
1 4 Pass
1 5 Pass
1 6 Pass

Can someone help me out?

Micron · Feb 15, 2019

the details are too sketchy to be accurate. You'd never have a "fail pass" entry? Always a space between the words?
Odd that you have multiple values in the same field like that. Easy enough to do via Replace function if not.

Onlylonely · Feb 15, 2019

Micron said:
the details are too sketchy to be accurate. You'd never have a "fail pass" entry? Always a space between the words?
Odd that you have multiple values in the same field like that. Easy enough to do via Replace function if not.

Hi Micron,

Let me briefly explain.
For the column result, if it has "fail" then update the same S/N with "Fail" in summary result. Please refer to attachment.

Micron · Feb 15, 2019

I must be too tired to grasp this, which is more confusing than where I started. What I read now doesn't jive with the pictures, plus it kinda looks like you have 'actual' and 'expected' backwards.

"if result = 'fail' then update Summary with 'fail'" but you show a summary value of Fail where you have a result of Pass in your "expected" result set for step 1. If I'm right and you have expected/actual reversed, then you want summary result to be Fail where Result is Fail but it already is Fail (in the top view). None of that looks like what you first posted (to me).
I guess my first suggestion won't work because you don't have "Pass Fail" in one field like I thought you did. Sorry.

surely it can't be this simple, but you could test it on a copy of your table

Code:

"Update Issuetbl Set [Issuetbl].[Summary_Result] = 'Fail' WHERE [Result]='Fail'"

Onlylonely · Feb 18, 2019

Micron said:
I must be too tired to grasp this, which is more confusing than where I started. What I read now doesn't jive with the pictures, plus it kinda looks like you have 'actual' and 'expected' backwards.

"if result = 'fail' then update Summary with 'fail'" but you show a summary value of Fail where you have a result of Pass in your "expected" result set for step 1. If I'm right and you have expected/actual reversed, then you want summary result to be Fail where Result is Fail but it already is Fail (in the top view). None of that looks like what you first posted (to me).
I guess my first suggestion won't work because you don't have "Pass Fail" in one field like I thought you did. Sorry.

surely it can't be this simple, but you could test it on a copy of your table

Code:

"Update Issuetbl Set [Issuetbl].[Summary_Result] = 'Fail' WHERE [Result]='Fail'"

Hi Micron,

What i want is, if 1 of the result show FAIL in result column. Then update the summary result "FAIL" for the same S/N.

Micron · Feb 18, 2019

Copy your table in order to back up its data and try this on your table copy
UPDATE tblYourTable SET Summary_Result = "Fail" WHERE Result LIKE "*Fail*";
Of course, you substitute your table and field names. It should change whatever is in the summary field to Fail if the Result contains Fail, across all records.

Onlylonely · Feb 18, 2019

Micron said:
Copy your table in order to back up its data and try this on your table copy
UPDATE tblYourTable SET Summary_Result = "Fail" WHERE Result LIKE "*Fail*";
Of course, you substitute your table and field names. It should change whatever is in the summary field to Fail if the Result contains Fail, across all records.

Hi Micron,

I've tried this an it is not working.
The code only will update the summary result to FAIL if the result column contain FAIL.
For the result column is pass , it will not update to FAIL for the same S/N.

Micron · Feb 18, 2019

Now I get it (I think). You want to update ALL records for a SN value where ANY record in that group has Fail in the Result field. This might be done in any of 3 ways:
- join the same 2 tables together in a query
- write a code procedure
- use a subquery
I struggle with subqueries, albeit they are the cleanest and most direct method for such things. The following sql is "air code" thus is untested. To do more than pull this out of thin air I would need a table to test it on, but you can give it a try. Change your table/field names as required but do not change AS T...

Code:

UPDATE tblYourTable SET Summary_Result = "Fail" WHERE SN IN
 (SELECT SN FROM tblYourTable AS T WHERE T.Result LIKE "*Fail*");

Look at this great site for stuff when you need help, but for this problem, scan down the page for Subquery basics.
http://allenbrowne.com/tips.html

Onlylonely · Feb 18, 2019

Micron said:
Now I get it (I think). You want to update ALL records for a SN value where ANY record in that group has Fail in the Result field. This might be done in any of 3 ways:
- join the same 2 tables together in a query
- write a code procedure
- use a subquery
I struggle with subqueries, albeit they are the cleanest and most direct method for such things. The following sql is "air code" thus is untested. To do more than pull this out of thin air I would need a table to test it on, but you can give it a try. Change your table/field names as required but do not change AS T...

Code:

UPDATE tblYourTable SET Summary_Result = "Fail" WHERE SN IN (SELECT SN FROM tblYourTable AS T WHERE T.Result LIKE "*Fail*");

Look at this great site for stuff when you need help, but for this problem, scan down the page for Subquery basics.
http://allenbrowne.com/tips.html

Hi Micron,

Im using procedure coding. Type mismatch.

Code:

sSQL = "Update Issuetbl Set [Issuetbl].[Summary_Result] = 'Fail' WHERE [Issuetbl].[Serial_Number] = '" & Forms!checklist!txtSN & "' IN (Select Serial_Number from issuetbl AS T where T.Result LIKE " * Fail * ")"
MsgBox (sSQL)
CurrentDb.Execute (sSQL)

Gasman · Feb 18, 2019

If the field is numeric do not use single quotes, that is for text.

Micron · Feb 18, 2019

Date values must be enclosed in # and string/text in quotes ' or ".
You've introduced a parameter that you didn't specify before - that you want a form control value to be in the mix. What you have won't work even if you fix the error because the form value needs to go in the subquery. Try

Code:

Update Issuetbl Set [Issuetbl].[Summary_Result] = 'Fail' WHERE [Issuetbl].[Serial_Number] IN 
(Select Serial_Number from issuetbl AS T WHERE T.Result LIKE '*Fail*' AND T.[Serial_Number] = 1)

Note that I didn't consider line continuation characters in eliminating scrolling here. If that works on your table copy then you have a working sql. All you'd need to do is work in the form reference instead of the value 1. I left it as is because it's unknown at this point what's text and what's not.

Onlylonely · Feb 19, 2019

Micron said:
Date values must be enclosed in # and string/text in quotes ' or ".
You've introduced a parameter that you didn't specify before - that you want a form control value to be in the mix. What you have won't work even if you fix the error because the form value needs to go in the subquery. Try

Code:

Update Issuetbl Set [Issuetbl].[Summary_Result] = 'Fail' WHERE [Issuetbl].[Serial_Number] IN (Select Serial_Number from issuetbl AS T WHERE T.Result LIKE '*Fail*' AND T.[Serial_Number] = 1)

Note that I didn't consider line continuation characters in eliminating scrolling here. If that works on your table copy then you have a working sql. All you'd need to do is work in the form reference instead of the value 1. I left it as is because it's unknown at this point what's text and what's not.

I'm still stuck... Attach herewith the file. Hopefully can solve it.
I've solve the step 1 summary to FAIL, but Step 2 summary still remain PASS.

Onlylonely · Feb 20, 2019

Onlylonely said:
I'm still stuck... Attach herewith the file. Hopefully can solve it.
I've solve the step 1 summary to FAIL, but Step 2 summary still remain PASS.

Anyone help? I still stuck ......

essaytee · Feb 21, 2019

I've come in late on this one. From your module 'Update Step 3' this is your snippet of code:

Code:

If Forms!checklist2!Frame305!.Value = 1 Then

sSQL = "Update Issuetbl Set [Issuetbl].[Result] = 'Pass'  WHERE [Issuetbl].[Serial_Number] = '" & Forms!checklist2!txtSN & "' AND [Issuetbl].[Character_ID] =5 AND [Issuetbl].[Step] ='3'"
CurrentDb.Execute (sSQL)

ElseIf Forms!checklist2!Frame305!.Value = 2 Then

sSQL = "Update Issuetbl Set [Issuetbl].[Result] = 'Fail'  WHERE [Issuetbl].[Serial_Number] = '" & Forms!checklist2!txtSN & "' AND [Issuetbl].[Character_ID] =5 AND [Issuetbl].[Step] ='3'"
CurrentDb.Execute (sSQL)
End If (sSQL)

There is no '3' value in the Step field of Table Issuetbl.

Onlylonely · Feb 21, 2019

essaytee said:

I've come in late on this one. From your module 'Update Step 3' this is your snippet of code:

Code:

If Forms!checklist2!Frame305!.Value = 1 Then

sSQL = "Update Issuetbl Set [Issuetbl].[Result] = 'Pass'  WHERE [Issuetbl].[Serial_Number] = '" & Forms!checklist2!txtSN & "' AND [Issuetbl].[Character_ID] =5 AND [Issuetbl].[Step] ='3'"
CurrentDb.Execute (sSQL)

ElseIf Forms!checklist2!Frame305!.Value = 2 Then

sSQL = "Update Issuetbl Set [Issuetbl].[Result] = 'Fail'  WHERE [Issuetbl].[Serial_Number] = '" & Forms!checklist2!txtSN & "' AND [Issuetbl].[Character_ID] =5 AND [Issuetbl].[Step] ='3'"
CurrentDb.Execute (sSQL)
End If (sSQL)

There is no '3' value in the Step field of Table Issuetbl.

Hi essaytee,

Sorry to make you confuse. I haven't work on the step 3 yet.
I'm working on step 1 and 2...

What i want is if there is a FAIL in step 1 or 2, table summary field need to update to "FAIL".

Micron · Feb 21, 2019

Wow. So many controls and none of them bound. I can find
"Update Issuetbl Set [Issuetbl].[Summary_Result] = 'Fail'..." in a few places, all of them commented out I think. What to do to replicate the issue in a database you've never seen before and no instructions??

Onlylonely · Feb 21, 2019

Micron said:
Wow. So many controls and none of them bound. I can find
"Update Issuetbl Set [Issuetbl].[Summary_Result] = 'Fail'..." in a few places, all of them commented out I think. What to do to replicate the issue in a database you've never seen before and no instructions??

Hi Micron,

Yes, all the control are not bound.

What to do to replicate the issue in a database you've never seen before and no instructions?? << i dont really get it. can you give me an example?

Micron · Feb 21, 2019

If I sent you a db and didn't tell you what form to open, data to enter, button to push, query to run, table to look at etc. etc. how effective do you think you would be in solving a problem? That's my dilemma when I open your db. I have zero idea on what to do or look at.

Onlylonely · Feb 21, 2019

Micron said:
If I sent you a db and didn't tell you what form to open, data to enter, button to push, query to run, table to look at etc. etc. how effective do you think you would be in solving a problem? That's my dilemma when I open your db. I have zero idea on what to do or look at.

My apologize,

1st - Open checklistform.
2nd - Input any part number , job number and Distinct Serial number.
3rd - Step 1, Question 1 to 12, put at least 1 FAIL
4th - Step 2 , Question 1 to 7 , put all pass
5th - Submit it.
6th - Open Issuetbl, check the data. Summary not update to FAIL

What i want is once we have FAIL, the update FAIL in Summary for Step 1 and Step 2 for all question.

Micron · Feb 21, 2019

I can't imagine why this is all being done in code rather than the form being bound, but that's just me.

I've been stepping through, and from what I can see it works but somewhere along the line, you undo it. Put a break on your code and step through with the table open. When the Summary field is updated a few times, go back to the table (leave the code window in a halted state) and click Refresh All and see that the Summary field has been set to False for as many times as you've allowed the sql to run. Then I continued stepping through, inquiring what the sql was in the immediate window. I see that these two are executed in 2 different places for some reason

Code:

Update Issuetbl Set [Issuetbl].[Summary] = 'Fail'  WHERE [Issuetbl].[Serial_Number] = '7789' and [Issuetbl].[Step]='2'
Update Issuetbl Set [Issuetbl].[Summary] = 'Fail'  WHERE [Issuetbl].[Serial_Number] = '7789' and [Issuetbl].[Step] ='1'

At some point, I went back to the table and found that several summary fields had been set back to Pass. What I'm saying is that it works, then it all gets undone. Unfortunately I didn't catch where so I leave that up to you.

Really, why don't you just bind the form to the table, and the frames to the fields??
EDIT - never mind for now. I think that's because you've put a frame for every record on a single record form rather than have a continuous subform.

If FAIL then update FAIL. (1 Viewer)

Registered User.

AWF VIP

Registered User.

Attachments

AWF VIP

Registered User.

AWF VIP

Registered User.

AWF VIP

Registered User.

Enthusiastic Amateur

AWF VIP

Registered User.

Attachments

Registered User.

Need a good one-liner.

Registered User.

AWF VIP

Registered User.

AWF VIP

Registered User.

AWF VIP

Similar threads

Users who are viewing this thread