many to many relationship and subform

[Lifeseeker]

Hello,

So I have this many-to-many relational database(two master tables A and B that feed into a junction table in the middle) but exploring the possibility of having a subform on a mainform.(has to be one-to-many)?


I am thinking that I will build the main main on the junction table have a subform built at the bottom that shows all the records that relate to record from master table A. The only issue I think I may have is the one-to-many relationship being the requirement for the subform.

In the junction table, the records may look like this:

87345 BBA8745
87345 BBA2353
53453 BBA2324
53453 BBA5365
34534 BBA5365


on the subform at the bottom, can I have:
87345 BBA8745
BBA2353
53453 BBA2324
BBA5365
34534 BBA5365

 

[CraigDolphin]

Ordinarily you would you the main form bound to one of the 'master' tables, and the subform bound to the junction table. You use the pk field of the master table as the master/child key field that joins the from and subform. You can hide the key field in the subform, if you like, and just show the key to the other master table. You can optionally use a combo box that stores the key value while displaying a more human readable field from the other master table.

 

[Lifeseeker]

Thank you Craig.

It worked for the most part. The part it didn't work was when I tried to add fields from the master table onto the form.

As you browse through the record on the form, the first two fields from the master table are showing properly, but as soon as you add some other fields from the master table, I get #N/A showing up or when you try to make edits on the field, it says at the bottom " you cannot make edits to unbound control"

All my controls are based on the query though.....So the main form is based off the query that pulls records from the master table and the subform is based on the query that pulls records from the junction table.

Any advice?

 

[CraigDolphin]

The recordsource property of the main form should be bound to the master table (or a query of the same), the recordsource property of the subform should be bound to the junction table (a a query of the same). You can then see which fields are availble in each of the forms by clicking on the field list button in the access design view menu.

Make sure the control source property of each control matches one of those fields. All available fields will also be shown in the dropdown list for the control source property.

If you can;t get it working, post an example of your database/forms and I'll take a look at it.