Develop a select record form

Please see attached file to see what I'm trying to do.  My question is this...  When the user starts to enter, for example, an ID number in the ID field, the listbox will start to populate with records that have that ID record.  Or, for example, when the user starts to enter a First Name in the FirstName field, the listbox will start to populate with records that contain that first name.  

But imagine the user is typing "St" in the First Name field... then for example all of the "Stan" and "Steve" record will be displaying.  But when they continue of typing and have typed "Ste" then only the "Steve" records will be displaying.  Then the same thing is true for all the search text boxes... ID, First Name, Last Name, Address, and City.

Is this even possible?

--SteveExample select form
SteveL13Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

als315Commented:
Look at sample with filters. You will not be able to search ID field if it is numeric field, because you can't apply text filter to this field. If you like, you can convert numeric field to text (in query) and apply filter to converted field
DBFilter.accdb
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BitsqueezerCommented:
Hi,

yes, this is possible using the Filter and Change event of each field.

I've already made such a tool which you can use if you want, you can find it on my download page:

http://www.ccedv.de/downloads/en

The filename is "CCFilterV2_7", the code is fully documented, but in German only, I do not have a translated version. The demo database contains some demos, one of them uses the Change event method.
This can be used for any kind of form, feel free to use it in your database if you want.

Cheers,

Christian
0
rspahitzCommented:
A good way to handle this, if those search fields are unbound, is to make them into dropdown lists, populated with all values in the table.  The challenge will be if there are too many or if there are duplicates, but this will give the required functionality as the user starts typing.

If you want to keep text boxes, you will run into a lot of challenges, although the easiest way to handle it would be to intercept the OnChange event and reload all records on each change.  Of course, this will potentially be very slow if you have a lot of records to filter. Also keep in mind that users may insert text into the middle of existing text (like "type sa" then realize you missed the "t" and go back and change it to "sta") and users may also use Ctrl-V or other paste functions including using the mouse to paste.
0
Helen FeddemaCommented:
See my Fancy Filters sample database.  It uses combo boxes with lists filled from data in tables, to avoid user error.  You have to make a selection, but IntelliSense will help you get to the first match.  Code on the AfterUpdate event of the combo boxes creates filters of the appropriate data type.

Here is a link for downloading the sample database:
http://www.helenfeddema.com/Files/accarch129.zip

and here is a screen shot of the form:
Fancy Filters form
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.