We help IT Professionals succeed at work.

looping through a recordset please., to check to see if all records  are true in the query

Fordraiders
Fordraiders asked
on
looping through a recordset please., to check to see if all records  are true in the query

I dont know why this is so hard for me to check, but
just trying to see if ALL the records in a query are true

if the "SEND_TO_LEADER" field  =  YES  AND RENEWAL_SKU = "NO"  in ALL the records then  boolean  =  true

' code for recordset
'' ===========================  START CHECK FROM TRUMP ALL
countme = 0
' lets see if a value for "send_to_leader"  as a "YES" and all "renewal_skus" =  "NO"
Set MyDBr = CurrentDb
Set qdr = MyDBr.QueryDefs("Query_FOR_HTML_stl")
qdr.Parameters!ENTERVALUE = [Forms]![main]![dbo_t_redbook_pricing_escalation_detail_subform]![RBP_MASTER_ID]
On Error Resume Next
Set rstr = qdr.OpenRecordset
rstr.MoveLast
rstr.MoveFirst
countme_stl = rstr.RecordCount

If countme_stl > 0 Then
  ' MsgBox "yes", vbDefaultButton1
    gb_renewal_rsy = False
End If

'Else
'    '  MsgBox "no", vbDefaultButton1
'    gb_renewal_rsy = True
'End If
rstr.Close
Set rstr = Nothing
GoTo LINE5
' ====================================   END CHECK

Open in new window



"Query_FOR_HTML_stl"
query:
SELECT dbo_t_redbook_pricing_escalation_detail.RBP_MASTER_ID, dbo_t_redbook_pricing_escalation_detail.SKU, dbo_t_redbook_pricing_escalation_detail.PRODUCT_DESCRIPTION, dbo_t_redbook_pricing_escalation_detail.QTY, dbo_t_redbook_pricing_escalation_detail.TARGET_PRICE, dbo_t_redbook_pricing_escalation_detail.COMPETITOR_PRICE, dbo_t_redbook_pricing_escalation_detail.TARGET_GP, dbo_t_redbook_pricing_escalation_detail.CURRENT_PRICE, dbo_t_redbook_pricing_escalation_detail.CURRENT_GP, dbo_t_redbook_pricing_escalation_detail.VENDOR_GUIDELINE_GP, dbo_t_redbook_pricing_escalation_detail.APPROVED_PRICE, dbo_t_redbook_pricing_escalation_detail.APPROVED_GP, dbo_t_redbook_pricing_escalation_detail.SEND_TO_LEADER, dbo_t_redbook_pricing_escalation_detail.TARIFF_ITEM, dbo_t_redbook_pricing_escalation_detail.RENEWAL_SKU, dbo_t_redbook_pricing_escalation_detail.Line_Color
FROM dbo_t_redbook_pricing_escalation_detail
WHERE (((dbo_t_redbook_pricing_escalation_detail.RBP_MASTER_ID)=[ENTERVALUE]) AND ((dbo_t_redbook_pricing_escalation_detail.SEND_TO_LEADER)="YES") AND ((dbo_t_redbook_pricing_escalation_detail.RENEWAL_SKU)="NO"))
ORDER BY dbo_t_redbook_pricing_escalation_detail.Line_Color;

Open in new window



Thanks
fordraiders
Comment
Watch Question

View Solution Only

omgangIT Manager
CERTIFIED EXPERT

Commented:
The query returns records where
RBP_MASTER_ID = the value from the form field   AND
SEND_TO_LEADER = YES     AND
RENEWAL_SKU = NO

Your conditional checks to see if any records are returned (countme_stl > 0)   if so then set the BOOLEAN value to FALSE

There is no Else part to the conditional.  The default value for a BOOLEAN is FALSE so the code always returns FALSE.

Unless I'm missing something.
OM Gang
omgangIT Manager
CERTIFIED EXPERT

Commented:
You need to know how many records there are total in the table where RBP_MASTER_ID = the value from the form
Then you can compare that total count to the count of those records where the other two conditions are met.
If the counts are equal then BOOLEAN value = True
If not, then FALSE
aikimark
CERTIFIED EXPERT
Top Expert 2014

Commented:
Your query, as written, should return all rows that meet your criteria.  Do you suspect that some rows violate that condition?
Software & Systems Engineer
CERTIFIED EXPERT
Distinguished Expert 2019
Commented:
Based on your previous question  you could just mod my code to include a counter and just compare it to the RecordCount. 
While Not .EOF
 If .Fields("RENEWAL") = "yes"  then
counter =counter+1
End if
.MoveNext
Wend
if counter = rst.RecordCount then
msgbox "EQUAL"
else
msgbox counter/rst.RecordCount 
End If

John TsioumprisSoftware & Systems Engineer
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
Iff you want to do it FAST then you can do it on query level like i do it on my attachment
Database121.accdb
Fordraiders

Author

Commented:
thats, what i ended up doing was getting a count of first:
send to leader  =  "Yes" ONLY.

then check to see if any rows  were(MY QUERY)
SEND_TO_LEADER = YES     AND
RENEWAL_SKU = NO

because i might have a RENEWAL_SKU = YES

compared those 2 counts and created a boolean based on the 2 counts
 if a =  b then  true else false

tHANKS