As been pointed out, in a classic in traditional sense, there’s really not such a thing as a many to many relationship between two tables, however by cobbling together related tables one after another, you do at the end of the day due in effect get the same result of a many to many relationship, but technically that’s not what occurring between two tables.
Let’s assume that each weekend we have to take in a bunch of donations from people. This means we have one main table with information about the date and time etc of this event. Our next table would have people and their donation amount. The next table after that would be to take a donation amount, and split up their funds into different accounts . This is a classic accounting distribution problem that just about every accounting package from Quickbooks to whenever has to implement. As it turns out, this classic distribution problem can be solved with very little code when using access.
The trick to modeling these types of forms is to use several sub forms placed into one main form.
The following form shows this:
If you look at the top there’s a main record with information about this batch run and date and time. Now, on the left side is many people and a donation amount. And on the right side is that donation for the one person split out to many different accounts. So I’m displaying many people, and I’m also to display the many accounts that a donation may be split out to .
Keep in mind that access does not let you place a continuous subform inside of a continuous subform. However, you can certainly place two continuous of forms beside each other as the above screen shot shows to model the same affect that you desire.
There’s also surprisingly as mentioned very little code to manage this whole thing.
For the left side continuous form, because the link master and child settings are set to the main form record, then NO code is required for this form to be populated with data. However, for the right side continues form to follow and display the correct Accounting Data for each person in the leftr side continuous form, access will not automatically do this for you . However, a simple bit of code in the on-current event of the left side form will forced access to wake up and note the right side continues form has to be RE populated to display the donation accounts for that one particular person .
The one line of code placed in the left side on-current event will fix this:
The link master/child settings on the right side continuous form I use:
linkChildFields main_id (whatever is the name of the field in
this sub-form that is used to relate back to the parent table)
LinkMasterFields [MasterFormLeftSide].[form].[ID] ("masterFormleft" is the name of
the subform contorl you used to hold the continuous form on the left side ).
So, you’ll need a tiny little bit of code, but not a whole heck of a lot, and you now have a screen that displays many information related to many information.
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada