Changing color for multiple characters in a report (1 Viewer)

Alc

Registered User.
Local time
Yesterday, 23:52
Joined
Mar 23, 2007
Messages
2,343
I found the following online and after a couple of tweaks it works well to highlight each instance of a single word in the 'Synopsis' field of a report.
Code:
Sub Highlight_Keywords()
    Dim RstKey As Recordset
    Dim strSearch As String
    Dim strTemp As String
    Dim strTempEnd As String

    Set RstKey = Forms!frmCRTTBControl!frmCRCriteriaTab!frmCRSystemKeyword.Form.RecordsetClone
    If RstKey.RecordCount <> 0 Then
        RstKey.MoveFirst
        Do While Not RstKey.EOF
            strSearch = RstKey!keyword

            Reports!rptPDResults!txtSynopsis.ControlSource = "=IIf([Synopsis] Is Null, Null, Replace([Synopsis], """ & strSearch & """, ""<b><font color=red size = 4>" & strSearch & "</font></b>""))"

            RstKey.MoveNext
        Loop
    End If
    RstKey.Close
    Set RstKey = Nothing
End Sub
Where I'm getting stumped is in adapting it so that it can change the colour and size of all words selected by the user.

The code above loops through the selection perfectly, but - as you can see - each iteration overwrites the previous, meaning that only the last word entered is coloured on the final report.

Is there an easy way for it to highlight however many words are entered?

If it were on a form, I know how to do it, but the fact that the report is being changed in design view has me puzzled, since the text isn't yet displayed.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 20:52
Joined
Oct 29, 2018
Messages
13,210
Hi. I guess I don't understand the difference between manipulating a form versus a report. I don't see anything in the code you posted, unless I just missed it, where the report is being opened in design view. Did I misunderstand your concern?
 

Alc

Registered User.
Local time
Yesterday, 23:52
Joined
Mar 23, 2007
Messages
2,343
Hi. I guess I don't understand the difference between manipulating a form versus a report. I don't see anything in the code you posted, unless I just missed it, where the report is being opened in design view. Did I misunderstand your concern?
Sorry, that was just a sub procedure that's being called to do the highlighting, the report is already open in design view at that point.
 

isladogs

CID VIP
Local time
Today, 04:52
Joined
Jan 14, 2017
Messages
14,018
Do you mean something like the screenshot below which is a report used to show results from my 'deep SQL search' feature?



As you can see, I use highlighting for each search term.
Did you want different colouring or highlighting for each word?
 

Attachments

  • SQLSearchReport.gif
    SQLSearchReport.gif
    53.6 KB · Views: 112

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 20:52
Joined
Oct 29, 2018
Messages
13,210
Sorry, that was just a sub procedure that's being called to do the highlighting, the report is already open in design view at that point.
I see. Have you tried modifying the Record Source instead? For example, you could duplicate the record source to a temp table, modify its contents, and then use it for the report. Just a thought...
 

Alc

Registered User.
Local time
Yesterday, 23:52
Joined
Mar 23, 2007
Messages
2,343
Do you mean something like the screenshot below which is a report used to show results from my 'deep SQL search' feature?



As you can see, I use highlighting for each search term.
Did you want different colouring or highlighting for each word?

That would work perfectly! I'm changing font colour but highlighting would do the job just as well.

The same colour for each word would be fine (I think some of the users might be put off by too many colours).
 

Alc

Registered User.
Local time
Yesterday, 23:52
Joined
Mar 23, 2007
Messages
2,343
I see. Have you tried modifying the Record Source instead? For example, you could duplicate the record source to a temp table, modify its contents, and then use it for the report. Just a thought...
I'm just playing with that at the moment.
Fiddly, but I think I see how it could work (and I'll take fiddly over not working).
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 20:52
Joined
Oct 29, 2018
Messages
13,210
Do you mean something like the screenshot below which is a report used to show results from my 'deep SQL search' feature?

As you can see, I use highlighting for each search term.
Did you want different colouring or highlighting for each word?
Just a guess, but I doubt you're opening the report in design view first. Am I right?
 

Alc

Registered User.
Local time
Yesterday, 23:52
Joined
Mar 23, 2007
Messages
2,343
Just to clarify, the report is opened in design view as the source is being reset. I left it in that view because I thought it needed to be, to do the rest of the manipulation, but I can close it and open it in preview mode if required.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 20:52
Joined
Oct 29, 2018
Messages
13,210
Just to clarify, the report is opened in design view as the source is being reset. I left it in that view because I thought it needed to be, to do the rest of the manipulation, but I can close it and open it in preview mode if required.
So, I guess I'm still lost with what is the difference between doing this on a form versus a report. You said you know how to do it with a form. What is different when doing it with a report? If not the part about opening the report in design view? :confused:
 

Alc

Registered User.
Local time
Yesterday, 23:52
Joined
Mar 23, 2007
Messages
2,343
So, I guess I'm still lost with what is the difference between doing this on a form versus a report. You said you know how to do it with a form. What is different when doing it with a report? If not the part about opening the report in design view? :confused:
With a form, I've used search and replace e.g.

strSearch = RstKey!keyword
strNew = "<b><font color=red size = 4>" & strSearch & "</font></b>"
Forms!frmPDResults!txtSynopsis = Replace(Forms!frmPDResults!txtSynopsis, strSearch, strNew)

With the report, this same method produces an error telling me "You can't assign a value to this object". I assumed it was because the report was already open, hence trying to do something in design view.
 

MickJav

It wasn't me
Local time
Today, 04:52
Joined
Nov 28, 2005
Messages
2,244
With a form, I've used search and replace e.g.

strSearch = RstKey!keyword
strNew = "<b><font color=red size = 4>" & strSearch & "</font></b>"
Forms!frmPDResults!txtSynopsis = Replace(Forms!frmPDResults!txtSynopsis, strSearch, strNew)

With the report, this same method produces an error telling me "You can't assign a value to this object". I assumed it was because the report was already open, hence trying to do something in design view.


Try using an unbound control plus the control will have to be a Rich Text


mick
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 20:52
Joined
Oct 29, 2018
Messages
13,210
With a form, I've used search and replace e.g.

strSearch = RstKey!keyword
strNew = "<b><font color=red size = 4>" & strSearch & "</font></b>"
Forms!frmPDResults!txtSynopsis = Replace(Forms!frmPDResults!txtSynopsis, strSearch, strNew)

With the report, this same method produces an error telling me "You can't assign a value to this object". I assumed it was because the report was already open, hence trying to do something in design view.
Hi. Thanks for the clarification. I guess we'll have to wait for Colin or Mick to explain how they are doing it on a report.
 

MickJav

It wasn't me
Local time
Today, 04:52
Joined
Nov 28, 2005
Messages
2,244
I have 2 styles for my chart reports Coloured and normal the coloured one

I have a Unbound text box set to rich text with the control source set to =DoChartRunColour([CID],[RID]) This dont work in a query because the contol needs to be rich text it wouldn't let me do it.


Some srecords can have over 600 chart details


I havent added any details as to the function(s) as you seem to know what your doing with the HTML only thing I'm not sure about there in you used red should it not be vbred for the colour I use a method by peter hibbs for the colours.
 

isladogs

CID VIP
Local time
Today, 04:52
Joined
Jan 14, 2017
Messages
14,018
The SQL Search feature is quite complex and embedded in one of my commercial apps so I can't provide a demo here. I'll try to explain the salient features as best I can without a demo.

The most important point is that highlighting/colouring is all handled from module code prior to opening the report

First of all the SQL search feature finds all objects with SQL containing the specified word(s) - up to 6 words in all. It then uses a recordset to loop through the SQL for each object and converts that SQL to HTML using the following procedure:

Code:
Public Sub UpdateSQLToHTML()

On Error GoTo Err_Handler

'updates SQL field to HTML (rich text) and adds highlighting of search criteria
Dim strSQL As String, strSQL1 As String
Dim strHTML1, strHTML2, strHTML3, strHTML4 As String
Dim arrSearch As Variant, N As Long
Dim strSearch As String, strText As String
Dim rst As DAO.Recordset

On Error GoTo Err_Handler

    strSQL1 = "SELECT tblDatabaseObjects.ObjectType, tblDatabaseObjects.ModifiedName," & _
        " tblDatabaseObjects.ItemName, tblDatabaseObjects.ObjectName, tblDatabaseObjects.SQL" & _
        " FROM tblDatabaseObjects" & _
        " WHERE (((tblDatabaseObjects.SQL) Is Not Null) AND ((tblDatabaseObjects.Tag)=True)" & _
        " AND ((tblDatabaseObjects.TypeSelected)=True));"
        
    'Debug.Print strSQL1
    
    'clear existing formatting
    Set rst = CurrentDb.OpenRecordset(strSQL1, dbOpenDynaset, dbSeeChanges)
    With rst
        If Not (.BOF Or .EOF) Then
            .MoveFirst
            Do Until .EOF
            .Edit
                strSQL = PlainText(!SQL)
                If strSQL <> "" Then
                    !SQL = strSQL
                End If
            .Update
            .MoveNext
            Loop
        End If
    End With
    
    'add main color to whole string
    strHTML1 = "<div><font color='#1F497D'>"
    strHTML2 = "</font></div>"
        
    Set rst = CurrentDb.OpenRecordset(strSQL1, dbOpenDynaset, dbSeeChanges)
    With rst
        If Not (.BOF Or .EOF) Then
            .MoveFirst
            Do Until .EOF
            .Edit
                strSQL = !SQL
                If strSQL <> "" Then
                    !SQL = strHTML1 & strSQL & strHTML2
                End If
            .Update
            .MoveNext
            Loop
        End If
    End With
        
   ' Set rst = Nothing
    
    'add highlighting to search text fields strText1=>strText6
    
    strHTML3 = "</font><font color='#800000'><font style='BACKGROUND-COLOR:#FFFF00'><strong>"
    strHTML4 = "</strong></font><font color='#1F497D'>"
    
    'set up array for loop
    strSearch = Replace(strText, "'", "")
    strSearch = Replace(strSearch, ",", "")
    strSearch = Replace(strSearch, "space", "_")
    arrSearch = Split(strSearch, " ")
  ' Debug.Print strText, strSearch
    'Debug.Print UBound(arrSearch)
        
    For N = 0 To UBound(arrSearch)
        strSelection = arrSearch(N)
        'Debug.Print strSelection
        If strSelection <> "" Then
        'strText(N) = Replace(strText1, """", "")
        
            Set rst = CurrentDb.OpenRecordset(strSQL1, dbOpenDynaset, dbSeeChanges)
            With rst
                If Not (.BOF Or .EOF) Then
                    .MoveFirst
                    Do Until .EOF
                    .Edit
                        strSQL = !SQL
                        If InStr(strSQL, strSelection) > 0 Then
                            strSQL = Replace(strSQL, PlainText(strSelection), strHTML3 & strSelection & strHTML4)
                            !SQL = strSQL
                        End If
                    .Update
                    .MoveNext
                    Loop
                End If
            End With
        End If
    Next N
    
    Set rst = Nothing
        
    'replace all single quotes in SQL field with double quotes
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "qryUpdateSQLFormatting"
    DoCmd.SetWarnings True

    'tidy up
    strSearch = ""
           
