MS ACCESS many to many relationship and subform
Posted on 2013-06-21
I have a many to many relationship where I need to populate the junction table using a subform. I've seen many examples of this however my situation is a bit different. The table on the one side of my many to many needs to show details based on 3 fields in the other one side table. See attached relationships.
I need the subform to show in datasheet view all of the values on the one side of the many where the 3 fields match on the other one side (which will be selected via combo boxes on the main form).
The subform also needs to allow me to enter in data for the datasheet records displayed.
I cannot get this to work.
1) The recordsource for my subform is a query based on the junction table and the one side table (right side of the MANY : JUNCTIO : MANY) relationship. The query, however, has to include a right outer join in order to display ALL records that match on the 3 fields selected). I then have the criteria for the 3 fields set to Forms!MainForm!FieldName so it only displays the values that match.
2) The link master and child fields are the PK from the one (left) side and the matching PK from the Junction table.
Nothing is displaying for me when I make the selections from the combo box.
I have been struggling with this for about 3 weeks and am beside myself wondering how this is done or if there is a sample available I can review.