Spaces in On Change event Search Box and Blank Row

[Pianopizza5]

I am creating a continuous search form and I am using an On Change event to have it update as the user types in the txtSearch. Unfortunately, I am having a couple of issues (I know that I could use the After Update event, but I want the user to see the potential results as they type)

Prevent a blank row from appearing at the bottom of the form without having the run-time 2185 error

1. To have no blank row - in the form properties, Form – Data – Allow Additions must be set to “No”
2. Receives the run-time error 2185: (you can’t reference a property or method for a control unless the control has focus) when search for item not in list

Ex. Search for “aa” in the Search Food Item box
Potential work around – set the focus on txtSearch – didn’t work.

txtSearch.SetFocus

3. To get no error - in the form properties, Form – Data – Allow Additions must be set to “Yes”
4. However, will have blank row
Ex. Search for “waffles” in the Search Food Item box

After user types in txtSearch, should be able to continue typing (and add spaces)

1. Because of the On Change event, the form requeries every time that a character is edited in the txtSearch.

2. This means that the text in txtSearch is selected.
3. To resolve this, I positioned the cursor to the end after every event.

txtSearch.SetFocus
txtSearch.SelStart = 500

4. The problem is that spaces are not recognized.
Ex. Search for “Apple Pie” in the Search Food Item box
The cursor does not advance beyond “apple”

If anyone has any thoughts, I’d really appreciate it. I saw some other posts similar, but was not able to get anywhere.

Thanks.

[Micron]

From what I see you're making it too complicated. If you're not going to have thousands of records in the form when it opens, just filter it based on the search term entered. No need to requery:
Me.Filter = "[FoodItem] LIKE '*" & Me.txtSearch.Text & "*'"
Me.FilterOn = True
No need to set focus to a control that already has focus because you're typing in it anyway. Remove that and maybe the extra row problem will go away when you set allow additions to no. However, you can't set focus to a disabled control so that could be at play. I've made too many changes to go back and figure out if that was involved in your coding. It may have been that the form was trying to activate a record and since the record only has one field and it's disabled, that would be a problem. Depending on the desired user experience, it may be enough to lock it. The weird thing about your form is that when it loads there are 10 records that I can't read, yet the control in the form seems to be formatted ok. BTW, if you filter you don't need the function either. If you will have too many records to start out with a full form, then it makes sense to start with no or a limited set of records, then start requerying.
EDIT - or open the form based on the search form that should come before the results form.
EDIT2 - I see the problem re: not seeing values in the record. Never would have occurred to me that the control would have been set to invisible. That makes no sense.

[Bulzie]

Is your search based on 1 column/field on the form? Why not set a combo box for that field and have Access do the continuous search for you? If you are looking to search for any word or text in that field, then do like Micron suggested, not sure why the user cares if the list changes based on entering a character, they can type in what they think and hit a button to search and bring back the records that match.

[CJ_London]

would help if you showed your code in the txtSearch change event

[Micron]

CJ, a db copy was posted.
The focus issue is a known bug. When allow additions is NO and a continuous form has no records, the unbound control loses its focus property value even though it is the Screen.ActiveControl. You can't even set focus in the immediate window (at least not in break mode). One solution is to allow additions (not that that makes sense). Another is to provide a value on a prior form and open the results form (already mentioned). Another might be to use MajP's class for FAYT.

[CJ_London]

I’m on my phone and can’t download and didn’t notice the link

[Micron]

NP. I just had an idea for another approach - use a key event to build a string and pass it to a variable. Then there should be no need to try and get the search textbox control value or text property value. Will give that a try.

[jojowhite]

do not use direct Replace() function.
If you type Capital letter, your field value will
be replaced with capital letter, which is annoying.

[jojowhite]

i introduced another hidden textbox (txtFilter) and use it's value to filter the query.
the value of this textbox comes from the Text property of txtSearch.
on the old filter (txtSearch), the trailing space is being removed, txtFilter
preserves the trailing space.

[Pianopizza5]

That's great, thanks! Could you please paste the code? Thank you so much for taking the time to look into this.

I like the idea. But I tried it and didn't have much success.

First, I created a “pass test” using a button and another text box and it does carry over the spaces, which is exciting (it works because it doesn’t have the On Current event, which a big reason for the problem, but this is how I need the db to behave.)

Then, I applied this logic to the txtSearch. The first problem was that the most recent character type was not passed. For example:

txtSearch: “a” txtFilter: “”
txtSearch: “ab” txtFilter: “a”

Furthermore, I had to set the focus to txtFilter before running the filter because the “reference error: 2185” was appearing. Then, I want the focus back on the txtSearch.

This gets back to the root, the cursor is set to position: 0…

txtFilter = txtSearch
 
txtFilter.SetFocus
 
Me.Filter = "[FoodItem] like '*" & Me.txtFilter.Text & "*'"
 
Me.FilterOn = True

txtSearch.SetFocus

SearchWSpacesExamplePassValue.accdb.zip

[Pianopizza5]

Yes! 100% agree about the caps. This is another thing that I wanted to fix. Can you please paste the code to use instead of Replace? Thank you!

[Pianopizza5]

Hmm, please let me know how this goes! Definitely open to anything and want to learn. Thank you so much for taking the time to look into this and helping me.

[Pianopizza5]

That's a good idea! Unfortunately, a combo box wouldn't be the best for this application because the user is searching for terms within paragraphs. I am using the on current because I want the results to filter as they type, as they may not know the exact terms (otherwise I could use an after update). The db attached is a simplified example to target the issue that I'm having.

Thank you so much for your time.

[jojowhite]

here try your formsearch (not the vba).

[Pianopizza5]

That’s great idea! I didn’t know that you could filter the query via VBA. That’s actually a game changer, because then you can utilize the query in multiple forms/reports and have them behave differently.

I set up the form based on the VBA filter. The problem now is that the cursor jumps to position 0 after the action.

*EDIT – do you mean have the “results” in a subform and the txtSearch in the main form? I tried that, but didn’t have any luck. I remember that’s how I originally set up the db, but was having trouble with the on current event.
(Really, the on update event would make things so much easier, but it’s not the best for the user experience.)
*EDIT 2 – hmm, which control did you see that is set to invisible?SearchWSpacesExampleVBAFilter.accdb.zip