Spaces in On Change event Search Box and Blank Row

[Pianopizza5]

Show what you are passing in.


Plus learn to walk your code with F8 and breakpoints.

I am passing the fields [txtFoodItem] - strText; [txtSearch] - strSearch into the function.
Function: HighTest (strText As String, strSearch As String)

[txtFoodItem] is the records, [txtSearch] is the searchbox

The formula on the form text object is:

=IIf(IsNull([txtSearch]), [txtFoodItem],HighTest([txtFoodItem],[txtSearch])

[Welshgasman]

See what @cj_london advised.

[Pianopizza5]

just realised this is the onchange event - so the control will not have been updated, You need to use strSearch.text

Interesting. Did not know that. Where would you place that in the VBA? I keep getting the "Invalid Qualifier Error" when I add ".text" to strSearch.

Thank you for helping me.

[Pianopizza5]

What happens now is the function runs when text is entered in the search box, but no results appear. (And there is a #Type error in the last record). Do we think this code is a viable solution? I'm open to anything.

[Welshgasman]

What happens now is the function runs when text is entered in the search box, but no results appear. (And there is a #Type error in the last record). Do we think this code is a viable solution? I'm open to anything.

Well you have to assign the result of that function back to your control?

[Pianopizza5]

Got it! (at the moment)

Needed to set HighTest = strTest.

[Pianopizza5]

Thank you all!

Now does anyone have any thoughts on why I can't add spaces in my search box? Since the event is On Current, the cursor resets after every stroke. I tried using Sel, but that doesn't account for spaces.

[jojowhite]

see again you db, you can add space to the search.

[Pianopizza5]

One more quick follow-up, in order for the highlight formula to work, I have to set the property Form – Data – Allow Additions to “Yes” (otherwise, the reference error appears (run-time 2185) when searching for an item). The main problem with this is that “!Type#” appears in the field that contains the formula in the “new record” at the bottom.

I thought this might be because of the null value in the “new record”, so I inserted the Nz formula. I also tried adding default values for the new records, but didn’t have any luck.

Here is the current formula in the control source:

=IIf(IsNull([txtSearch]),[txtFoodItem],HighTest([txtFoodItem],[txtSearch])

And the Highest function is:

Function HighTest(strText As String, strSearch As String)
 
Dim strTemp As String
Dim strPhrase As String
 
Const strcTagStart = "<font color=red>"
Const strcTagEnd = "</font>"
 
 
strTemp = PlainText(strText) 'to remove any formatting
strPhrase = Mid(strTemp, InStr(strTemp, strSearch), Len(strSearch))
strTemp = Replace(strTemp, strPhrase, strcTagStart & strPhrase & strcTagEnd, , 1)
strText = Replace(strTemp, vbCrLf, "<br>")
 
HighTest = strText
 
End Function

Any thoughts? Thanks.

[Welshgasman]

I can get the highlight not hightest to work even with Additions = No?

[Pianopizza5]

Interesting, glad it worked for you. I’m not having any luck though. If the additions is set to “No,” the error comes up when you are searching for an item that is not in the records. It has to deal with there being no records (since the txtsearch is being used to filter the query).

[Welshgasman]

Ah, you never said when nothing was found.
So I just tried for something that is not there. Still no error?
Do not know what to say?

[Pianopizza5]

No worries. Thanks for trying.

I attached my db to show you/whoever else sees this. It currently has the Form - Add Additions set to "No." The error appears when you search for an item that is not in the list. For example, type "ag."

The error goes away by setting "Add Additions" to "Yes." But, then we get the "#Type!" result in the new record, which I don't want.SearchNoAddError.accdb.zip

[Welshgasman]

Well as I said, it does NOT do that for me, so hard to debug?
Would be interested to see if it happens for anyone else who decides to try it.

[Pianopizza5]

Very interesting! I looked into it more and the default value doesn't carry over when there is a formula and multiple tables in the query. I'm going to start a new thread because my original question was solved.

Thank you for you help.