MS Access filter as you type in record set

Posted on 2011-03-21
Last Modified: 2013-11-27
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
Question by:databarracks
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 3
LVL 47

Accepted Solution

Dale Fye (Access MVP) earned 125 total points
ID: 35183111
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 & "*'"
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 125 total points
ID: 35184151
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


LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35184431

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.


Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.


Author Comment

ID: 35185221
Cheers Jeff trying your code now

Author Comment

ID: 35187754
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
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35190177
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.

LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 35190382

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.

Author Comment

ID: 35190552
Ok could you please tell me who I need to award the points to then :)

Author Comment

ID: 35190568
If I use a list box I could have over 700 ompany names when I have mfinished with my import? Will that still work?
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 35190626
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.


Author Comment

ID: 35190703
Ok then I will proceed to award points accordingly

Author Closing Comment

ID: 35190733
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
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35190837
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...


Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question