Full Version: Query doesnt look at value of text box until it loses focus
UtterAccess Forums > Microsoft® Access > Access Forms
Speedy
Hi everyone,
Hope someone who knows Access a lot better than I can help.
My setup:
I have a form with a listbox (containing people's name), and a text box. When I type data in the text box, I want it to filter the content of the list box. (eg When I type "smith" in the text box, I want to only see people with names starting with "smith")
I did this in VBA code by changing the SQL statement in the listbox's rowsource and that worked, but I need to add lots of other types of filters as well and this is getting too cumbersome.
I want to use queries and just change the rowsource to point to different queries.
Anyway, I have the query setup to have the following line in the criteria row of the [LastName] column:
Like [Forms]![frmResidentSummary-Details]![txtFilter] & "*"
This works really well, except, Access only looks at the contents of my text box (txtFilter) when I exit the field on the form.
When I type "smi" in the text box, my list box doesnt change. If I click out of the form onto a different control, my list box changes to show me all people with names starting with "smi"
My code for setting the rowsource for the list box (and then requerying the listbox) is triggered from the "change" event on my text box. I know the code if triggering OK, but the Access query seems not to look for the value of the text box until after it loses focus. I've tried using the .text and .value properties in my query, but no good.
Can anyone shed some light on this?
Thanks
Jon
jmcwk
Jon,
That does the code look like in the Change Event of your Control ?
fkegley
You should use the Text property of the text box, the code needs to be in the Change event of the text box, I don't understand why it's not working. It may be, though, that the Query cannot "dig down" to get the Text property. At any rate, I generally do this kind of thing with code in the appropriate event, like this:
e.NameOfListBox.RowSource = "SELECT field-list FROM Table-Name WHERE field-name Like '" & [Forms]![frmResidentSummary-Details]![txtFilter].Text & "*'"
The Like statement in your post is using the Value property of the text box, therefore, its contents are not "set" until the text box is exited in some way. The Text property is the one to use while you are keying into the text box.
Speedy
Thanks for the replies. Much appreciated.
John: My code in the Change Event just checks what type of filtering I am doing, and sets the list box`s rowsource to the relevant query. I have a combobox allowing the user to filter by Name, or Treaty Number and will be adding more options to filter things like `people with no photographs, etc, etc`. I have only done All (ie no filter) and filter by Name. The code is:
-------------------------------------------------------------------------------------------
Select Case Me.cboFilter
Case 1
If Me.chkActiveOnly Then
Me.lstResidents.RowSource = "qryResidentSummaryListBox-AllFilter-ActiveOnly"
Else
Me.lstResidents.RowSource = "qryResidentSummaryListBox-AllFilter-ActiveAndInactive"
End If
Case 2
If Me.chkActiveOnly Then
Me.lstResidents.RowSource = "qryResidentSummaryListBox-LastNameFilter-ActiveOnly"
Else
Me.lstResidents.RowSource = "qryResidentSummaryListBox-LastNameFilter-ActiveAndInactive"
End If
End Select
Me.lstResidents.Requery
--------------------------------------------------------------------------------------------
Odid get this working but it`s a bit of a bodge, and I dont like it. In the Change Event for the text box, I added the following code BEFORE the above code:
-------------------------------------------------------------------------------------------
Me.lstResidents.SetFocus
Me.txtFilter.SetFocus
Me.txtFilter.SelStart = 999
-------------------------------------------------------------------------------------------
This makes the form work correctly as we shift focus away from the text box and than back again but it`s not a very clean way to do things. Can I get your opinions on this
Again, thanks to John and Frank for the assistance....
Thanks
Jon
jmcwk
Jon,
Can you attach a zipped copy of your DB with no sensitive data and no larger than 500 KB ?
Instructions are Here if you need guidance
Jack Cowley
In your first post you state, "...I need to add lots of other types of filters as well and this is getting too cumbersome." If you want to filter by no selections or any combination of selections then I would recommend you build a search form based on the method you will find in this article. It is very powerful and very flexible...
My 3 cents worth...
Jack
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.