MS Access filter as you type in record set

Hi there,

I am trying to achieve similar results as in post but using a continous form as opposed to a list box. Have had a look at Allen Brownes modules but feel that he has overdone it and think the code in the above post is cleaner.

My continous form only displays a persons name and need to filter as you type in a text box and not a combo box?

Struggling on how to make it read through a dataset not interested in a list box? Your help is much appreciated
Who is Participating?
Dale FyeCommented:
use the change event of your textbox to filter the dataset. The following code would filter your continuous form based on text entered in a control with the name "txt_Find".  It would identify any name where the lastname  has the text you entered in your search textbox anywhere within the name.

Private Sub txt_Find_Change()

    if len(me.txt_Find.text & "") = 0 then
        me.filteron = false
        me.Filter = "[LastName] Like '*" & me.txt_Find.text & "*'"
        me.filteron = true
    end if

End Sub

If you only want those that start with the search string, try:

        me.Filter = "[LastName] Like '" & me.txt_Find.text & "*'"
Jeffrey CoachmanMIS LiasonCommented:
Again, is your luck day...

Now, to get the count of the matches add this code:

    With Me.RecordsetClone
        If .RecordCount > 0 Then
            txtCountMatches = .RecordCount
        End If
    End With


Jeffrey CoachmanMIS LiasonCommented:

1. I don't see the need to requery the Company Name textbox on the after update event of the search box...

2. The way your interface is now, it introduces an issue of having only the "Single Typed in Character" as the only criteria.

In other words, you cant simply type "C" for all values that contain "C",
...Then type an "O" immediately afterwards (Meaning now "CO" is entered in the search box) and find all values containing "CO".

In your current design, typing "C" will show all text with "C"
Immediately typing "O" after the "C" will clear the "C" criteria and replace it with "O".
Thus you can't just type in "CO" (what the user will probably "Expect"), and get all text containing "CO".

You can try something like this (or some variation of it):
Private Sub txtSearch_Enter()
    Me.txtSearch.SelStart = Nz(Len(txtSearch))
End Sub
(As is listed in the sample I posted in the link you posted)

But this may cause other issues with trying to enter spaces-bar characters, clearing the search (search box will lose the focus), and entering text for which there are no matches.

Finally doing this will trigger the code for each search string, so as your list grows, this will slow down your searches...


This all being said, ...this is why this seemingly slick "Dynamic" search is not commonly used.
It can cause too many interface issue that may have to be worked around.
(This is probably why this works better with a simple short list in a list box than in a continuous form)

Most of the time, entering your full search term then triggering your "Filter" code is all that is needed.


Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

databarracksAuthor Commented:
Cheers Jeff trying your code now
databarracksAuthor Commented:
Hi jeff most of the code seems to be working the way you described and most importantly the way I want it. However was wondering why my new [txt_Find] text box doesn't continue to display what I had previously typed. It is behaving exactly the way you described it would but have removed all after update features as suggested.

I  believe that the way it is now may confuse the user if they don't see the full string of what they have been typing is?

I have attached the work I have done again and want you to see why it is doing this? Thanks for your help again
Jeffrey CoachmanMIS LiasonCommented:
Just so we are clear,...
fyed posted the actual code that answers your direct question as posted.
So if you keep the current design, he should be given most of the credit (Points)

I simply posted the code to do the counts, as I saw that you had this set up to act like the sample I posted in the referenced link.

<I  believe that the way it is now may confuse the user if they don't see the full string of what they have been typing is?>
Again, this is the issue with doing "Cool, slick", interfaces like this. (Dynamic searching)
As I stated, this is not commonly done for all the reasons I stated above, did you read my comments?

I played around with this for a while and each "fix" introduced another issue.

If you want this to function like the sample in the link, then the simplest method may just be to use a listbox and a combobox,(Just like in the sample), not a continuous form with a textbox.
Since your form is simple, I don't see anything that a a continuous form with a textbox can do that a listbox/combobox cannot.
In fact, with a listbox you can select multiple, non-contiguous values from a listbox. (This is not possible with a continuous form.)
sample attached

All this being fair, perhaps fyed has a way of tweaking this to work the way you want, while maintaining the present design.

Dale FyeCommented:

Really.  I provided a couple of lines of code, you've responded to virtually all of the posts and provided gobs (is that a word) of code examples.  From the looks of it you even had the time to look at the OPs code and make specific recommendations.  Unfortunately, I'm unable to download other databases at the moment.

I tend to concur with you that using the change event can become very tedious with large recordsets, and I generally use a textbox and a filter command button to do my filtering, unless the dataset is relatively small.
databarracksAuthor Commented:
Ok could you please tell me who I need to award the points to then :)
databarracksAuthor Commented:
If I use a list box I could have over 700 ompany names when I have mfinished with my import? Will that still work?
Dale FyeCommented:
Award points to those that helped based upon how much their contribution helped you solve your problem.

Depending on where you data sits (your PC, network, ...) and speeds, the Change event code should work fine for a data set of 700 records or less.

databarracksAuthor Commented:
Ok then I will proceed to award points accordingly
databarracksAuthor Commented:
Both experts were very helpful and it is a shame that I couldn't necessarily achieve what I had exactly wanted, however this is by no means a reflection on the great help I got from the experts
Jeffrey CoachmanMIS LiasonCommented:
An even points split is fine with me.

My point was that fyed actually answered your question as it was posted.

I post comments in the interest of helping out.
If something I posted helps resolve the issue, then great.

My concern was that fyed get the "Accepted Solution" with a majority of the points.

But since we are all being so courteous here, an even split is fine with me...

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.