Why double highlight text fails? (1 Viewer)

[KitaYama]

I've had this problem for quite several years. But never asked it.
Now that there's another thread on this subject I think it's time to learn a little bit more.

A sample file is attached.
In the form that opens, there's 2 text boxes. Type a letter or more ONLY in the left text box and hit the enter.
The form is filtered and highlights the keyword.
Try it several time to see if it works just fine. (only the left text box)

Now close the form and open it again.
And do the same test ONLY for the right textbox.
It works. nothing wrong.

But if you use both textboxes to filter the form, only one of them highlights the keyword.

Can anyone explain why both textboxes can not highlight the keyword at the same time?
Thanks.

 

[Edgar_]

When I was writing my code for that other thread, I noticed that changing the form's RecordSource property modifies the textboxes' TextFormat property. That's probably what is happening here.

Anyway, you can fix it by changing the property again to rich text, so, this should work:

Private Sub srchFld2_AfterUpdate()
    FilterMe
    Me.txtFld1.TextFormat = acTextFormatHTMLRichText
    Me.txtFld2.TextFormat = acTextFormatHTMLRichText
End Sub

 

[xavier.batlle]

I've had this problem for quite several years. But never asked it.


A sample file is attached.
In the form that opens, there's 2 text boxes. Type a letter or more ONLY in the left text box and hit the enter.
The form is filtered and highlights the keyword.
Try it several time to see if it works just fine. (only the left text box)

Can anyone explain why both textboxes can not highlight the keyword at the same time?
Thanks.

If you change the fld1 and fld2 type to Longtext in the table tbl your database example works properly:

 

[Edgar_]

If you change the fld1 and fld2 type to Longtext in the table tbl your database example works properly:

That change is problematic, though. It's the reason behind the solution given in this post of the other thread. I don't think changing your backend columns for a frontend feature is a good idea.

 

[KitaYama]

If you change the fld1 and fld2 type to Longtext in the table tbl your database example works properly:
View attachment 116204

BE is sql server. I can not change that in table design view because the table is a linked table.

 

[arnelgp]

see also the problem of using Replace() function i mentioned on post#5 this link.

 

[KitaYama]

When I was writing my code for that other thread, I noticed that changing the form's RecordSource property modifies the textboxes' TextFormat property. That's probably what is happening here.

Anyway, you can fix it by changing the property again to rich text, so, this should work:

Private Sub srchFld2_AfterUpdate()
    FilterMe
    Me.txtFld1.TextFormat = acTextFormatHTMLRichText
    Me.txtFld2.TextFormat = acTextFormatHTMLRichText
End Sub

That gave me an error when only one textbox was used to apply the filter.
It seems that
I had to change the code to :

    If InStr(Fltr, "Fld1") Then
        Me.txtFld1.TextFormat = acTextFormatHTMLRichText
    End If
    If InStr(Fltr, "Fld2") Then
        Me.txtFld2.TextFormat = acTextFormatHTMLRichText
    End If

This seems to solve the problem and I hope I can use it in my db.
The actual code does several other tasks and is used as a generic public function to be re-used.

I found a comment in my db that went back to 10 years ago, where I had asked myself to check why this problems happens.
And never bothered to check, up to now.
Thanks for solving an old problem.

 

[KitaYama]

I noticed that changing the form's RecordSource property modifies the textboxes' TextFormat property.

@Edgar_
Out of curiosity, Why it happens only for the second text box and not both?

 

[KitaYama]

If it helps you to check what's going wrong here, it's how I managed to bypass this situation 10 years ago when I first faced this.

I added two texboxes on top of each other, one with a control source to the field in the table (Plain text), and another one with empty control source above it (rich text).
After filtering, and adding the html tag to the content, I hide the plain textboxes and show the text boxes with rich text.
It's exactly like the sample in #1, but using 4 set of textboxes.

This is the control source of both:

=IIf([Odr] Is Null, Null, Replace([Odr], "25", "<font color=""red"">25</font>"))
=IIf([Dr] Is Null, Null, Replace([Dr], "20040", "<font color=""red"">20040</font>"))

The only difference that I see is that Dr & Odr are actual textboxes in the form, not fields from record source.
My sample #1 uses fields from record source instead of controls on the form.

 

[KitaYama]

see also the problem of using Replace() function i mentioned on post#5 this link.

Yes, I had noticed that problem.
But since we are following JIS, we are safe for this method.
According to JIS, all documents that ends up to a drawing should be all upper case.
The data in table is all in upper case, and in the code I change ucase to bypass user's input.

