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

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?

ASKER CERTIFIED SOLUTION

John Tsioumpris

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

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