none
Many to Many Relationship in Forms

    Question

  • I'm dealing with a many to many relationship, trying to figure out the best way to display my forms.

    table Meeting Info is attached to junction table FundsMeetings which is attached to table Fund Name.

    I'd like individual records from the Meeting Info table to be displayed in the main form, and a list of all associated records in table Fund Name to show up in a subform.  Any suggestions for how I can do this?

    Thursday, March 15, 2012 8:10 PM

Answers

  • Here is an example of the end result of making the relationships in Tables and creating lookup there. then creating a Form with the fields from table where the lookups exist.

    There is a combo box with the record source selected from available fields in the Table called cboManufacturerLookup.

    Then there are fields below with corresponding fields with the code to return values from the lookup field in the table including some concatenated and email and web hyperlinks. 1st picture in design 2nd UI.

     

    The third picture is what the cbo can look like when choosing fields to display.

    A command button is what you would want to use to open a Form for Contacts rather than a hyperlink.

    Just to point out when setting up for the Form keep in mind there is a limit of the number of fields that can be populated by CBO. 9 is the limit but you can get around that to a degree by Concatenating multiple fields to a single field.


    Chris Ward


    • Edited by KCDW Friday, March 16, 2012 6:40 PM add text
    • Marked as answer by Bruce Song Wednesday, April 04, 2012 6:38 AM
    Friday, March 16, 2012 6:38 PM
  • I'm assuming Ken's example will work fine if I add more fields to the query behind the combo box, and allow them to be displayed.  But is there any way to make one of those fields a hyperlink?

    No reason whatsoever why not.  An alternative to referencing the Column property of a combo box in the way Chris describes is, as you say, to base the subform on a query which joins the table modelling the relationship type to the referenced table.  So if we take my little demo file as an example, say we added a Level column to the activities table so that we could have two rows for swimming say, with levels Beginners and Advanced, the subform would be based on a query which joins the ParentActivities and Activities table on ActivityID like so:

    SELECT ParentActivities.*, Activities.Level
    FROM ParentActivities INNER JOIN Activities
    ON ParentActivities.ActivityID = Activities.ActivityID
    ORDER BY Activity;

    In the subform we could then have a text box bound to the Level column.  As many more columns as necessary can be added in the same way.  You would not want the user to be able to edit the level in the subform, however, so by setting its Locked property to True (Yes) and its Enabled property to False (No) it becomes read only; the user can't even move focus to it.

    There can be another situation, however, where you would not simply want to pull in a value from the referenced table as we have with Level above, but would want to be able to edit it.  The classic example of this is a UnitPrice value in an OrderDetails or InvoiceDetails table for instance.  In this case a Products table  would have UnitPrice column with each product's current unit price.  The OrderDetails table would have a ProductID column referencing the key of Products, but if we simply pulled in the UnitPrice value as we've done with Level, as the UnitPrice values in products change over time the prices shown in each order or invoice would also change.  This is wrong of course as they should reflect the price when the invoice or order was created, not the current price.  So the OrderDetails table would also have a UnitPrice column, and the current value from products would be pushed into it in code in the AfterUpdate event procedure of the ProductID control, a combo box.  The sample Northwind database which comes with Access has a rather long winded example of how this can be done, but essentially it's a question of looking up the value and assigning it to the bound UnitPrice control in the subform like this:

    Dim strCriteria As String

    strCriteria = "ProductID = " & Nz(Me.ProductID,0)

    Me.UnitPrice = DLookup("UnitPrice", "Products", strCriteria)

    The Nz function is used here to return a zero if the ProductID control is Null, e.g. if a user makes a mistake and deletes its value before selecting another one.  As there will be no product with a ProductID of zero the DLookup function will return a Null and assign this to the UnitPrice in OrderDetails.

    In the language of the database relational model this is a question of 'functional dependency'.  In this case UnitPrice in Products is functionally dependant on the key of Products and UnitPrice in OrderDetails is functionally dependant on the key of OrderDetails, which is how it should be for the tables to be correctly 'normalized'.

    I don't follow your point about a hyperlink, however, but I see no reason why not.


    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Friday, March 16, 2012 7:14 PM Small clarification
    • Marked as answer by Bruce Song Wednesday, April 04, 2012 6:38 AM
    Friday, March 16, 2012 7:13 PM

