VBA Using InStr() match a record in Table A to a record in Table B, then update a field in Table A with a value from the matching Table B record

Question

I'm having one of those days where everything has gone blank. Simply - I have two tables, called WordyA and WordyB. WordyA contains a field called 'Answer' and may contain values such as 'Excellent, brilliant, fantastic, etc' WordyA also contains an analysis field called 'Analysis' that will insert a 'common value' from the second table, WordyB. WordyB is a sort of directory, and had two fields - 'Word' containing 'Excellent etc' above, but also a code field called 'Analysis'. The idea being, all the words above might be classed as code 'A'. If the words in the WordyB table were 'Very Good' or 'Pretty Good' they would have a code of 'B' I want to cycle through the WordyA records and assign a code letter from the WordyB table. So we can produce tables and analysis with all the type 'A' responses, compared to call the type 'B' responses.

I used the following VBA code, and played around with it so much I can't see the wood for the trees. So even simple suggestions would help!

I'm using nested DO statements to try to run through each record at a time. (There are only 30 'answers' in WordyB so it shouldn't be too process hungry)

Dim findwhat As String
Dim outanalysis As String
Dim findresult As Integer
Dim mresult As String
Dim lineid As Integer

Set rsanswer = CurrentDb.OpenRecordset("WordyA")
rsanswer.MoveFirst

Do Until rsanswer.EOF Or rsanswer.BOF
    lineid = rsanswer![IDData]
    findin = rsanswer![Answer]
    mresult = rsanswer![Analysis]
    Set rsdirect = CurrentDb.OpenRecordset("WordyB")
    rsdirect.MoveFirst
    Do Until rsdirect.EOF Or rsdirect.BOF
        outanalysis = rsdirect![DLetter]
        findwhat = rsdirect![DWord]
        If InStr(findin, findwhat) Then
            Rem CurrentDb.Execute "update WordyA Set mresult = outanalysis where IDData = " & lineid
        End If
    rsdirect.MoveNext
Loop
rsanswer.MoveNext
Loop
rsanswer.Close
rsdirect.Close

My version on the IF statement either reports a missing clause or just doesn't do anything.

I don't think the 'update' option I have REM'd out above is right, looking for alternatives

Answer 1

Concatenate the value:

CurrentDb.Execute "update WordyA set mresult = '" & outanalysis & "' where IDData = " & lineid & ""

Addendum:

The field name is not mresult but Analysis, thus:

CurrentDb.Execute "update WordyA set Analysis = '" & outanalysis & "' where IDData = " & lineid & ""

However, use DAO as you have the table open and avoid the clumsy SQL:

Set rsanswer = CurrentDb.OpenRecordset("WordyA")
Set rsdirect = CurrentDb.OpenRecordset("WordyB")

rsanswer.MoveFirst
Do Until rsanswer.EOF Or rsanswer.BOF
    findin = rsanswer![Answer].Value
    rsdirect.MoveFirst
    Do Until rsdirect.EOF Or rsdirect.BOF
        findwhat = rsdirect![DWord].Value
        If InStr(findin, findwhat) Then
            rsanswer.Edit
            rsanswer!Analysis.Value = rsdirect![DLetter].Value
            rsanswer.Update
        End If
        rsdirect.MoveNext
    Loop
    rsanswer.MoveNext
Loop
rsanswer.Close
rsdirect.Close