Many To Many Form Design Help

[captain1974]

Access 2003.

I have searched for info on designing many to many forms, but not found anything to match what I am trying to achieve.
I have attached part of my database for reference.
I want to create a form that a user selects a 'Vineyard' from a combobox and then selects 'blocks' particular to that vineyard from a list box. The displayed blocks are filtered only to list the blocks that are from the selected vineyard. This part all works ok (actually I have been using it for other forms with a combo box just to select one block & it all works fine), but I now want whatever is entered into the 'operations' subform to be recorded for each record that is selected in the list box. Ie I want to select 'many' records at once within the listbox.
I have no idea of how to set the form up to do this, the current example is just for visual purposes.
I think that the form will need a master record navigator that navigates through operations, not vineyards.
I will also be adding a calculated text box which totals the hectare size of all of the blocks selected.

Any help or suggestions would be appreciated. If there is a better and different way to do this than the way I'm attempting I would be glad to hear.

 

[namliam]

many to many forms, but not found anything

The reason you didnt find it is that it is BAD DESIGN. In any (relational) database there should not be a many-to-many (N to M) relationship between tables and/or forms.

Unless the database is a given and cannot be changed I suggest you go back to the design table and put (atleast) a junction table inbetween to make two 1 to N relationships...

 

[captain1974]

The reason you didnt find it is that it is BAD DESIGN. In any (relational) database there should not be a many-to-many (N to M) relationship between tables and/or forms.

Unless the database is a given and cannot be changed I suggest you go back to the design table and put (atleast) a junction table inbetween to make two 1 to N relationships...

Thank you for looking at my database.
As per the Jpeg of my relationship window, the table tblLinkVO is the junction table between the 2 tables. Maybe I didn't explain that properly in my description, that's why I added the database for reference.

 

[namliam]

I dont like diggin' thru DBs, did you try basing your form of a query or doing a subform for the junction table with a subform for the final table?

 

[captain1974]

The junction table is a subform at the moment. I'm having trouble passing the values from the list box to the junction table(subform). I don't know which subforms should go inside which forms. Whenever I have tried basing this type of form on a query I end up creating new (duplicate) records for 'blocks' and 'vineyards' and I'm struggling to find resources to help in this area.

 

[namliam]

Create a form based on Block
Then create a subform inside Block for your junction table
then inside the subform create another subform for VineYard