Need help in creating a List Box for multiple selection

[SandyLY]

I have 6 multivalue lookup fields in my QuoteCond table, all of the forum comments indicates this is not a good Database decision, so I am changing them but can’t seem to get it right….

Table QuoteCond
• (PK)QuoteCondID (connected to table AnchorID – one to many)
• AnchTypeID

Table AnchorID
• (PK) AnchTypeID
• (PK) QuoteCondID

AnchType
• (PK) AnchTypeID (connected to table AnchorID – one to many)
• AnchorType

Form QuotCond
• QuoteCondID
• List Box AnchType (lookup value in AnchorType, AnchTypeID – store value AnchTypeID in QuoteCond AnchTypeID)

Row Source
SELECT [AnchorType].[AnchTypeID], [AnchorType].[AnchorType] FROM [AnchorType] ORDER BY [AnchorType];

Control Source
AnchTypeID

Couple of issues -
1. List box works but I want to choose multiple items – when I change the “Multi Select” property to Extended the List box stops working – it allows me to highlight more than one item but my selections don’t stick and the table is not updated.
2. I want the List box to have check boxes for each item on the list.

I am not good with code so hopefully what I want doesn’t take a lot.

Thanks Sandy

[HansV]

You have a many-to-many relationship between QuoteCond and AnchType, correctly implemented through 2 one-to-many relationships with the AnchorID table.

One way to enter data is through a subform (usually a continuous or datasheet subform). You link the subform to the main form through its Link Master Fields and Link Child Fields properties, no code is needed.
Here is a very simple example:

Many2Many.zip

It is also possible to use a multi-select list box. This requires code in the form's On Current event to select the correct items in the list box, and in the On Click event of the list box to update the table. Agaim, here is a simple example:

Enrollment3.zip

Look at the code behind the form to see how it's done.

[SandyLY]

Hans, thank you for your quick response. I like Eileen’s Lounge so much, you give answers that are – quick, to the point, simple enough for beginners, you give choices and you explain those choices, your answers actually work and are simple to implement. I do wish the site had an RSS feed but the emails are a great idea.

Two questions:
1. I like the look and feel of the Many to Many example better than the Enrollment example (I even understood most of the code) but not as much as I like the look and feel of the Multivalue look-up field, the Multivalue drop down is more of what I had in mind, you can see all of the choices in the drop down and tick the check box for the choice.

You can probably add check boxes to the Enrollment example (and maybe put the choices in a drop down) with more coding but since I want to keep this as simple as possible for maintenance, while still giving them a well developed database, I think I may stay with the Multivalue look-up field, somewhat depending on your opinion.

What do you think….my database may have as many as 10 but probably not more Multivalue fields with no more than 10 choices per field and they will all be in 1 table, I don’t know if that makes using the Multivalue fields better or worse. The intended users are very (very) much NOT computer people and their training will be minimal.

Looking forward to your opinion.

2. If I decide to go with one of the examples, question – would I have to create a connection table (like the AnchorID table) for each of my fields? Or can they be put into one connection table? I am thinking this is not possible or a bad idea (since I have come across any examples in all my research) but if so, can you give me an example of how that would work?

Thanks Sandy

[HansV]

Like many "old-fashioned" (read "old" ) database designers, I feel reluctant about multi-valued fields, because they violate the rules of relational database design, and theoretically it's more vulnerable. But I have to admit that they provide a very user-friendly interface, and because of the two different ways you can use them in a query, they can be handy.

The two sample databases that I attached use the more traditional design. If you have 10 different many-to-many relationships, you'd need to create 10 different connection tables. That *is* more work. A single connection table might be possible, but it's probably not a good idea.

[SandyLY]

Thank you, whatever decision I make at least I am now well informed.
Sandy