Exit_Handler:
    Exit Sub
        
Err_Handler:
    If Err = 3075 Then
        Resume Next
    Else
         MsgBox "Error " & err.Number & " in UpdateSQLToHTML procedure : " & Err.Description
         Resume Exit_Handler
    End If
    
End Sub

The HTML version changes the selected words to red text with yellow highlighting

The report then displays the generated HTML version of the SQL in a rich text contro las shown in the previous screengrab.

Hope that helps
 
Last edited:

Alc

Registered User.
Local time
Yesterday, 23:52
Joined
Mar 23, 2007
Messages
2,343
Got it!

Thanks to all who answered. I eventually went with the solution proposed by theDBguy, but IslaDogs' code did tell me I was going in the right direction (steps outlined below, in case it helps anyone else).

I feel a little stupid for not thinking of it myself, but that's the benefit of a forum like this.

Process
1. Clear a local table

2. Load the results of the keyword search into the local table.

3. Cycle through all the keywords selected by the user and for each run the following SQL:
Code:
UPDATE {local table} SET Synopsis = Replace([Synopsis],"{[I]keyword[/I]}","<b><font color=red size = 4>{[I]keyword[/I]}</font></b>");

4. Open the report as normal. The selected keywords will be in red and in a slightly larger font. Note: As already mentioned by various people, make sure the text field that will display the new text is RTF.

This method has the added advantage of allowing me to 'rank' keywords by colour. At present, all are red, but this is currently being discussed by management. I simply need to add different colours via different queries if required e.g.
Code:
UPDATE {local table} SET Synopsis = Replace([Synopsis],"{keyword1}","<b><font color=red size = 4>{keyword1}</font></b>");

UPDATE {local table} SET Synopsis = Replace([Synopsis],"{keyword2}","<b><font color=blue size = 4>{keyword2}</font></b>");

UPDATE {local table} SET Synopsis = Replace([Synopsis],"{keyword3}","<b><font color=yellow size = 4>{keyword3}</font></b>");
Note: The above is just for illustration, I'd have to check if 'yellow' and 'blue' work as colour names.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 20:52
Joined
Oct 29, 2018
Messages
13,210
Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.
 

Users who are viewing this thread

Top Bottom