Solved

Develop a select record form

Posted on 2014-10-08
4
234 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 39

Accepted Solution

by:
als315 earned 250 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 250 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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

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…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

920 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

15 Experts available now in Live!

Get 1:1 Help Now