Highlighted Search (1 Viewer)

Valentine

Member
Local time
Today, 12:10
Joined
Oct 1, 2021
Messages
261
I have the following code in my database that searches through all my records for certain keywords and prints out a report that has each record that has the keyword in it. I want to highlight the keyword in the report to make it easier to find in each record. Is this possible?
Code:
Dim dbCurr As DAO.Database
Dim qdfCurr As DAO.QueryDef
Dim rsSearch As DAO.Recordset
Dim strSearch As String
Dim sqlStr As String
Dim reportName As String
Dim queryName As String

reportName = "Search Keyword Report"
queryName = "Search Keyword Query"

If IsNull(Me.txtNarrativeKeySrch) Or Me.txtNarrativeKeySrch = "" Then
    MsgBox "Please type in your keyword.", vbOKOnly, "Keyword Needed"
    Me.txtNarrativeKeySrch.Backcolor = vbYellow
    Me.txtNarrativeKeySrch.SetFocus
Else
    strSearch = Me.txtNarrativeKeySrch.Value
    Set dbCurr = CurrentDb()
    Set qdfCurr = dbCurr.QueryDefs(queryName)
    sqlStr = "SELECT [Cnf].* " _
           & "FROM [Cnf] " _
           & "WHERE ([Cnf].[Operational Gap] Like '*" & strSearch & "*' OR " _
           & "[Cnf].[Needed Capability] Like '*" & strSearch & "*' OR " _
           & "[Cnf].[OP Impact if Unfulfilled] Like '*" & strSearch & "*' OR " _
           & "[Cnf].[Potential Solutions] Like '*" & strSearch & "*' OR " _
           & "[Cnf].[Prior Resolution Attempts] Like '*" & strSearch & "*' OR " _
           & "[Cnf].[Need By Narrative] Like '*" & strSearch & "*' OR " _
           & "[Cnf].[Required Use Narrative] Like '*" & strSearch & "*' OR " _
           & "[Cnf].[LTCON Narrative] Like '*" & strSearch & "*' OR " _
           & "[Cnf].[Title] Like '*" & strSearch & "*' OR " _
           & "[Cnf].[J38RQ Notes] Like '*" & strSearch & "*' OR " _
           & "[Cnf].[FARWG Notes] Like '*" & strSearch & "*' OR " _
           & "EXISTS (SELECT [CnfToAdditionalDetail].[Additional Detail], [CNF ID#] " _
           & "FROM [Cnf] LEFT JOIN [CnfToAdditionalDetail] ON [Cnf].[CNF ID#] = [CnfToAdditional Details].[CNF ID#] " _
           & "WHERE ([CnfToAdditionalDetail].[AdditionalDetails] Like '*" & strSearch & "*' AND NOT EXISTS (" _
           & "SELECT [Cnf].* " _
           & "FROM [Cnf] " _
           & "WHERE ([Cnf].[Operational Gap] Like '*" & strSearch & "*' OR [Cnf].[Needed Capability] Like '*" & strSearch & "*' OR " _
           & "[Cnf].[OP Impact if Unfulfilled] Like '*" & strSearch & "*' OR [Cnf].[Potential Solutions] Like '*" & strSearch & "*' OR " _
           & "[Cnf].[Prior Resolution Attempts] Like '*" & strSearch & "*' OR [Cnf].[Need By Narrative] Like '*" & strSearch & "*' OR " _
           & "[Cnf].[Required Use Narrative] Like '*" & strSearch & "*' OR [Cnf].[LTCON Narrative] Like '*" & strSearch & "*' OR " _
           & "[Cnf].[Title] Like '*" & strSearch & "*' OR [Cnf].[J38RQ Notes] Like '*" & strSearch & "*' OR " _
           & "[Cnf].[FARWG Notes] Like '*" & strSearch & "*' " _
           & "))))) "
    Me.txtNarrativeKeySrch.BackColor = VbWhite
    Me.txtNarrativeKeySrch.Value = ""
    qdfCurr.SQL = sqlStr
    DoCmd.OpenReport reportName, acViewReport, queryName, , acWindowNormal
    qdfCurr.Close
    Set qdfCurr = Nothing
    Set dbCurr = Nothing
