Many to Many form

[ktj]

Hi All,



I have been looking at this example of a many to many database complete with forms. http://www.databasedev.co.uk/many_to_many_example.html (it allows you to download the sample db)

What I dont understand is how the forms were created. When i try to do something similar i can't get the forms to work in the same way.

For those who dont want to download the file the problem is this. This is a DB which allows details of a music collection to be stored. In one form it allows you to view an artist and in the sub form add a related album. Alternatively you can view a form that shows the album details but in the sub form you can add a related artist.

The problem i have is trying to work out what tables the forms were based on. When I try to do it, it looks the same but I am unable to change any of the sub form data. Which of the 3 tables need to be included 'album', 'artist' and/or the junction table?

[jzwp11]

The junction table relates the artists and the albums, so basically you need all three tables to make the relationships. In terms of forms, in either case, the subform is based on the junction table. If you want to view an artist with all of their associated albums, then the main form should be based on the artist table. Again, the subform would be based on the junction table. If you want to view albums with all associated artists, then the main form should be based on the albums table (subform based on the junction table once again).

[ktj]

Thanks. I understand how to view the associated data using the either album or artist as the main form and the junction table as the subform.

What I dont understand is how you add new associations in the subform.

In the example DB, when viewing the Artist Details as the main form, you are able to add new album associations in the sub form by simply choosing one in the drop down.

How do you set up the subform to allow users to add new associations (e.g. choose a new album for a particular artist).

[RuralGuy]

The "magic" is performed by utilizing the ability of a ComboBox to bind a column of the RowSource of the cbo to a field of the RecordSource of the Form (a query) by using the ControlSource of the cbo. No additional coding is needed.

[ktj]

I understand the mechanics of what you are saying its just I'm getting confused on what specifically the sub form should be based on (just the junction table or a query which is a mix of both the junction and one of the others) and also which fields and tables im pointing to when i create my combo boxes.

I know where to change the column/row source etc I just don't know what fields I should be connecting to.

[RuralGuy]

The SubForm RecordSource is what controls what you can display on the SubForm. That is simply your decision.