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
  • 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.



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.

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
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…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
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 …

947 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now