Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

MS Access filter as you type in record set

Posted on 2011-03-21
13
Medium Priority
?
1,999 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 49

Accepted Solution

by:
Dale Fye earned 375 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 375 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

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
 
LVL 49

Expert Comment

by:Dale Fye
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 49

Expert Comment

by:Dale Fye
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

886 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