CODEWITHSUNDEEP
sqldatabasevb.netvbams-access
user3669092
user3669092

Reputation: 53

VBA check if distinct values from 2 fields match

I'm VERY new to VBA but have some coding experience so I'm slowly starting to learn. I have two tables in my access database that each have one field that is the same. One table is a distinct list of the possible values ("TOSITEXREF")that could show up in the same field on the other table ("Trans_Earned"). This function I am trying to create will run in a macro to figure out whether or not the data that is being appended via queries has an instance in the ToLocn field that is not on the list of possible values. Here is what I have so far, but is not working:

Function TESTING()

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("TransEarned")

Dim rs2 As DAO.Recordset
Set rs2 = CurrentDb.OpenRecordset("TOSITEXREF")

Dim cond1A As Boolean

cond1A = (rs.Fields("ToLocn") = rs2.Fields("ToLoc"))

If cond1A Then
    DoCmd.OpenQuery "Earns", acViewNormal, acEdit
Else
    DoCmd.CancelEvent
    MsgBox "Unknown ToLocation, Please Update TOSITEXREF File to acccount for new location", vbOKOnly, "NEW LOCATION"
End If

Set rs = Nothing
Set rs2 = Nothing

End Function

Upvotes: 1

Views: 626

Answers (1)

user3669092
user3669092

Reputation: 53

Final VBA example that will return a value in the Loc field from table1 that does not show up in the Loc field of Table2:

Function Validate()

Dim sql As String
Dim rs As DAO.Recordset

sql = "SELECT Table1.Loc FROM Table1 LEFT JOIN Table2 ON Table1.Loc =  Table2.Loc WHERE (((Table2.Loc) Is Null))"
Set rs = CurrentDb.OpenRecordset(sql)

If (rs.RecordCount = 0) Then
     MsgBox "WORKS", vbkokonly, "WORKS"
Else
     MsgBox "DOES NOT WORK", vbkokonly, "DOES NOT WORK"

Set rs = Nothing

End If
End Function

Upvotes: 1

Related Questions