Many-To-Many MS Access Form With Checkboxes for All Options

Question

I'm working on a Microsoft Access application for a summer camp to track which entities have signed up for which activities. There is a form for editing an entity's information. I would like to add to that form a list of all activity options. By each option should be a checkbox. When the checkbox by an option is checked, a entry should exist in the many-to-many junction table linking the entity with the activity.

Google offered some examples of building many-to-many forms but none (at least that I found) showing how to provide a full list of options with checkboxes.

How would I do this?

Database Table Layout: Entity (EntityID, first name, last name, etc.) Activity (ActivityID, activity name) Entity_Activity (EntityID, ActivityID)

Answer 1

Instead of check-boxes, the more natural way to do it with MS Access would be to have a list of activities (in a sub form) that each entity is signed up for. Activities would get added from a pull down list (and perhaps an Add button), and removed with a Remove button. With a clever query, you limit that list to only activities that the entity doesn't have yet.

Alternately, you could go with the checkboxes, but you'll have to modify your table layout slightly. Entity_Activity would need a third field (SignedUp, yes/no). You would then have to populate every Entity_Activity combination when you created a new Entity. However, if you should happen to add another Activity later on, you'll have to go through some hoops to get all the existing Entity's entries updated.

Answer 2

One way to do this:

• Create a new entry in the Activity table.
• Manually insert one checkbox per activity on the form.
• Register an onClick handler on each checkbox that adds the appropriate row to the junction table when tje checkbox is checked and removes the appropriate row when the checkbox is deselected.

I was hoping for an approach that didn't require manually laying out the form. With this method, every time a new activity is added, the form must be modified. Oh well....