Hi All,
I have a pretty difficult problem that I could not immediately figure out or handily google how to do. So here goes:
We have a database that has a table listing people and their contact information (phone number, email, address, etc) - pretty standard stuff. We also have another table that lists information for a "Task" - such as a description of the task, when it is due, the completion percentage of the task, etc. And lastly we have tables that list the "Task" in one column one person associated to it in the next (for example one table to list who owns each task, one table to list who works on each task, etc).
Currently, the user can use a form to fill in all of the "Task" information except for the people info. When they get to filling in information regarding people (who the task is assigned to, who is the owner of the task, etc), they must go to the task-people tables and manually do it. The column that lists a person's name is a value list that calls the names from the contact information table, to save the user some time.
Now, we want to add some fields to the form to record people information as well. At first I thought of maybe just making a few fields with the value list and having it record one field for one table cell, but that was dashed because tasks can have any number of people working on them (20 or more even!) so it would not be very efficient or form-like to have 20+ fields just for inputting people. Then I looked into List boxes (we use Access 2010, so there are some funky things that can be done with List boxes and lookups). However using listboxes, the user can only manually scroll down a long list containing all or a subset of the contacts and then click on checkboxes next to each person they want - very time-consuming and irritating when we look at filling in hundreds of tasks a week.
The end goal that has eluded me so far is a field that can act like Outlook's "To" field. It should be able to auto-complete names once they start getting typed, it should be able to list more than one person, and it should be able to record all of that information in a way that can be easily queried.
As far as i know, this is not a small thing to do and i'm not sure it's possible without some vba (i've tried value lists and list boxes without vba and lookups without vba). Is what I'm asking for even possible? Any and all help would be appreciated! Thanks in advance!
-A