Looking for matches, adding to match table

[Darryl Kerkeslager]

1. Search field "agency_name" of table "agency" for possible matches to the
agency name I plan add; put results in recordset RF.

2. Add the similar-sounding names to table "matches" using recordset RT

Several loops of step #1 are made, looking for matching strings, likely
resulting in some same agencies being added to RF on successive passes.
Previously when I did this, for step #2, I looped through each record in RF
added in step #1, and used Seek to make sure the record was not already in
RT, then added it.

The goal was so that the user could look at possible matches before deciding
to add the new agency.

I am dissatisfied with the results, and feel there must be a better way to
do this. I thought perhaps I could just .AddNew, and if I hit an error on
..Update, just discard the .Addnew in error handling and move on (?).

Better still, is there some query that I could just run Like "INSERT INTO RT
all records NOT IN RF" ?

This seems so basic, and yet ...

--
Darryl Kerkeslager

[Darryl Kerkeslager]

Never mind. Found adequate solution:

rL.Open "NewAgencyTable", CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic, adCmdTable
For i = 1 To sLen - 4
src = "SELECT agency_id, agency_name, agency_type " & _
"FROM su_agency WHERE agency_name Like '%" & Mid(aName, i,
4) & "%'"
rL2.Open src, CurrentProject.Connection, adOpenForwardOnly, _
adLockReadOnly, adCmdText
Do While Not rL2.EOF
rL.AddNew
rL.Fields("agency_id") = rL2.Fields(0)
rL.Fields("agency_name") = rL2.Fields(1)
rL.Update
rL2.MoveNext
Loop
rL2.Close
Next i
rL.Close
Exit Sub
handle_error:
If Err.Number = -2147217887 Then
rL.CancelUpdate
Resume Next
End If

--
Darryl Kerkeslager