Results 1 to 9 of 9
  1. #1
    Caribou16 is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Feb 2018
    Posts
    4

    Question Looking for input control with specific behavior: auto complete/filter

    Hello- Apologies if this is a basic question, I'm an Access novice that's trying to learn the program and help out some folks by digitizing a currently manual paper/pencil/Excel process. My question is specific to form construction, but I'll include the whole background, in case there's something I could be doing better.

    The goal: Friend is in charge of collecting and maintaining community service records for the students of a local private school. The community service is accrued through school sanctioned events. This information is needed for awards and National Honors Society eligibility. Current process is she has a record for each student in an Excel sheet and manually augments their hour totals as events are completed. It's a small school, so only ~300 records to worry about, but still a tedious process.

    My plan: I currently have several tables set up:




    • Students: This contains records on the students and should hopefully only need to be entered once.
    • Clients: This contains records on the entity the community service is done for and contains background and contact info.
    • Events: This contains records for each individual community service event.


    One client can have one to many events, one event can have one to many students participating. Set up this way, I should be able to report a total of any individual student's total hours.

    Where I'm having difficulty is assigning the students to a specific event. The form user needs to select from the list of all students in the Student table to relate them with the specific event. As of right now, adding students is using the combo box control, but that will become cumbersome once all the students are added.

    What I'm hoping for is a control which is essentially a combo box, but will auto filter by a specific field (last name) in the Student table to make entry easier.

    Thank you!

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Think you need more tables. Do you need to associated student with particular client as well as event?

    ClientsEvents

    StudentAssignments
    StudentID, ClientEventID
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Agree.
    To address the concern about a long list of students, though, you could use a 'find as you type' procedure. It narrows down the list of possibilities according to what has been entered. A combo will accept such input, as well as allow you to restrict the user from adding typos to the list (e.g. McAlister when it should be McAllister). User can stop at any time and select from the filtered list. You can also allow new entries via the control, but I think I'd advise against it for at least the time being.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    Caribou16 is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Feb 2018
    Posts
    4
    Quote Originally Posted by June7 View Post
    Think you need more tables. Do you need to associated student with particular client as well as event?

    ClientsEvents

    StudentAssignments
    StudentID, ClientEventID
    No, although I suppose it would a nice thing to be able to see who ever worked for a specific client. Would I have to relate them directly to the Client table....or can it somehow be inferred from the Students attached to an Event, which are attached to a Client?

  5. #5
    Caribou16 is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Feb 2018
    Posts
    4
    Quote Originally Posted by Micron View Post
    Agree.
    To address the concern about a long list of students, though, you could use a 'find as you type' procedure. It narrows down the list of possibilities according to what has been entered. A combo will accept such input, as well as allow you to restrict the user from adding typos to the list (e.g. McAlister when it should be McAllister). User can stop at any time and select from the filtered list. You can also allow new entries via the control, but I think I'd advise against it for at least the time being.
    So how would I go about doing this? Is a combo box even the correct control? The way I envision this form looking, the user enters in the event information at the top...then enters in one by one the students into a list at the bottom. The students which can be added to the list is what ideally should be search/filterable.

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    The form user needs to select from the list of all students in the Student table to relate them with the specific event.
    This made it sound like that was already decided. According to your 'vision' you're wanting a subform/main form setup so that you can enter multiple records (the many side of the parent relationship) pertaining to the main form parent field. Then you can still use a combo if that presents the desired look/behaviour for the form. Combos in datasheets don't display the drop down button until the user selects the field IIRC, so you might want a continuous form instead of a datasheet. Or you could present a multi select list box in a popup form and allow the user to select several names to be added to the datasheet/continuous form records in one step. Point is, there's many ways one might build this. It all depends on the goals of the designer or wishes of the user - which aren't always in sync! What's often true is that the more complex you make it (such as a multi select listbox), the more code that's involved.

    So I still think a combo on a datasheet or continuous subform can work, and that combo can make use of the OnChange event to filter as the user types, including the possibility that the user removes the entry in one keystroke.

  7. #7
    Caribou16 is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Feb 2018
    Posts
    4
    Appreciate the responses. I guess since I'm the only designer and no one is really asking for this, I can do what I want! (But I'd love to give them something that will be easier then manually updating an Excel sheet.)

    So what is the best practice for a user input form to perform this function? I'm not married to any sort of table structure and don't mind retooling if there is a better/easier way.

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    If you don't need to show that the student worked with a specific client, just the event, then have a 'junction' table for StudentEvent.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Here's a nice little database that assigns events to participants in a many-to-many 3 table setup.
    Assignments are made using side-by-side listboxes that allow you to move events from the 'available listbox' to the 'assigned listbox' for a participant selected by a combobox.
    There are a lot of very helpful features and reports included.
    Generic-EventsSchedule V001.zip

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 07-15-2015, 01:39 PM
  2. Auto Complete A Subform
    By dgutsche in forum Forms
    Replies: 1
    Last Post: 08-14-2014, 04:10 PM
  3. auto complete fields
    By mona in forum Access
    Replies: 3
    Last Post: 05-09-2012, 07:41 AM
  4. Replies: 3
    Last Post: 11-04-2011, 01:50 PM
  5. Auto Complete Data
    By manicamaniac in forum Access
    Replies: 5
    Last Post: 09-14-2010, 03:38 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums