Avatar of Fordraiders
Fordraiders
Flag for United States of America asked on

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

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
Microsoft AccessVBA

Avatar of undefined
Last Comment
Fordraiders

8/22/2022 - Mon
omgang

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
omgang

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

Your query, as written, should return all rows that meet your criteria.  Do you suspect that some rows violate that condition?
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ASKER CERTIFIED SOLUTION
John Tsioumpris

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
John Tsioumpris

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

ASKER
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