I fear I am missing a very obvious solution for my current dilema, but does anyone know how to allow multiple selections in a lookup list?

For example:

I want to associate a table of buyers with another table of items purchased. So for every buyer, there could be multiple items purchased.

I can't figure out how to set up the lookup list so that when you link the tables, you can select multiple items in your lookup list.

I would appreciate any insight anyone could provide. Thanks.

Comboboxes do not support the multi-select property. That is only available with listboxes.

The multi-select property of a listbox is most often used to allow the user to select multiple items to be used as criteria in a query. A multi-select listbox CANNOT bound to a table field for the simple reason that relational database theory prohibits the storing of multiple pieces of data in a single column.

If you are intending to use the multiple selections as criteria in a query, you will need VBA code to loop through the selectedItems collection of the listbox and create a text string. There are many examples of code available by searching this forum.

If you are intending to store multiple values, you need to change your table structure since you have a 1-to-many relationship. You will need a separate table to hold the "many-side" data and a subform to view/enter it correctly. No coding will be required. Do some reading on database normalization if you don't understand this concept.

Take a detailed look at northwinds.mdb, the sample db that ships with all versions of Access to see how an order entry system is structured.

Thank you. I guess the solution wasn't as simple as I thought.

We use Rational Products here and with some of the Rational products users can go in and select what kind of data a field is going to be (single list, multiple list, etc.) and set it up as such.

This is going to take a little more work.

Why do I need to create a separate table to hold my "many-side" data if it already is in a table?

Defining a table that stores multiple items in a single column would be incorrect. Additionally, this can only be done by using unbound controls and VBA. Not to mention how it would complicate your queries. All-in-all, it's a lot of work to do something that you should not be doing.

Defining a table that stores multiple items for a single buyer by defining some number of separate columns to hold the items is also incorrect. This is much like the way spreadsheets are designed and will have the same types of limitations.

The only correct solution is two tables (or three if you include a table for items since this is technically a many-to-many relationship).

tblBuyer
BuyerID (primary Key)
etc.

tblPurchases
BuyerId (primary key field1)
ItemId (primary key field2)
etc.