Highlighted Search

V

Valentine

Member
Local time
Today, 14:49
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
 
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.
 
I was hoping to highlight just the keyword.
 
Valentine said:
I am sorry your sentence is a bunch of gibberish to me LoL.
Click to expand...
The fields you're searching through, what are their data types? Are they all Long Text fields?
 
Valentine said:
All except the Title field which is a short text
Click to expand...
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.
 
I can take the title out of the keyword search, Yes every field is rich text.
 
Valentine said:
I want to highlight the keyword in the report to make it easier to find in each record. Is this possible?
Click to expand...
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
 
Eugene-LS said:
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
Click to expand...
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.
 
Eugene-LS said:
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
Click to expand...
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?
 
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?
 
Valentine said:
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?
Click to expand...
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
 
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.
 
Well if it can't be adapted easily, then you might output the report to Word, and use Word's search command...

1653497860262.png
 
You must log in or register to reply here.

Similar threads

V
How do I RecordSet
2
Replies
22
Views
1,450
SHANEMAC51
SHANEMAC51
F
Solved Combobox search as you type issue
2
Replies
20
Views
2,219
moke123
moke123
A
Search through textbox
Replies
10
Views
737
MarkK
MarkK
M
Solved Syntex Error
Replies
12
Views
846
Josef P.
J
B
Retrieve 6 numbers from string
Replies
2
Views
462
arnelgp
arnelgp

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom