MS Access Find & Highlight Multiple Substrings tha

Question

I'm a beginner trying to figure out a strategy.

Sample Data Table: Ingredients Field: FormulaIngredients Field Contents (Long Text Rich Text): rutabaga, leeks, carrots, wheat, flour, butter, sugar, eggs, milk, peanut butter, stone ground corn meal, whole grain oats, cabbage, turmeric, cloves, spice, natural beef flavor, ground beef.

Table: RestrictedTable Column with 100 values to compare: RestrictedItem Example Column Values: milk bake spoon carrots mustard steam

Desired Result: Want to highlight/change font of matches within FormulaIngredients field that match to any of the ~100 values in a Table Column: RestrictedItem. Imagine the italicized words are red.

rutabaga, leeks, carrots, wheat, flour, butter, sugar, eggs, milk, peanut butter, stone ground corn meal, whole grain oats, cabbage, turmeric, cloves, spice, natural beef flavor, ground beef, mustard.

Or, copy & replace the content of FormulaIngredients to a new field which applies a red color to those words that match to Table Column: RestrictedItem.

I've explored...

InStr Problem: I don't want to pass a string of info to a form/report, also do not care about the position the substring was found in, I want to find all of them, in any case & duplicates are ok.

Dim strTemp, strTempEnd As String
Dim strSearch As String

strSearch = Me.OpenArgs

If InStr(1, Me.Text0, strSearch) <> 0 Then
    strTemp = Left(Me.Text0, InStr(1, Me.Text0, strSearch) - 1)
    strTempEnd = Mid(Me.Text0, Len(strTemp) + Len(strSearch) + 1)
    strTemp = strTemp & "<font color=red>" & strSearch & "</font>"
    strTemp = strTemp & strTempEnd

    Me.Text0 = strTemp
End If

HTML Problem: This solution compares 2 columns of data. I want to compare one field to a table of values & find multiple matches within that one Long Text field.

    Public Function MyCompare(c1t As Variant, c2t As Variant)

  Dim strResult    As String
  Dim s        As String
  Dim i        As Integer
  Dim bolSame     As Boolean

  If IsNull(c1t) Or IsNull(c2t) Then Exit Function

  bolSame = True

  For i = 1 To Len(c2t)
   s = Mid(c2t, i, 1)
   If Mid(c1t, i, 1) = s Then
    If bolSame = False Then
      bolSame = True
      s = "</strong></font>" & s
     End If
   Else
     If bolSame = True Then
      bolSame = False
      s = "<font color=red><strong>" & s
     End If
   End If
   strResult = strResult & s
  Next

  If bolSame = False Then
   strResult = strResult & "</strong></font>"
  End If
  MyCompare = strResult

End Function

VBA Problem: I would have to type out all 100 keywords in my table that I want to compare/search in my Long Text form field & REPLACE is a case sensitive search. Is there a way to compare to a table of values?

Me.{ControlName}.Value = Replace(Me.{ControlName}.Value _
                               , "red", "<font color=red>red</font>")

Answer 1

Suggest VBA procedure that:

1. opens recordset of RestrictedTable

2. loops through recordset and runs an UPDATE action SQL on Ingredients table

Example:

Dim db As DAO.Database  
Dim rs As DAO.Recordset  
Set db = CurrentDb  
Set rs = db.OpenRecordset("SELECT * FROM RestrictedTable")  
Do While Not rs.EOF  
    db.Execute "UPDATE Ingredients SET FormulaIngredients = Replace([FormulaIngredients], '" & rs!RestrictedItem & "', '<font color=red>" & rs!RestrictedItem & "</font>')"  
    rs.MoveNext  
Loop

I did a quick test with static parameter and it worked.

CurrentDb.Execute "UPDATE Ingredients SET FormulaIngredients = Replace([FormulaIngedients], 'carrots', '<font color=red>carrots</font>')"

Doesn't matter if the formula text has CARROTS, carrots, CArRoTs - all will match and be replaced with carrots. However, if formula text has carrot, it will not match.

Partial matches might not be a concern, but if you want to be sure to change only whole words, use space before and after the search and replace strings: ' carrots '. Unless you want milk in buttermilk to get highlighted.

If you don't want to alter the original data, first copy to another field then use that other field in the UPDATE statement. This can be done with an UPDATE statement before the Do While block.

db.Execute "UPDATE Ingredients SET FormulaHighlight=FormulaIngredients"