Access form for many users to many with checkboxes for all parameters

Question

I am working on a Microsoft Access summer camp application to keep track of which organizations have registered for which activities. There is a form for editing entity information. I would like to add to this form a list of all activity parameters. For each parameter there should be a flag. When the option check box is checked, there should be an entry in the many-to-many link table that links the object to the activity.

Google offered some examples of creating many-to-many forms, but not one (at least I did not find) showing how to provide a complete list of options using checkboxes.

How can I do it?

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

Answer 1

One way to do this:

• Create a new entry in the Activity table.
• Manually select one checkbox for activity by form.
• The onClick handler is registered on each flag which adds the corresponding row to the connection table if the tje flag checks and deletes the corresponding ones if the flag is unchecked.

I was hoping for an approach that did not require manually laying out the form. With this method, every time a new activity is added, the form must be changed. Oh good...

Answer 2

Instead of checkboxes, a more natural way to do this using MS Access will be to have a list of actions (in the subtitle) for which each object is registered. Actions will be added from the drop-down list (and, possibly, the “Add” button) and deleted using the “Delete” button. With smart query, you restrict this list to only actions that do not exist yet.

Alternatively, you can check the boxes, but you will have to change the layout of the table a bit. Entity_Activity will require a third field (SignedUp, yes / no). You will then need to fill out each Entity_Activity combination when you create a new Entity. However, if you need to add another activity later, you will have to go through some hoops to update all existing Entity entries.