Solved

Develop a select record form

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

759 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

22 Experts available now in Live!

Get 1:1 Help Now