End If
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:10
Joined
Oct 29, 2018
Messages
21,358
Highlight the row or just the keyword? If just the keyword, I think you'll only be able to do that if the field is a Memo with RTF.
 

Valentine

Member
Local time
Today, 12:10
Joined
Oct 1, 2021
Messages
261
I was hoping to highlight just the keyword.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:10
Joined
Oct 29, 2018
Messages
21,358
I am sorry your sentence is a bunch of gibberish to me LoL.
The fields you're searching through, what are their data types? Are they all Long Text fields?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:10
Joined
Oct 29, 2018
Messages
21,358
All except the Title field which is a short text
Okay, so I am saying you won't be able to highlight the Title field. Now, to the next step. Check the Properties of all your Long Text field and make sure they are set as Rich Text in the Text Format property.

PS. As I said earlier, I am not even sure it's possible with a Long Text fields, because I don't know what is the rich text code for highlighting. Otherwise, I know the code to make the text bold and red.
 

Valentine

Member
Local time
Today, 12:10
Joined
Oct 1, 2021
Messages
261
I can take the title out of the keyword search, Yes every field is rich text.
 

Eugene-LS

Registered User.
Local time
Today, 19:10
Joined
Dec 7, 2018
Messages
481
I want to highlight the keyword in the report to make it easier to find in each record. Is this possible?
Yes, it's possible.
Make report in RTF or HTML format ... using tags <bold> & <color>
You can also highlight the desired text when exporting the report to MS Word or MS Excel
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:10
Joined
Oct 29, 2018
Messages
21,358
Yes, it's possible.
Make report in RTF or HTML format ... using tags <bold> & <color>
You can also highlight the desired text when exporting the report to MS Word or MS Excel
There you have it. But when I hear "highlight," I think of the background color. If so, I'm not sure how to do that.
 

Valentine

Member
Local time
Today, 12:10
Joined
Oct 1, 2021
Messages
261
Yes, it's possible.
Make report in RTF or HTML format ... using tags <bold> & <color>
You can also highlight the desired text when exporting the report to MS Word or MS Excel
Ok so the report is in rich text already. I am not exporting it to word or excel it is just visible in access and the users scroll down and read the report. is what your saying something i can add to the button code for the search so it auto highlights or is this a fix that I have to implement to the report itself after the search has been generated?
 

Valentine

Member
Local time
Today, 12:10
Joined
Oct 1, 2021
Messages
261
After reading a bunch of google and the responses here looks like if i want to have the searched text highlighted my best bet is to open the report in Word. What would that look like? Could I keep the code i have or do i have to rewrite everything?
 

mike60smart

Registered User.
Local time
Today, 16:10
Joined
Aug 6, 2017
Messages
1,899
After reading a bunch of google and the responses here looks like if i want to have the searched text highlighted my best bet is to open the report in Word. What would that look like? Could I keep the code i have or do i have to rewrite everything?
Open your Report in Print Preview and on the Ribbon there is an Icon for More - Click on this Icon and then select the Export the selected Object to Rich Text
 

Valentine

Member
Local time
Today, 12:10
Joined
Oct 1, 2021
Messages
261
so thats how to manually change it. I was looking for a VBA option to add it to my code for the search.

Jdraw, that is a continuous form, what I am trying to do is create a report from a form input.
 

jdraw

Super Moderator
Staff member
Local time
Today, 12:10
Joined
Jan 23, 2006
Messages
15,364
Well if it can't be adapted easily, then you might output the report to Word, and use Word's search command...

1653497860262.png
 

Users who are viewing this thread

Top Bottom