Solved

Develop a select record form

Posted on 2014-10-08
4
239 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
[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
4 Comments
 
LVL 40

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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
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…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

734 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