SearchDisplay = "=IIf(" & strField & " Is Null, Null, " & _
"Replace(" & strField2 & ", """ & Val & """, """ & strcTagStart & UCase(Val) & strcTagEnd & """))"

 

[KitaYama]

The more I test, the more I think of the possibility of this being a bug.
It makes no sense only adding a second control source, changes the textbox to Plain text.

 

[CJ_London]

might be an idea to use an ado recordset where you set the two fields to longvarchar

This is my version of your FilterMe function

rivate Function FilterMe2()
Dim rsA As Object
Dim rs As DAO.Recordset

Dim Fltr As String

    'create ado recordset and populate with filtered records
    Set rsA = CreateObject("ADODB.Recordset")
   
    With rsA
        .Fields.Append "PK", 3 'adInteger
        .Fields.Append "Fld1", 201, 510 'adLongVarChar
        .Fields.Append "Fld2", 201, 510 'adLongVarChar
       
        .CursorLocation = 3 'adUseClient
        .LockType = 3 'adLockOptimistic
        .CursorType = 3 'adOpenStatic
           
        .Open
       
   
        If Not srchFld1 & "" = "" Then
            Fltr = " AND Fld1 like '*" & srchFld1 & "*'"
        End If
      
       
        If Not srchFld2 & "" = "" Then
            Fltr = Fltr & " AND Fld2 like '*" & srchFld2 & "*'"
        End If
       
       
        If Fltr = "" Then
            Fltr = "PK=0"
        Else
            Fltr = Mid(Fltr, 6)
        End If
       
        Set rs = CurrentDb.OpenRecordset("SELECT * FROM tbl WHERE " & Fltr)
        While Not rs.EOF
            .AddNew
            !PK = rs!PK
            !fld1 = rs!fld1
            !fld2 = rs!fld2
            rs.MoveNext
            .Update
        Wend
       
    End With
   
    Set Recordset = rsA
   
    'set formatting
    setFormsHtmlTag Me, "Fld1", srchFld1
    setFormsHtmlTag Me, "Fld2", srchFld2
   
End Function

depends how many records you have but might be better to load all the records into the ado recordset, then just filter that and apply the formatting - something like this

Private Function FilterMe3()
Static rsA As Object
Dim rs As DAO.Recordset

Dim Fltr As String

    'create ado recordset - all records
    If rsA Is Nothing Then
        Set rsA = CreateObject("ADODB.Recordset")
   
        With rsA
            .Fields.Append "PK", 3 'adInteger
            .Fields.Append "Fld1", 201, 510 'adLongVarChar
            .Fields.Append "Fld2", 201, 510 'adLongVarChar
           
            .CursorLocation = 3 'adUseClient
            .LockType = 3 'adLockOptimistic
            .CursorType = 3 'adOpenStatic
               
            .Open
       
            Set rs = CurrentDb.OpenRecordset("SELECT * FROM tbl")
            While Not rs.EOF
                .AddNew
                !PK = rs!PK
                !fld1 = rs!fld1
                !fld2 = rs!fld2
                rs.MoveNext
                .Update
            Wend
       
        End With
   
    End If
   
    'create and apply filter string
    rsA.Filter = ""
    If Not srchFld1 & "" = "" Then
        Fltr = " AND Fld1 like '*" & srchFld1 & "*'"
    End If
 
   
    If Not srchFld2 & "" = "" Then
        Fltr = Fltr & " AND Fld2 like '*" & srchFld2 & "*'"
    End If
   
   
    If Fltr = "" Then
        Fltr = "PK=0"
    Else
        Fltr = Mid(Fltr, 6)
    End If
   
    rsA.Filter = Fltr
    Set Recordset = rsA
   
    'modify the controlsource
    setFormsHtmlTag Me, "Fld1", srchFld1
    setFormsHtmlTag Me, "Fld2", srchFld2
   
End Function

 

[MarkK]

Here's another way, where the ControlSource is fixed at design time...

It makes no sense only adding a second control source, changes the textbox to Plain text.

It makes sense in this regard: changing the control source causes the system to query the attributes of the underlying field, and if it does not support rich text, the system assumes the control should not either. In this sense, though it is not desirable for your particular task, it is predictable.

 

[CJ_London]

or modify your original function to clear the controlsource, set the textformat and then apply the formula

Private Function FilterMe()
    
    Dim Fltr As String
    
    
    If Not srchFld1 & "" = "" Then
        Fltr = " AND Fld1 like '*" & srchFld1 & "*'"
    End If
    
    
    If Not srchFld2 & "" = "" Then
        Fltr = Fltr & " AND Fld2 like '*" & srchFld2 & "*'"
    End If
    
    
    If Fltr = "" Then
        Fltr = "PK=0"
    Else
        Fltr = Mid(Fltr, 6)
    End If
    
    Me.RecordSource = "SELECT * FROM tbl WHERE " & Fltr
    
    txtFld1.ControlSource = ""
    txtFld1.TextFormat = acTextFormatHTMLRichText
    setFormsHtmlTag Me, "Fld1", srchFld1
    
    txtFld2.ControlSource = ""
    txtFld2.TextFormat = acTextFormatHTMLRichText
    setFormsHtmlTag Me, "Fld2", srchFld2
    
End Function

 

[Edgar_]

@Edgar_
Out of curiosity, Why it happens only for the second text box and not both?

View attachment 116205

1. Initially, the form has no RecordSource.
2. When you provide a RecordSource using the FilterMe function, the form assigns a ControlSource to your textboxes with the help of the setFormsHtmlTag function.
3. In your original setFormsHtmlTag routine, the ControlSource for the textbox is set using a Replace function that includes HTML tags. However, for the other control, it simply assigns the field name as the ControlSource without incorporating any HTML tags.
4. Since both textboxes are bound to a Short Text field, the textbox without HTML tags reverts to plain text, while the one with HTML tags remains as rich text, even though both are based on Short Text fields. So, in appearance, this is a textbox behavior.

Therefore, if we include HTML tags for the two textboxes using the setFormsHtmlTag routine, like this:

Public Sub setFormsHtmlTag(frm As Form, Ctrl As String, txt As Variant)
 
    Dim SearchDisplay As String
    Const strcTagStart = "<font color=#FF0000>"
    Const strcTagEnd = "</font>"
 
    With frm
        If txt & "" = "" Then
            SearchDisplay = "=""<html>"" & " & Ctrl & " & ""</html>"""
        Else
            SearchDisplay = "=Replace(" & Ctrl & ", '" & txt & "', '" & strcTagStart & txt & strcTagEnd & "')"
        End If
        .Controls("txt" & Ctrl).ControlSource = SearchDisplay
    End With
 
End Sub

We will retain the necessary behavior of the textbox. Additionally, we can change the TextFormat property of the textbox to override its behavior, as you did in a previous post. The end goal is to avoid modifying backend structures to ensure frontend functionality; instead, let the frontend handle the display of backend data.