Compare text vs a table's fields. If match, write data pulled from *adjacent* field (1 Viewer)

[southen]

The below snippet is part of a bigger piece of code. Essentially, I'm trying to transform the code to make it more versatile.

To this end, rather than having values hard-coded in the VBA, I'd like for the VBA to read those values from a table. I'd like the code to loop through the table's column until there is a blank field in the relevant column, and at that point, stop looping.

Assume I have a table "Word" and two columns: "Match" and "Extra Info." I'd like the VBA to read from column Match. If there is no value, the script ends. If there is a value, and that value matches what the VBA is comparing against, then the code reads the data from the corresponding field "Extra Info" and writes it to another field.

Below is the example of how it works now, without this dynamic behavior. For reference, the VBA is reading an Outlook .msg file:

 'This command below will search the email body for the following words and if found, insert them into the Access field "Description"

 'Round 1
      If InStr(msg.Subject, "TC> 0 Or InStr(msg.body, "TC") > 0 Then
Me.Description = "TC Extra Info: " & strTicket            
     End If
  
 'Round 2
  ElseIf InStr(msg.Subject, "TR") > 0 Or InStr(msg.body, "R") > 0 Then
Me.Description = "TR Extra Info"
            End
        End If

So, I'd like TC and TR to pull from table Word, column Match. If there is a match in the email's msg.body, then Me.Description = table Word, column Extra Info.

I thought I could do an if isnull on the match field- if true, end the script, and if false, then do Dlookup. But I'm having problems with the if null working, and also don't know how to iterate that well.

I'd immensely appreciate any help or guidance I could get on this.

Thank you so much!!

 

[southen]

I'm sorry, but...bump

 

[Frothingslosh]

What if the text contains more than one of your keywords? Do you just go with the first one you find, or do you need to do something with all of them?

 

[southen]

Thanks a ton for the response! I see what you're saying. The text likely will only contain one key word; in case there are two, it should just be first match. If there's a match, then it'd be great to end the check. Once we finish going through the list of keywords to check, if there's no match, it would be good to just not take any action.

 

[Frothingslosh]

Ending the check is easy, but questions like that are things you always need to keep in mind. You said that the text will likely only contain one key word, but I guarantee you that if people are involved, then at some point you'll wind up with multiple keywords and need a plan for what to do. If it's 'just use the first one', that's fine; just have a plan.

As to doing the match, the simplest, if brute-force, approach is to create a recordset based on the table containing the words, and cycle through it until you either reach the end of the recordset or find a match. If you find a match, you can abort any loop with the appropriate Exit command (Exit For, Exit Do, etc).

One way to do that would be something like this:

Public Function FindKeyword(ByVal SearchText As String) As String
Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("YourSourceHere")
 
    With rs
        Do Until .EOF
            If InStr(1, SearchText, !Match) > 0 Then
                FindKeyword = !ExtraInfo
                Exit Do
            End If
            .MoveNext
        Loop
    End With
 
    rs.Close
    Set rs = Nothing
 
End Function

This is just a function that opens a recordset based on "YourSourceHere" (you'll want to change that) and cycles through the recordset, checking your keywords against the string provided when you called the function. If it finds a match, it returns the value found in ExtraInfo. if it doesn't, it returns an empty string.

You would then do whatever you want with that string that gets returned.

That is also, btw, how to cycle through a recordset. Beats the HELL out of repeated If/Then statements and repeated DLookups. If you're unsure about anything I did and what it does, feel free to ask.

 

[southen]

Thanks a ton, I really appreciate the time you spent on providing this information! I follow the logic but I'm still a little fuzzy on how to integrate with the something like msg.Subject. I would have thought I should define SearchText or replace it with msg.Subject? I can't see where that part would go. As for the FindKeyword = !ExtraInfo, we're just placing the value from ExtraInfo into field FindKeyword, right?

Thanks!

 

[Frothingslosh]

Okay, SearchText is the variable that is used for a parameter. Basically, when calling the function, you provide the text you want searched. So:

Result = FindKeyword(msg.body)

This would run the function on the text contained in msg.body and put either an empty string or the extra text into the variable Result, which you would then use as you will.

As to your other question, I'm basically using shortcuts. The command "With rs" tells Access that it should assume anything that I start with either a period or an exclamation point should be treated as having the rs object in front of it. So !ExtraInfo is really rs!ExtraInfo, which is itself shorthand for rs.Fields("ExtraInfo").Value.

Make sense so far?

Then the line FindKeyword = !ExtraInfo simply means "Assign the value contained in the field ExtraInfo on the current record to FindKeyword". As FindKeyword is the function name, that means that, unless it gets changed elsewhere in the procedure (and it doesn't here), that is the value that will be returned. (Basically, functions are used as variables inside themselves, although it's usually safest to only assign a value at the very end.)

The idea behind making this its own procedure is just to keep things modular. A general rule is that each procedure should do one thing. You'll break it a lot, but try to stick to that when you can. If you need something else done, call another procedure. This makes your code MUCH easier to develop, debug, and maintain. And yes, this does mean that you will ofttimes wind up with a 'driver' procedure that does little other than call other procedures.

 

[southen]

Thank you very much for the further explanation. I'm stepping out for the weekend, but will continue to try to get this right again on Monday. In the meantime, I wanted to thank you again for taking the time to help with a solution and to explain it to me.

 

[southen]

I reviewed the code and I have a much better handle on it now. I've done incorporation and it works wonderfully. I'm not going to venture in to weighting frequency of terms (ex., if it matches > 3 times, then...) since I don't think this is a good measure of predictability. But thank you so much again for this code and for the explanation, it helps tremendously!