Data Entry Form Help: many-to-many relationships and multiple selections.

[slkasulke]

Hi All,

I have a relatively elementary question for you all…

I have a database that stores information about brochures on hand. Each brochure can come in one of the following languages: English, Spanish, French, or a combination of the three.

I have a many-to-many relationship structured like this:

Products Table
- Product ID

Languages Table
- Languages (list of languages here)

ProductLanguage Table
- ProductLanguage ID (autonumber)
- Languages (linked to the languages table)
- ProductID (linked to the product table)

I need to create a data entry form that allows users to add NEW items. I am absolutely stumped on how it is I allow a user to select multiple values from a many-to-many relationship on a form.

I’ve done quite a bit of googling and this is my last resort. If any of you could point me in the direction of a good tutorial or give me a few pointers, that would be extremely appreciated.

Thanks!

 

[Beetle]

In a scenario like this you use a main form based on, let's say the Products table for this example, with a sub form based on the junction table (in this case ProductLanguages). The Master/Child link for the subform control would be ProductID. In the subform you would use a combo box bound to the LanguageID field in the junction table (in other words LanguageID is the Control Source of the combo box). The Row Source of the combo box would be a query that returns the necessary fields from the Languages table. When you select a product (Brochure) in the main form, the associated Languages appear in the sub form.

You can also reverse the logic and have a main form based on the Languages table with the sub form (still based on the junction table) displaying the associated Brochures for a given Language.

 

[slkasulke]

In a scenario like this you use a main form based on, let's say the Products table for this example, with a sub form based on the junction table (in this case ProductLanguages). The Master/Child link for the subform control would be ProductID. In the subform you would use a combo box bound to the LanguageID field in the junction table (in other words LanguageID is the Control Source of the combo box). The Row Source of the combo box would be a query that returns the necessary fields from the Languages table. When you select a product (Brochure) in the main form, the associated Languages appear in the sub form.

You can also reverse the logic and have a main form based on the Languages table with the sub form (still based on the junction table) displaying the associated Brochures for a given Language.

Thanks. It seems I was on the right track when I was putsing around with this in the afternoon.

The way I'm visualizing it, this doesn't seem very user-friendly... Selecting multiple from dropdowns has never been an option I favored in user interfaces. I have other tables with similar relationships that have 50+ options.

Is there a way I can incorporate checkboxes? (This may be getting over my head, but I have all the time in the world to keep making mistakes.)

 

[Beetle]

With a combo box all you need to do is start typing and the list will filter for values that match what you have typed so far. You do not need to scroll through the entire list each time you want to make a selection.

Not sure what your train of thought is about the check boxes, but having a bunch of check boxes (50+ ?) on a form is much less user friendly than a combo box IMO.

 

[slkasulke]

With a combo box all you need to do is start typing and the list will filter for values that match what you have typed so far. You do not need to scroll through the entire list each time you want to make a selection.

Not sure what your train of thought is about the check boxes, but having a bunch of check boxes (50+ ?) on a form is much less user friendly than a combo box IMO.

Let's assume the user doesn't know the options available... how would typing and having things pop up work for them.

Also, deselecting in a combo box is not as easy as unchecking a checkbox.

Do you get what I'm saying? I'm having a hard time articulating.

 

[Beetle]

On the surface it might seem simple enough to just let users make selections by checking a few boxes, but as the developer you have to consider more than just the user interface. You also have to consider data integrity and the maintenance of the application. Using multiple check boxes to store choices can quickly become a maintenance nightmare.

Let's suppose you do create a form with, say, 25 check boxes. You have a couple of options with regards to actually storing the choices that are made on this form;

1) Create a table with 25 Yes/No fields (which is terrible table design) and base the necessary queries, forms, reports and code on this table.

2) Use a proper table design (with 1 or 2 fields to store the choices made) and use an unbound form with code behind it to determine which choice was made and write a record back to the table via an append or update query.

Now let's suppose you need to add another (26th) choice (a very likely scenario at some point in many applications). In scenario 1 above you need to add yet another Yes/No field to the table and then go back and modify all the queries, forms, reports and code that are based on this design. In scenario 2 above you may save yourself the trouble of adding another field to the table but you still need to redesign your form (and possibly reports) and most likely rewrite or modify your code. And these are just the design issues. There are also (at least in scenario 1) a lot of problems with simply trying to query the table for usable information.

In a properly designed application, when you need to add a new value to a list of choices, you should only need to add another record to the table where the list values come from. There should not be a need to re-design objects (tables, queries, forms, reports, code) to accommodate a new choice.

If you can provide more details about the nature of that part of your application (where the user needs to choose from 50+ options without knowing anything about what options there might be), maybe we can make some recommendations. Maybe list boxes would be a good option. Maybe the values can be broken down into categories so the user can choose a category first, then choose from a shorter list of options.