Form, sub-form and many-to-many relationship

[atiras]

The answer to this will probably be blindingly obvious... once somebody points it out to me!

Using Access 2007, I have 3 tables:

tblResearch -- primary key ResearchID plus various other fields
tblTags -- primary key TagID plus one text field Tag
tbljctResearchTags -- primary key plus two fields ResearchID and TagID, creating a many-to-many relationship between ResearchID that allows each Research entry to have zero one or many Tags associated with it.

(All tags can similarly be associated with entries in a number of other tables.)

I have a form frmResearch (based on qryResearch that outputs all the fields in tblResearch) displaying the data for a single entry at a time from the Research table, including a concatenated list of the Tags associated with that entry.

To update the Tags for an entry, I have a command button that opens subfrmResearchTags based on tbljctResearchTags and tblTags, linked from ResearchID on frmResearch to ReseachID in subfrmResearchTags. The correct set of Tags is displayed for the current Research record but when I add or delete a Tag, ResearchID in the junction table isn't being updated correctly.

Am I trying to do something impossible, or have I just messed it up?

 

[Zaeed]

This is how i'd do it..

Ditch your junction table, and instead include a ResearchID field in you Tag table.

In your form, have a subform that has the Tag table as its source, and is linked by the ResearchID to the master form..

This form will then show the Research item, and in the subform all the Tags that are associated with that Research item..

 

[atiras]

Thanks for that, but it wouldn't let me have the many-to-many relationship I need.

 

[georgedwilkinson]

Pat Hartman did a demo M:M database on this site. You should take a look at that.

 

[atiras]

Thank you; I looked at it before I posted my query. I have tabbed subforms (similar to those in the example) working very well -- and can solve this problem short-term with a similar sub-form. However, as this data is updated relatively infrequently, and there are a lot of other sub-forms that really need to be tabbed, I wanted to use a command button to trigger this one.

I suspect the problem must lie in the way I've constructed the query for the sub-form.