Have a list of foods in a table and a list of family members in another, not sure how to make a check list.

[Bettany]

Hi, I have 500 foods in a table, and the goal is for each family member to be able to go down the list and check the foods they like using a form. So essentially I need a form with the 500 foods listed, a checkbox next to each, and to be able to indicate somewhere on the form, who the person checking the checkboxes is (I wouldn't want them to have to list their name 500 times). I'm confused as to how to approach from a setup perspective. I tried creating a new query to bring in the foods and the familymember table but I got a cartesian product and was still unaware as to how to add the checkboxes. Thank you!

 

[theDBguy]

I think to get the ui you described, you would either use an unbound form or a temporary table.

 

[Gasman]

I would probably have a combo, that breaks the foods down into categories, meats, fish, veg etc.
Then a listbox that uses that combo value as criteria. Listbox would be multi select.
The user name would automatically be selected as I would ask them that when they started the DB.

Then I would run a query from a command button to populate a table that held those links. I would delete all previous links for that category before adding the new ones. So a few button presses, one for each category used.

Then take it from there, with any issues.

 

[ebs17]

Start with a many to many Relationship between the foods table and the familymember table.
The form design then derives from this relationship.

 

[GPGeorge]

Hi, I have 500 foods in a table, and the goal is for each family member to be able to go down the list and check the foods they like using a form. So essentially I need a form with the 500 foods listed, a checkbox next to each, and to be able to indicate somewhere on the form, who the person checking the checkboxes is (I wouldn't want them to have to list their name 500 times). I'm confused as to how to approach from a setup perspective. I tried creating a new query to bring in the foods and the familymember table but I got a cartesian product and was still unaware as to how to add the checkboxes. Thank you!

Perhaps you would be well served by investing time in learning how to design tables and the relationships between tables in a relational database application. What you propose (500 checkboxes) is not going to work very well no matter how you try to implement it.

Instead, you need to follow Pat's directions on setting up the tables and then the proper forms to work with them.

Here's a brief video that should get you started.

 

[Gasman]

You would NEVER use an unbound form with 500 checkboxes in a relational database. You need to add the junction table as suggested by ebs17. Then you need to use a subform. Each row in the subform reflects one choice. You pick the choice from a combo.

Here's an example of a m-m implementation. It shows two ways of looking at the relationship. One using a subform and the other using a popup.

No example attached Pat?

 

[Bettany]

Pat, that solution means a lot o

You would NEVER use an unbound form with 500 checkboxes in a relational database. You need to add the junction table as suggested by ebs17. Then you need to use a subform. Each row in the subform reflects one choice. You pick the choice from a combo.

Here's an example of a m-m implementation. It shows two ways of looking at the relationship. One using a subform and the other using a popup.

Thanks Pat, from a usability standpoint that seems extremely painful. To be sure I have this right, your solution involves clicking a combo box in a subform, scrolling through 500 options in that combobox, selecting an item, and then repeating that process, potentially 500 times?

 

[Bettany]

I think to get the ui you described, you would either use an unbound form or a temporary table.

Kind of inclined to agree here. Any UI setup that doesn't have all the of checkboxes available to be clicked just seems too painful and repetitive (particularly combo boxes). Is your idea to have all the foods listed next to the check boxes on a single form, and to use VBA to tie all the info together?

 

[GPGeorge]

Kind of inclined to agree here. Any UI setup that doesn't have all the of checkboxes available to be clicked just seems too painful and repetitive (particularly combo boxes). Is your idea to have all the foods listed next to the check boxes on a single form, and to use VBA to tie all the info together?

"...each family member to be able to go down the list and check the foods they like..."

To be honest, asking anyone to select 500 checkboxes also seems a bit over the top to me. No matter how you present that interface, with checkboxes, combo boxes, etc. 500 selections for anything is just plain overwhelming. Not to mention finding and changing a selection later when they change their minds.

There are other ways to do this, but the one that any experienced relational database developer would recommend is going to be based on the many-to-many approach previously recommended by others here. I know, you don't have to do that, but sooner or later the price of not doing it is going to seem too high.

But then there's another way to do this, as well. Two in fact. One is to include categories of food, and foods within those categories:

Meat, Fish, Poultry
Dairy
Fresh Vegetables
Fresh Fruits
Condiments

and so on. You'll still have hundreds of foods in some of the categories, but that's a more user friendly strategy already.

I suggest, once again, you invest time learning how relational database applications work. Also do a search on Cascading Combo Boxes to get some insights into more user friendly interface designs.

Obviously, you have the final say, but since you came to this forum to ask for advice from experts, that's what you are going to get, expert advice.

 

[bastanu]

You could also consider having two multi-select listboxs side by side in the classical Available<->Selected setup and implement code in double-click event to move from one side to the other in conjunction with the four usual buttons (<-,->,->>,<<-).
Cheers,

 

[GPGeorge]

You could also consider having two multi-select listboxs side by side in the classical Available<->Selected setup and implement code in double-click event to move from one side to the other in conjunction with the four usual buttons (<-,->,->>,<<-).
Cheers,

That's probably the approach I'd take too, but 500 rows in a list box? Daunting. There has to be some pre-filtering to get the list down to a manageable size.

 

[Uncle Gizmo]

your solution involves clicking a combo box in a subform, scrolling through 500 options

Edit:-
My method solves the aforementioned problem ...

500 is a lot of choices! Might pay to group the choices in some way.

I did a blog about check lists here:-

Add a Check List to your MS Access Database - Nifty Access

Add a Checklist to MS Access Checklists are very useful. However, they can be a pain both for developer and for the user. The last thing you want is a subform comprising of a set of of a combo boxes that the user has to select in order to provide the question and the answer!

www.niftyaccess.com

Might be of interest.

I also demonstrate how to setup checklist groups here:-

Add a Multiple Check List to your MS Access Database - Nifty Access

Multiple Checklists This set of videos I expand on the idea presented in the "Single Checklist" videos. I recommend you watch the "Single Checklist" videos before you start creating"Multiple Checklists". You will need to be able to set up a "Single Checklist" before you embark on the task of...

www.niftyaccess.com

But you need to setup a single checklist first, to gain insight in how multiple check lists work....

If you can't find that blog, let me know. ..

This YouTube maybe of particular interest:-