Solved Clearing text boxes on KeyDown with enter key (1 Viewer)

mikenyby

Member
Local time
Today, 11:35
Joined
Mar 30, 2022
Messages
85
Hi everybody!

I have a form with three unbound text boxes that are to be used as three different search functions. I have control buttons next to each text box that open a report based on parameters collected from the text boxes. I'd like the user to be able to just hit the enter key after entering their search parameters in the text box, and I've got a code that kinda works except it only works once. After "enter" is hit the first time, any subsequent search will return ALL records. Oddly enough, this doesn't affect the control buttons. They seem to still return only records matching the terms entered into the text box. The form is named "School Information Search". Here's what the form looks like:
1650917108449.png

Using the first text box and control button as an example, if you type the name of a school and click the "Search by School Name" control button, a macro runs to open a report. The report's linked query uses the following criteria under the field that is searched:

Like "*" & [Forms]![School Information Search]![SchoolNameSearchBar] & "*"

The macro resets the text box by SetProperty --> Value --> [left blank]. This button always works. No issues on subsequent searches.

If one types a search term and hits enter, the report is opened using the following code:

Private Sub SchoolNameSearchBar_KeyDown(KeyCode As Integer, Shift As Integer)
If KeyCode = vbKeyReturn Then
DoCmd.OpenReport "School Search by Name", acViewReport
End If
End Sub

The first time I attempted a search using this code it worked. But every subsequent attempt has opened a report showing ALL the records. I have no idea what is happening here.

I have an inkling that because the enter key moves to a new line in the text box, the report may be collecting its parameters using the blank new line. Does this make sense? Any possible fixes?

Thanks.

EDIT: Apparently it's not because of the new line in the text box. I just changed "Enter Key Behaviour" to Default and it still returns all records in the report.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:35
Joined
Oct 29, 2018
Messages
21,358
Hi. When the user enters a search term in the Textbox and hits the Enter key, they are essentially updating that Textbox. If so, I think it might be simpler to just use the Textbox AfterUpdate event, rather than trap the Enter key. This also gives you an added bonus to execute the search when they use the Tab key. Just a thought...
 

mikenyby

Member
Local time
Today, 11:35
Joined
Mar 30, 2022
Messages
85
Hi. When the user enters a search term in the Textbox and hits the Enter key, they are essentially updating that Textbox. If so, I think it might be simpler to just use the Textbox AfterUpdate event, rather than trap the Enter key. This also gives you an added bonus to execute the search when they use the Tab key. Just a thought...
DBGuy, you're my hero. I just created this macro and it works like a charm:
1650982568643.png

If only I realized it was this simple.

Additonal question, is there a way I can set the focus back to the SchoolNameSearchBar on the same macro after I've reset the value? As it is the focus moves to the next text box on the page when the enter key is pressed. If the enter key property is changed from default to "new line in field", the macro won't run. I tried adding the "GoToControl" action to the macro but I get an error message after the report opens:
1650982878986.png


It's probably something simple. I'm learning as I go here. Thanks for your help.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:35
Joined
Oct 29, 2018
Messages
21,358
DBGuy, you're my hero. I just created this macro and it works like a charm:
View attachment 100158
If only I realized it was this simple.

Additonal question, is there a way I can set the focus back to the SchoolNameSearchBar on the same macro after I've reset the value? As it is the focus moves to the next text box on the page when the enter key is pressed. If the enter key property is changed from default to "new line in field", the macro won't run. I tried adding the "GoToControl" action to the macro but I get an error message after the report opens:
View attachment 100162

It's probably something simple. I'm learning as I go here. Thanks for your help.
Hi. Glad to hear you got it sorted out. I am guessing at the completion of the AfterUpdate event, the rest of the jobs Access has to do gets done, including setting the focus to the next control in the Tab Order. As a workaround, I would ask you to try creating a very small unbound Textbox where you would send the focus to in the search box AfterUpdate event. Then, in the GotFocus event of that Textbox, send the focus back to the Search box.

Hope that helps...
 

mikenyby

Member
Local time
Today, 11:35
Joined
Mar 30, 2022
Messages
85
That works! It's tedious with all the control boxes I've got on this database, but it's a solution. Thanks again.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:35
Joined
Oct 29, 2018
Messages
21,358
That works! It's tedious with all the control boxes I've got on this database, but it's a solution. Thanks again.
Sometimes, we have to live with some limitations. Good luck with your project.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:35
Joined
Feb 19, 2002
Messages
42,970
Since you are searching for ONE of the options not a combination, you can simplify the interface by creating a single text box, an option group with four options - name, address, box, all and a button to run the process. The code uses a Case statement to analyze the option group value and builds a filter or where clause as appropriate. Then the button either applies the filter or sets the form's RecordSource query to include the where clause and requeries the form.
 

Users who are viewing this thread

Top Bottom