Solved

MS Access filter as you type in record set

Posted on 2011-03-21
13
1,311 Views
Last Modified: 2013-11-27
Hi there,

I am trying to achieve similar results as in post http://www.experts-exchange.com/Microsoft/Development/MS_Access/Access_Coding-Macros/Q_23752521.html 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
SampleExperts1.accdb
0
Comment
Question by:databarracks
  • 6
  • 4
  • 3
13 Comments
 
LVL 47

Accepted Solution

by:
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
    else
        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 & "*'"
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 125 total points
ID: 35184151
Again, ...today is your luck day...
;-)

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

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

;-)

JeffCoachman
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35184431
Notes:

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

Oy!

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.

;-)

JeffCoachman
0
 

Author Comment

by:databarracks
ID: 35185221
Cheers Jeff trying your code now
0
 

Author Comment

by:databarracks
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
SampleExperts1-1.accdb
0
 
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.

JeffCoachman
Access-EEQ2126901178DynamicFilte.mdb
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 35190382
Jeff,

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

Author Comment

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

Author Comment

by:databarracks
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?
0
 
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.

Dale
0
 

Author Comment

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

Author Closing Comment

by:databarracks
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
0
 
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...
;-)

Jeff
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
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…
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…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

762 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

18 Experts available now in Live!

Get 1:1 Help Now