Search code examples
sqlms-access-2010jet

How do I display all records in a subform EXCEPT the one being displayed in the main form in Access 2010 without VBA?

I'm working on a database in Access 2010 which stores addresses. I've carefully designed the tables so that I can store a person and an address separately and then relate them through two other tables (relationship between the people is necessary to store separately from their address as well).

I have the following tables (for this question, focus is on PERSON and RELATIONSHIP):

    PERSON            RELATIONSHIP           ADDR_REL             ADDR
    ------------      ------------           -----------          -----------
(PK)PERSON_ID     (FK)PERSON_ID          (FK)RELATIONSHIP_ID  (PK)ADDR_ID
    FIRST          (K)RELATIONSHIP_ID    (FK)ADDR_ID              ADDR_LINE1
    LAST              RELATIONSHIP_TYPE  (PK)ADDR_REL_ID          ADDR_LINE1
                  (PK)REL_PK                                      ...

Without using VBA (if possible), I want to be able to display a given PERSON on a Form, and have a Subform which displays the other PERSONs in the matching RELATIONSHIP, but NOT display the original PERSON (whichever record is on display in the parent Form) in the subform result set.

FYI: I am able to build a solution to this in VBA myself, but it just seems like their aught to be a way to pull this off without it.

I've tried hidden fields, linked subforms, and custom Queries, but I always end up with all people in the relationship showing in the subform (or no results at all, or parameter prompts). Is there really no way to beat this without writing code?

Solution

  • set the recordsource of the subform to include all the rows in the relationship except the record displayed on the main form. I'm not really sure what the logic defining "the matching relationship" looks like, but I've taken a guess below: (this is all one query but I've broken it up a bit with comments for understanding purposes - remove the comments to use it)

    'get all persons in a given relationship
SELECT p.Person_ID, First, Last 
FROM Person p INNER JOIN Relationship r ON p.Person_ID = r.Person_ID 

'define given relationship
WHERE Relationship_ID = (
  SELECT Relationship_ID FROM Relationship 
  WHERE Person_ID = Forms(nameOfYourMainForm)!nameOfTextboxInMainFormContainingPersonID)

'and the person isnt the person displayed on the main form:
AND p.Person_ID <> Forms(nameOfYourMainForm)!nameOfTextboxInMainFormContainingPersonID;