Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
How to correctly handle Many to Many relationship in Access?
Main form frmTitles (tblTitles) with subform subfrmSongs (tblSongs). The key fields are TitleID (tblSongs) and SongID (tblSongs). I now am having to deal with a many to many relationship between a song and composers. I have a tblComposers (ComposerID, ComposerFirstName, ComposerLastName). I also have set up a join table tblMMSongComposers with two key fields SongID and ComposerID.
How do I setup entering the data on subfrmSongs to include the composer(s) for each individual song? I am guessing it would be an unbound control that would take the current SongID pass it through tblMMSongComposers and tie it with the appropriate ComposerID(s). 2018-10-04-MusicManagerRelationships.pdf
How do I setup entering the data on subfrmSongs to include the composer(s) for each individual song? I am guessing it would be an unbound control that would take the current SongID pass it through tblMMSongComposers and tie it with the appropriate ComposerID(s). 2018-10-04-MusicManagerRelationships.pdf
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Access doesn't have any way to model many to many directly with forms.
You'll need a second subform tied to the first (click on a song, 2nd subform displays composers). For the second subform, you'll use a combo control to store a value in the linking table, and the rowsource for the combo would be the composers table.
To sync the two subforms, place a hidden control on the main form. For the second subform, set the master link on the subform control to the hidden control on the parent (a master link can be a field or a control - child links though must always be a field).
Then in the first subform's OnCurrent, place the key the second subform needs to filter on in the hidden control on the parent.
When that occurs, the 2nd subform will see that, refresh, and display the existing composers.
Jim.
You'll need a second subform tied to the first (click on a song, 2nd subform displays composers). For the second subform, you'll use a combo control to store a value in the linking table, and the rowsource for the combo would be the composers table.
To sync the two subforms, place a hidden control on the main form. For the second subform, set the master link on the subform control to the hidden control on the parent (a master link can be a field or a control - child links though must always be a field).
Then in the first subform's OnCurrent, place the key the second subform needs to filter on in the hidden control on the parent.
When that occurs, the 2nd subform will see that, refresh, and display the existing composers.
Jim.
Yes, in a nutshell that is what I want to do I think. Originally my subfrmSongs was a continuous form but I suspect that this will not be possible with what you suggest. First step how do I add the second subform subfrmComposers and then tie it to the subfrmSongs? I am guessing that subfrmComposers will need to be a continuous form so that all of the composers can be displayed and with that in mind how can a combo box be used to carry out what we want it to? Finally how do I sync the two subforms?
Hi,
I would do what Jim said above only if you want to display the subforms side by side.
The more easier way is to show a continous form (songs) in the detail section of a form which has a header and footer area. Then add a composer subform (continous form also) into (usually) the footer area of the main form. This subform would contain the m:n table showing the selected composers as a combobox like Jim described above.
Yes, you'll always get an error of Access which says "noooo...that's not possible to add a subform to a continous form so I switch back the main form to single mode!". That's one of the oldest strange error message of Access as this is completely nonsense. Simply switch the main form back to continous mode and you have a perfectly working continous form which shows a continous subform with all the selected composers from the m:n table as values in the combobox. Both forms are coupled with the usual subform link fields and you are through with it.
Create a second form where you have the composers in the main area and the songs in the subform if you want it the other way round also.
By the way, you can synchronize also any kind of form with any arrangement using events. I've explained how to do that in this article:
How to synchronize forms using own events
Cheers,
Christian
I would do what Jim said above only if you want to display the subforms side by side.
The more easier way is to show a continous form (songs) in the detail section of a form which has a header and footer area. Then add a composer subform (continous form also) into (usually) the footer area of the main form. This subform would contain the m:n table showing the selected composers as a combobox like Jim described above.
Yes, you'll always get an error of Access which says "noooo...that's not possible to add a subform to a continous form so I switch back the main form to single mode!". That's one of the oldest strange error message of Access as this is completely nonsense. Simply switch the main form back to continous mode and you have a perfectly working continous form which shows a continous subform with all the selected composers from the m:n table as values in the combobox. Both forms are coupled with the usual subform link fields and you are through with it.
Create a second form where you have the composers in the main area and the songs in the subform if you want it the other way round also.
By the way, you can synchronize also any kind of form with any arrangement using events. I've explained how to do that in this article:
How to synchronize forms using own events
Cheers,
Christian
You suggestion seems more in line with what I want to do, however I can't seem to be able to locate a combo box on the new subform, Access keeps putting it on the first subform. How do I get around this?
Hi,
don't know how you tried this? After adding a subform container control you need of course a form inside of the subform control and this is a normal continous form containing a combobox and all the other fields you want to have.
The simplest way is to create a continous form with the m:n fields and the ID of the song as hidden control and the ID of the composer as combobox showing whatever columns you want to display. After this is working (which should show all current entries of your m:n table) simply drag and drop this form from the navigation window into the main form's footer, get the above described error message of Access, laugh a while at it and set the main form back to continous form. If you already have set relationships between the tables in the way you have shown in the PDF then Access should already have set the link fields in the subform container - that's all, the form should work now.
Cheers,
Christian
PS: I've added a little demo database where it's easier to see how this can be done.
MToN.zip
don't know how you tried this? After adding a subform container control you need of course a form inside of the subform control and this is a normal continous form containing a combobox and all the other fields you want to have.
The simplest way is to create a continous form with the m:n fields and the ID of the song as hidden control and the ID of the composer as combobox showing whatever columns you want to display. After this is working (which should show all current entries of your m:n table) simply drag and drop this form from the navigation window into the main form's footer, get the above described error message of Access, laugh a while at it and set the main form back to continous form. If you already have set relationships between the tables in the way you have shown in the PDF then Access should already have set the link fields in the subform container - that's all, the form should work now.
Cheers,
Christian
PS: I've added a little demo database where it's easier to see how this can be done.
MToN.zip
BitSqueezer, that get me closer. Remember that I already have a main form, frmTitles, with the subform subfrmSongs. Now I am putting the frmMMSongComposer into the footer of subfrmSongs. Got the error message, laughed at it, then switch subfrmSongs back to continuous.
When I select a song I am getting a dialog box asking for a missing parameter tblMMSongComposer.SongComp
When I select a song I am getting a dialog box asking for a missing parameter tblMMSongComposer.SongComp
Since you've moved along a bit, I'm not really going to jump back in, but to clarify what I said earlier:
1. The subforms can be single or continuous.
2. Both would have been on the main form.
3. You could if you wanted to nest the 2nd subform within the other.
I just find it easier to put both on the main form.
Jim.
1. The subforms can be single or continuous.
2. Both would have been on the main form.
3. You could if you wanted to nest the 2nd subform within the other.
I just find it easier to put both on the main form.
Jim.
Hi,
of course you can use the double-continous form which I have shown in my example as a subform in another continous form if the subform is also moved into the header or footer of the form.
"Missing parameter" is more often a sign that the queries you used are too complicate or you used to many queries which are based on other queries - or the simplest reason, you used a query which wants to have a parameter which you didn't provide. But that has nothing to do with your initial question, how to handle an m:n table with forms.
We can discuss that but in that case you would need to upload a demo database with some demo records and an explanation how you got the error. As it seems that you also used a multi-valued field in the titles table it could also be the reason for the problem (you should always avoid that construct as it can always be made with normal tables also).
Cheers,
Christian
of course you can use the double-continous form which I have shown in my example as a subform in another continous form if the subform is also moved into the header or footer of the form.
"Missing parameter" is more often a sign that the queries you used are too complicate or you used to many queries which are based on other queries - or the simplest reason, you used a query which wants to have a parameter which you didn't provide. But that has nothing to do with your initial question, how to handle an m:n table with forms.
We can discuss that but in that case you would need to upload a demo database with some demo records and an explanation how you got the error. As it seems that you also used a multi-valued field in the titles table it could also be the reason for the problem (you should always avoid that construct as it can always be made with normal tables also).
Cheers,
Christian
Bitsqueezer, got it there was an additional ID field in the join table. Now it is working as it should! I guess all I have to do is sync the forms. Do I sync all three forms frmTitles, subfrmSongs, qryfrmSongs. I would guess I need to read article Synchronizing form using own events? Am I correct in that?
Hi,
no, synchronizing as described in my article is only needed if you want to have a clean and fast way to synchronize any form with any other form, independent of the subform layout and even independent if you have complete separated forms.
If the forms have a clean relationship between main and subform all you need to do is to set the link id in the subform container control between the main and the subform, Access does the rest for you automatically. Look into the demo database. The subform automatically refreshes it's contents if you select different songs in the main form. That's done by Access, no code needed.
The same works also over more than one nested subform.
Cheers,
Christian
no, synchronizing as described in my article is only needed if you want to have a clean and fast way to synchronize any form with any other form, independent of the subform layout and even independent if you have complete separated forms.
If the forms have a clean relationship between main and subform all you need to do is to set the link id in the subform container control between the main and the subform, Access does the rest for you automatically. Look into the demo database. The subform automatically refreshes it's contents if you select different songs in the main form. That's done by Access, no code needed.
The same works also over more than one nested subform.
Cheers,
Christian
Premium Content
You need an Expert Office subscription to comment.Start Free Trial