Solved

How do I create a "Type to Query" form in Access?

Posted on 2011-02-11
6
199 Views
Last Modified: 2012-05-11
I have a database with a large number of contacts.  I would like to create a form where I could easily narrow down my results by typing.  

Example:  

My form has 2 text fields.  fld_fname and fld_lname.  There is a list box showing all my contacts.  

I would like to narrow down the list by typing criteria in the fields.  Putting an A in fld_fname and T in fld_lname would narrow down the list to only show contacts with the initials A T etc...

Any thoughts?  

Oh yeah, I'm using Access 2003.  
0
Comment
Question by:NDawg
[X]
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
  • 3
  • 2
6 Comments
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 300 total points
ID: 34869774
You can use the Filter property of the Form. Add a couple of textboxes to your form (one to filter by fld_fname, another to filter by fld_lname) and then do this in the AFterUPdate event of both:

Me.Filter = "fld_lname LIKE '" & me.txtYourLNameSearchBox & "*'"
Me.FilterOn = True

You could also use the Change event of those boxes, and reference the Text property:

Me.Filter = "fld_lname LIKE '" & me.txtYourLNameSearchBox.Text & "*'"
Me.FilterOn = True

Note that .Text works ONLY when that box has the focus.
0
 

Author Comment

by:NDawg
ID: 34869894
Hey LSM, Thanks for the quick response.  Both work, but for my needs I'm going with the Chane event.  

Just did a quick test and the code works, but only allows for 1 character at a time.  As soon as I type the second letter it replaces the first letter.  

I've gotta sign off for now, but will continue to dig more tomorrow.  
0
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 200 total points
ID: 34870777
you will need a string variable holder

private sub text0_change()
dim str as string
str=str & me.text0.text

me.filter="[fldName] like '" & str & "*'"
me.filteron=true
end sub
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:NDawg
ID: 34877091
Ok. I think I've figure out what the problem is.  After filtering, the field is coming back with the entire contents selected.  That is why the second letter is replacing the first letter instead of being appended to it.  
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34878359
the text box where you are typing the information must be UNBOUND for this to work.
0
 

Author Closing Comment

by:NDawg
ID: 34908383
Both suggestions were a big help at getting me pointed in the right direction, but neither was a perfect solution.  I would give an A- if I could, but I can't.  

I'm old school access guy from way back.  As I got in to linux I eventually stopped spending much time with it.  I've got a project that calls for it and have found a few things have changed and that I've forgotten a few things.
0

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Familiarize people with the process of utilizing SQL Server views 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 Microsoft Access…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

735 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