Search code examples
ms-accessms-officems-access-2016

How to implement multi-select?

First of all, I need to say that I never worked with Access or any other databases before. I'm trying to understand how it works and picked "Students" template for this task (see step 0). Now, let's start.

i. Creating the base from the template and fill it with data

Step 0. Creating new database based on "Students" template:

enter image description here

Step 1. On the objects pane (i.e. left sidebar) search for Student List form and double-click on it:

enter image description here

Fill the First Name and Last Name for the first student: it will be Mike Smith.

In the Special Circumstances column it is possible to select multiple circumstances: for example, Circumstance 1 and Circumstance 2. And if you click on the icon below the combo-box, it is possible to edit the list of available check-boxes:

enter image description here

Step 2. After we have created the entry for the first student, double-click on the Guardian List form and create entries for 3 guardians: Jane Roe, John Doe and Jill Joe.

Step 3. Return to the Student List form. Click on the Open cell in front of Mike Smith. It will be opened Student details form for this student. When it will be opened, click on Guardian Information tab - and you will see, that all 3 guardians are available for combo-box:

enter image description here

ii. Now, my task

  • I want to see the guardians directly in the Student List form - the same way as Special Circumstances located.

  • I want to get the option to select multiple guardians for each student - the same way as Special Circumstances check-boxes are implemented.

iii. How it was solved myself, but only the 1st point of the task

To simplify the task, I will use Student ID column (located on the Student List form), instead of creating new column.

On the left sidebar - click on Student List form with right mouse button and then select Design View. When Design View will be opened, select Student ID field, right click on it, then click Change To and Combo Box:

enter image description here

Then, on the right sidebar select Data tab, set curson inside Row Source field and then click small ellipsis (...). In the Show Table window select Guardians and then click Add button and close this small window.

enter image description here

enter image description here

Then, in the large window, do this:

enter image description here

And then click Save As and Close on the ribbon.

Then, switch the Student List form back to the Layout view (the same way, as we switched it early to Design View). And you will see that the first point of my task is solved: the last names of guardians are available in Student ID column:

enter image description here

But how I can implement multiselect, the same way as it is implemented in Special Circumstances column? So, Mike could have both Jane Roe and John Doe as his guardians.

Solution

  • Multi-valued combo boxes are very odd controls. Avoid them whenever possible.

    Creating an unbound multi-valued combo box is not possible (see How can I create a Multiple Value Combo box on an Unbound Form). A combo box is multi-valued when the field it is bound to is multi-valued, or when it's set to a multi-valued lookup.

    To change a field to a multi-valued field, you need to alter the table design, and set the Allow multiple values property to Yes:

    enter image description here

    This will automatically change any combo boxes bound to this field to multi-valued combo boxes.

    Note that I highly recommend not using this approach. This database already demonstrates a junction table, the recommended way to manage many-to-many relationships. Using junction tables and multi-valued combo boxes is not possible without using very hacky techniques.