CODEWITHSUNDEEP
sqlms-accessvbams-access-2010
Lilly
Lilly

Reputation: 233

Checking if record matches another MS Access VBA/SQL

I'm not sure how to find a matching record via VBA. In the SELECT SQL it is selecting a record that matches the field of program and language from table CFRRR. In the IF statement I want to know if the selected record's caseid (which is also a field in CFRRR) matches the casedid of another record on CFRRR. I'm not sure if go the direction of DLookup or Match. Here is where I am at:

 strSQL = "SELECT CFRRRID, [Program], [language] FROM CFRRR
 WHERE assignedto Is Null"
    Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

    If rs.currentrecord.caseid

Upvotes: 1

Views: 836

Answers (1)

HansUp
HansUp

Reputation: 97101

You're SELECTing CFRRRID and you want to know whether that CFRRRID value is present in another row of the same table. You can include a DCount expression to find out.

strSQL = "SELECT CFRRRID, [Program], [language], " & _
    "DCount('*', 'CFRRR', 'CFRRRID=' & CFRRRID) AS CountOfCFRRRID " & _
    "FROM CFRRR WHERE assignedto Is Null"

When the calculated value, CountOfCFRRRID, is greater than 1, you know the current CFRRRID value is stored in another row of the table.

Upvotes: 1

Related Questions