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

x
?
Solved

Develop a select record form

Posted on 2014-10-08
4
Medium Priority
?
244 Views
Last Modified: 2014-10-11
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
0
Comment
Question by:SteveL13
4 Comments
 
LVL 40

Accepted Solution

by:
als315 earned 1000 total points
ID: 40369182
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
 
LVL 24

Assisted Solution

by:Bitsqueezer
Bitsqueezer earned 1000 total points
ID: 40369184
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
 
LVL 22

Expert Comment

by:rspahitz
ID: 40369222
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
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 40370660
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

Featured Post

Industry Leaders: 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!

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
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…

879 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