Is there a way on a form to have multiple checkboxes appear in order to capture a many to many relationship between two tables? If anyone could provide guidance for the following example I would find it most useful...

tblPerson
PersonID
PersonName

tblGenre
GenreID
GenreName

tblPersonGenre <== this is the join table
PersonGenreID
Person <== linked to tblPerson
Genre <== linked to tblGenre

Now based on the table structures above let's say you have 3 existing genre records (country, rock, jazz) and 1 person record (let's call him "Bob"), is it possible to create a form where you can select Bob on a drop down while also on the form there would be one checkbox for every possible record in the genre table? You would be able to check them off to update the join table accordingly but your overall choices would be defined by the genre table? If someone could provide a reply for how to do this or possibly a sample database of such an example I would be most appreciative, thanks!

Hi,

I don't have the link right now but try doing a search in the code archive for a demo by NoahP on multiple checkbox GUI.

Hope that helps...

Here's the link DBGuy was referring to - Good Stuff!

Multiple Checkbox GUI In A Normalized Structure

Hi Fastrhythm,

If the previous suggestions are not quite what you are looking for then I suggest using a main form - subform to show a 3D representation of the junction table (tblPersonGenres) which would show the Genres across the top of the form (I am assuming that there are not more than 10 or so), the persons down the left hand side (as many as you need) and a tick (or whatever symbol you want) at the junctions of the two. In this way you have all the information you want on screen without having to move to a different record for each person (although you would have to scroll the sub-form if you have more than 20 or so person records).

See the attached (Access 2003) demo for an example.

This method uses a main form with a subform, the subform is bound to a temporary table which is set up to show the Genre for each field (8 in this example) and each record in the table represents a person from the Persons table. The three main tables are connected in a many-to-many configuration (as in your example) and then when the form is opened a bit of code looks through the three tables and inserts the relevant data into the temporary table (tblGrid) which is then displayed on the subform. Clicking on any field on the subform toggles the tick that is or isn't displayed and some code creates or deletes a record in the junction table (tblPersonGenres). This method ensures that the data is properly normalised.

You would also need some other forms to handle the two tables that hold the person and genre data.

Some points you should be aware of in this demo :-
- the three tables you mentioned are joined with Referential Integrity enforced (see the Relationships window) so that if you should delete a person or a genre record, the corrsponding records in the junction table will also be deleted.
- the PersonID field holds a number for each person record. When you add or delete a record from this table you must ensure that these numbers are updated so that they are continuous and always start at 1, i.e. no gaps in the sequence. This is because these numbers are used to determine the reference for the subform records when the user clicks on a field.
- the GenreID field in the table tblGenres does the same as above, the numbers must be continuous. Also, in the case of the genres, if you need to add or remove a genre you would need to add or remove a field from the subform itself and change the VBA code slightly but hopefully this would only happen very rarely.
- the Genres fields along the top of the form are programmed automatically, you can just add or change them in the table and the form will show the amended names. Note that these are sorted on the GenreID field, number 1 is first, 2 second and so on.
- the Persons list is sorted alphabetically on PersonName field in the code (you should really have a FirstName field and a LastName field for this so that you could sort on LastName).

Try it out and post back if you need any further information.

Peter Hibbs.