All replies

  • Go to your relationships window and right click the Join line to edit. look at the choices there to choose the type you need.

    Chris Ward

    Thursday, March 15, 2012 8:20 PM
  • Asssuming the following:

    Meeting Info table  FundsMeeting table  Fund Name table
    MeetingInforID -----FKMeetingInfoID
                                   FKFundNameID -------FundNameID

    Use the Meeting Info table (or a query based on it) as the record source for the main form.

    Use a query that joins the junction table, FundsMeetings, to the Fund Name table on FKFundNameID = FundNameID as the record source for the subform.

    In the subform control set:
    Link Master Fields to MeetingInfoID
    Link Child Fields to FKMeetingInfoID


    http://www.saberman.com

    Thursday, March 15, 2012 8:27 PM
  • For a simple example see ParentActivities.zip in my public databases folder at:

    https://skydrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    You might have to copy the text of the link into your browser's address bar (not the link location).  For some reason it doesn't always seem to work as a hyperlink.

    Note how the subform uses a combo box bound to the ActivityID column but set up to hide the bound column and show the text Activity column.  The Activities table is included in the subform's RecordSource query purely for the purpose of ordering the rows in the subform alphabetically.

    Ken Sheridan, Stafford, England

    Thursday, March 15, 2012 10:35 PM
  • Thank you for confirming for me the best way to be approaching a Many to Many relationship.

    Looks like I'd been including items from the Fund Name table, themselves in the subform, instead of using them as a display in a combo box for my junction table ID field.  

    Maybe you can answer some lingering questions for me.  I see how this approach works for Ken's ParentActivities example, but I'm hoping to display more than one field from my FundName table.  Ideally, I'd like to display all fields in the FundName table, including an ID field to unbound table "Contacts" that is a hyperlink.  I want to be able to click the hyperlink and open a form for "Contacts."  

    I'm assuming Ken's example will work fine if I add more fields to the query behind the combo box, and allow them to be displayed.  But is there any way to make one of those fields a hyperlink?

    Friday, March 16, 2012 4:43 PM
  • Here is an example of the end result of making the relationships in Tables and creating lookup there. then creating a Form with the fields from table where the lookups exist.

    There is a combo box with the record source selected from available fields in the Table called cboManufacturerLookup.

    Then there are fields below with corresponding fields with the code to return values from the lookup field in the table including some concatenated and email and web hyperlinks. 1st picture in design 2nd UI.

     

    The third picture is what the cbo can look like when choosing fields to display.

    A command button is what you would want to use to open a Form for Contacts rather than a hyperlink.

    Just to point out when setting up for the Form keep in mind there is a limit of the number of fields that can be populated by CBO. 9 is the limit but you can get around that to a degree by Concatenating multiple fields to a single field.


    Chris Ward


    • Edited by KCDW Friday, March 16, 2012 6:40 PM add text
    • Marked as answer by Bruce Song Wednesday, April 04, 2012 6:38 AM
    Friday, March 16, 2012 6:38 PM
  • I'm assuming Ken's example will work fine if I add more fields to the query behind the combo box, and allow them to be displayed.  But is there any way to make one of those fields a hyperlink?

    No reason whatsoever why not.  An alternative to referencing the Column property of a combo box in the way Chris describes is, as you say, to base the subform on a query which joins the table modelling the relationship type to the referenced table.  So if we take my little demo file as an example, say we added a Level column to the activities table so that we could have two rows for swimming say, with levels Beginners and Advanced, the subform would be based on a query which joins the ParentActivities and Activities table on ActivityID like so:

    SELECT ParentActivities.*, Activities.Level
    FROM ParentActivities INNER JOIN Activities
    ON ParentActivities.ActivityID = Activities.ActivityID
    ORDER BY Activity;

    In the subform we could then have a text box bound to the Level column.  As many more columns as necessary can be added in the same way.  You would not want the user to be able to edit the level in the subform, however, so by setting its Locked property to True (Yes) and its Enabled property to False (No) it becomes read only; the user can't even move focus to it.

    There can be another situation, however, where you would not simply want to pull in a value from the referenced table as we have with Level above, but would want to be able to edit it.  The classic example of this is a UnitPrice value in an OrderDetails or InvoiceDetails table for instance.  In this case a Products table  would have UnitPrice column with each product's current unit price.  The OrderDetails table would have a ProductID column referencing the key of Products, but if we simply pulled in the UnitPrice value as we've done with Level, as the UnitPrice values in products change over time the prices shown in each order or invoice would also change.  This is wrong of course as they should reflect the price when the invoice or order was created, not the current price.  So the OrderDetails table would also have a UnitPrice column, and the current value from products would be pushed into it in code in the AfterUpdate event procedure of the ProductID control, a combo box.  The sample Northwind database which comes with Access has a rather long winded example of how this can be done, but essentially it's a question of looking up the value and assigning it to the bound UnitPrice control in the subform like this:

    Dim strCriteria As String

    strCriteria = "ProductID = " & Nz(Me.ProductID,0)

    Me.UnitPrice = DLookup("UnitPrice", "Products", strCriteria)

    The Nz function is used here to return a zero if the ProductID control is Null, e.g. if a user makes a mistake and deletes its value before selecting another one.  As there will be no product with a ProductID of zero the DLookup function will return a Null and assign this to the UnitPrice in OrderDetails.

    In the language of the database relational model this is a question of 'functional dependency'.  In this case UnitPrice in Products is functionally dependant on the key of Products and UnitPrice in OrderDetails is functionally dependant on the key of OrderDetails, which is how it should be for the tables to be correctly 'normalized'.

    I don't follow your point about a hyperlink, however, but I see no reason why not.


    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Friday, March 16, 2012 7:14 PM Small clarification
    • Marked as answer by Bruce Song Wednesday, April 04, 2012 6:38 AM
    Friday, March 16, 2012 7:13 PM