How to use linking tables and bound objects in MS Access forms.

August 4, 2010 8:50 PM

Microsoft Access 2007 form creation question. Hopefully not-too-wrongheaded and clearly described inside.

My understanding: When modeling a many-to-many relationship, you use a linker table.

Example: Movies and genres. A movie can belong to many genres. A genre can describe many movies.

Implementation example:

____Linking Table_____    __Movie Table__     __Genre Table__ 
|ID|Movie ID|Genre ID|    |ID|Movie Name |    |ID|Movie Genre|
| 1|   1    |    1   |    | 1|True Lies  |    | 1|Comedy     |
| 2|   1    |    2   |    | 2|Goodfellows|    | 2|Action     |
| 3|   1    |    3   |    |  |           |    | 3|Spy        |
| 4|   2    |    2   |    |  |           |    |  |           |
----------------------    ----------------    ----------------

How can I take those tables and make sensible controls?

Scenario 1: Update the linking table.

Open a form associated with a movie. A pulldown list is populated with genre text descriptions (bonus points if genres already associated with the movie are excluded). Select a genre, hit a button, and a new entry is added into the linking table matching the movie and the genre ID.

Scenario 2: Simply listing the names of genres a movie is associated with.

Open a form associated with a movie. Have a label filled with a list of genre names the movie is associated with.

How do I make this work?

Please, please, please: don't assume I know something. I'm learning, and self teaching. Lots of gaps in knowledge here.

I learn best by example, and I can follow instructions like a champ.

posted by jsturgill to Computers & Internet (2 answers total)

Access 2007 supports multivalued fields, which does the linking table for you behind-the-scenes. According to this article, you can use the multi-valued field in a form.
posted by zixyer at 9:32 PM on August 4, 2010

Here's another one I often fall back on. Multi-valued fields are tricky, and I have had real trouble arguing with them before about which value to actually STORE, but I can't always recreate the issue.

Here's another link from MS on mutivalued fields.
posted by TomMelee at 5:37 AM on August 5, 2010