M2M / Master Child Form Design

[Kenilik]

Hi,

I need some advice about how to structure some MS Access 2007 Forms for the following scenario.

My table structure is as follows:

Product
++++++
ID (pk)
ProductName
...

Tag
++++
ID (pk)
TagName

ProductTag
+++++++++
ProductID (fk)
TagID (fk)

I want to have a main form that allows me to search for and displays a list of all products (and the dozen or so attributes for each product) that meet that criteria (I'm thinking a continuous form view of products).

So far so good. No problems.

Now I want a "tagging" form to the right of my continuous forms product list that is linked to the product which currently has focus. The tag panel should always show a complete list of all Tags. I want a checkbox next to each tag that I can switch on and off whether that tag applies to the currently selected Product. I also want to be able to add tags on the fly (without leaving the main form).

I think I have three forms in play but this is where I need some advice:

• Main Form
• Product Form (subform to Main) my product search criteria is probably on this form header.
• Tag Form (also subform to Main)

But with this approach I'm have trouble linking the two sub forms to each other. Actually, I'm having huge trouble figuring out how to display the list of all tags with a check box that when checked on and off creates / deletes a record in the ProductTag table and I really want Access to do as much of the CRUD as possible. I would prefer to avoid writing screeds of SQL INSERTS/DELETES on events. Am I asking too much of Access?

If you can help, it would be much appreciated. If in your response you can discuss what RecordSource and Master/Child Link fields and/or event triggers I should be focusing on that would be a great help.

 

[rodmc]

But with this approach I'm have trouble linking the two sub forms to each other.

A subform links to the master not another subform, unless the second subform is the child of the first subform