I have a table, "tScenarios", with a short text field called "wostCaseTopEvent". The value for this field can either be a preset selection or something the user enters themselves. I wrote some code that adds anything new that the user enters to another table, "tTopEvents". It compares the contents of "tScenarios" to the contents of "tTopEvents", and if it finds a match it won't create a new record.
For some reason it's not registering entries that match as matching, and duplicates records a number of times. This doesn't happen for all entries, only some, and I can't figure out why. I even made sure there were no extra spaces / spelling mistakes and copy/pasted the entries from one table onto the other to ensure they were exactly the same, and the issue persists.
Here's my code:
Code:
Dim db As Database, rs As Recordset, rs2 As Recordset
Dim TE As String, TEcheck As String
Set db = CurrentDb
Set rs2 = db.OpenRecordset("tTopEvents")
Set rs = db.OpenRecordset("tScenarios")
Do Until rs.EOF
TE = rs(5)
'for testing, remove later
MsgBox "scenario event: " & TE
TEcheck = "False"
Do Until rs2.EOF
If rs2(1) = TE Then
'for testing, remove later
MsgBox TE & " matched with " & rs2(1)
TEcheck = "True"
End If
rs2.MoveNext
Loop
If TEcheck = "False" Then
'for testing, remove later
MsgBox "No match found for " & TE & " Adding to table"
db.Execute "INSERT INTO tTopEvents(topEvent)" & "VALUES(""" & TE & """)"
End If
rs.MoveNext
Loop
If anyone knows why this is happening, please let me know. Any help is appreciated!