How to make a list box display/set related fields in a many-to-many relationship

[pgregory]

I'm hoping someone can help me figure out how to set the row source and control source of a list box so that it displays records from another table that are related to the record currently being displayed in a form.



I am a relational-database newbie, so this is very much a nontrivial task for me. I scoured Google for solutions, and I was able to get a subform to do more or less what I want. But the subform has a clunky interface for what I'm trying to do. (I was also able to very easily accomplish what I want using a combo box and a multivalued/lookup field, but I've seen people bashing these on Access forums.)

I've attached a sample database that's set up the way I think I'm going to structure my actual database. The goal is to keep track of all the orders issued by a government agency (these are orders in the legal sense, not product orders). Each order will be assigned to one or more staff attorneys in my department. Here's how I set up the tables and relationships:

I have a table called Orders, a table called Attorneys, and a junction table called Assignments. I have established a many-to-many relationship between Orders and Attorneys via the Assignments table. That is, one or more attorneys can be assigned to work on each order, and each attorney will work on many orders.

I have a form Orders based on the Orders table, and what I want to do is place a list box on it that allows the user to add/change the attorneys assigned to that order. Better yet would be using a multiselect combo box, the way I was able to do when I used a lookup field. But I think I've read that combo boxes can't do multiselect except with lookup fields.

If you open the attached database file, you'll see the Orders form containing the subform that does more or less what I want. Next to the subform is a list box with a row source based on the Attorneys table and the control source left blank. I want the list box control to select the names of any attorneys who are associated with the current order (which has to require some reference to the Assignments table) and allow the user to change the selections. Beyond that I'm pretty lost.

Thanks for any assistance you can provide!

TestDB.accdb

[pgregory]

P.S. - People looking at the database will probably think I used an odd naming convention for my primary/foreign keys. I gave them all distinct names because I'm really trying to understand which keys should be included in queries involving a many-to-many relationship.

If you know of any good resources for learning about relational databases, I'm all ears. Books or websites would be great.

[Bob Fitz]

Take a look at the attached db. Not sure if I've understood your requirement correctly. Post back if you have any questions.

[pgregory]

Thanks, I'll take a look! I'm not at my work computer today, so I don't have access to Access. I will look at it first thing on Monday.

Thanks again!

[pgregory]

That's not quite what I'm going for. I would like the list box to display all of the attorneys in the Attorneys table, but to highlight only the subset of attorneys assigned to the current order (which happen to be those shown in the subform).

I have attached an updated version of the database, adding a combo box control to the Orders form. The combo box is tied to a lookup field "AssignedAttorneys" that I added to the Orders table. The combo box provides a better illustration than the subform of how I would like the user interface for assigning attorneys to work.

You'll note that both the combo box and the subform rely on dropdown menus to facilitate the selection of attorneys. (If you click the First Name field in any subform record--including the new/add record line--there's a dropdown menu that allows you to select an attorney.) The list box cannot do a dropdown menu, so it needs to be populated with all available attorneys in order for the user to be able to select an attorney who is not already assigned to the current order.

Ultimately, I will only keep one of these controls. I love the combo box interface, but as I said, I've heard this is not the "right" way to design relational databases. So I'd like to give the list box a try, if I can get it to do what the combo box does.

Thanks again for your help!

TestDB02.zip

[davegri]

Would something like this work with two listboxes? The combobox would select the order
and the listboxes would hold the attorneys. It would not be possible for an attorney to be assigned twice to an order, but more than one attorney could be assigned to an order.
MembersWeekend.zip

[orange]

pgregory,
Watch this older, free video from Datapig --- using 2 listboxes.
May give you some ideas, and Datapig is always a good reference.

[pgregory]

Thanks davegri and orange for these ideas. The two-listboxes solution is elegant, although I was hoping for something more compact (like the combobox with its dropdown menu or, at most, a single listbox). But maybe Access is not capable of doing my ideal solution.

[pgregory]

It just seemed like it should be possible to set the listbox's row source property to list all available attorneys, and then to set its control source to some kind of query that would result in the assigned attorneys showing up as highlighted/selected. But again, I'm pretty ignorant of Access's capabilities.