Two-Way Many-to-Many Form

How do you provide a user interface for tables with a many-to-many relationship?

In a relational database system, a many-to-many relationship occurs when multiple values in one table can connect to multiple values in another table.  For example, consider application permissions.  Each user could be granted multiple permissions.  And each of those permissions can be granted to other users, too.

So, how do you show both sides of the relationship at the same time?  

This was something I struggled with for a long time.  Then I finally realized the answer: you don't show both sides of the relationship at the same time.  Instead, you treat the many-to-many relationship in your user interface the same way you treat it in your database schema: as a pair of one-to-many relationships.

• One user can have many permissions
• One permission can be granted to many users

Many-to-Many Data Management Form Screenshot

I wrote a centralized security application for one of our clients.  The program controls permissions for many other standalone Access applications: a general ledger, an accounts payable system, a fixed assets tracking system, etc.  

Here's what the main "Manage Permissions" form looks like:

How It Works

NOTE: This is more of a high-level overview than a step-by-step how-to.

The "Display By" Option Group

End users have the option to manage data in the tables by looking at one User at a time or one Permission at a time.

There is code in the option group control's AfterUpdate event to change the RowSource of the second combo box.  If the "User" option is selected, then the combo box shows all the records in the User table.  If the "Permission" option is selected, then the combo box shows all the records in the Permission table for the selected "Program" ("GL" in the example above).

Updating the List Boxes

When the user chooses a User or Permission from the combo box, the AfterUpdate event for the combo box fires.  Code in the AfterUpdate event sets the RowSource for the two list boxes.

The key to populating the two list boxes is a single subquery.  When "Display By" is set to "Permission", the subquery returns the logins for users that have been granted the selected permission:

Dim AllowedLogins As String
AllowedLogins = "SELECT LoginName " & _
                "FROM Login_Permission " & _
                "WHERE PermissionID = " & Qt(Me.cbPicker.Value)

After building the subquery, I use that to set the RowSource for the two list boxes.  First, I build the RowSource for the "Available" listbox on the left-hand side of the form which includes the following WHERE condition:

Dim Criteria As String
Criteria = "WHERE Program = " & Qt(Me.cbProgram.Value) & _
           "  AND PermissionID NOT IN (" & AllowedLogins & ")"

Next, I build the RowSource for the "Allowed" listbox on the right-hand side of the form.  It's a mirror image of the "Available" listbox.  I simply remove "NOT" from the "NOT IN" clause:

Me.lbAllowed.RowSource = Replace(Me.lbAvailable.RowSource, " NOT IN (", " IN (")

Editing the Lists

The users don't edit the lists directly.

Instead, they use the command buttons to move items back and forth between the two list boxes.

The OnClick event of the top button runs an INSERT query to add one or more records from the left listbox to the Login_Permission composite table (the table that acts as a bridge for the many-to-many relationship).

The OnClick event of the bottom button runs a DELETE query to remove one or more records from the Login_Permission table based on what the user has selected in the right listbox.  

In both cases, the event handler requeries both listboxes after running the INSERT or DELETE query.

Items of Note

Enable Multi-Select for the List Box Controls

You will probably want to enable either the Simple or Extended Multi-Select property of the two list boxes.

The Simple setting is more intuitive and discoverable for inexperienced users, but I think the Extended setting is well worth the extra user training that may be needed.  Being able to select all items with only two clicks (click on top item then shfit-click on bottom item) is a huge timesaver, especially for situations where it makes sense that the user would want to perform such bulk updates.

Works Best with Small Lists

This approach–where the user can switch between the two sides of the many-to-many relationship–works best when the two sides have a relatively limited number of records.

In this case, there are no more than a few dozen users and a few dozen application permissions.  

Add Filtering to Keep the List Boxes Manageable

I further limited the number of available permissions by only showing permissions for one application at a time (the rest of the form is hidden if the "Program" combo box is empty).

If the number of users ever gets too high, I could add another combo box to filter by department.  Obviously, the values you allow your end users to filter on will depend on the nature of your own many-to-many tables.