Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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)

like image 896
Ben Gribaudo Avatar asked Jun 23 '09 00:06

Ben Gribaudo


People also ask

How do you create multiple forms in Access?

Create a multiple item formOn the Create tab, in the Forms group, click Multiple Items. If Multiple Items is not available, click More Forms, and then click Multiple Items. Access creates the form and displays it in Layout view.

How do I add a radio button to a form in Access?

Open the form in design view. Select the option group by clicking on its border. If necessary, resize it to make room for the new option button. Select the Option Button control (aka radio button) in the Controls group of the Design tab of the ribbon.


2 Answers

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....

like image 65
Ben Gribaudo Avatar answered Sep 28 '22 12:09

Ben Gribaudo


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.

like image 42
BIBD Avatar answered Sep 28 '22 13:09

BIBD