Results 1 to 5 of 5
  1. #1
    Nina Hound is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Jul 2016
    Posts
    6

    Multiple instances of record in main form when linked to subform

    I have two tables linked by a linking table. Table A contains a list of artists. Table B contains a list of works. The linking table allows a many to many relationship between the two. In other words, one artist can have many works, and one work can have more than one artist associated with it.



    I want my main form to show the artist and the subform to show all the works associated with that artist. I have that set up fine and it works. The problem is that If an artist has 4 works, I have to step through the same artist 4 times (highlighting a different work on the subform each time) to get to the next artist. I have tried various combinations of joins to no avail.

    What I want is for the artist to show up on the main form, all the associated works to show up on the subform (which it does now), but to have only one instance of the artist. When I advance to the next record in the main form, I should get a different artist, no matter how many works are associated with him/her. How do I accomplish that? Thank you for any help you may be able to provide.

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Main form is a single record form.
    the subForm is multi record.
    link on key.

  3. #3
    Nina Hound is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Jul 2016
    Posts
    6
    Not sure what your are suggesting. Yes, main form is single record; sub form is multi-record (dataset view); they are linked through the linking table.

  4. #4
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    What is the record source for the subform?

    Try creating a query containing the link table and the works table, joined on Work_ID. Make that query the record source for the subform, then link the main form to the subform using the Artist_ID.

  5. #5
    Nina Hound is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Jul 2016
    Posts
    6
    Yes, that was my mistake. I had the link table in the query for the artists table (main form). When I did as you suggested and put the link table in the query for the sub form, that did the trick. Thank you so much for your insight.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 10
    Last Post: 12-17-2014, 03:31 PM
  2. Replies: 3
    Last Post: 11-04-2012, 09:25 AM
  3. Replies: 6
    Last Post: 08-22-2012, 03:24 AM
  4. Replies: 3
    Last Post: 06-22-2011, 08:51 AM
  5. Replies: 3
    Last Post: 06-14-2010, 06:48 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums