Automatically Reciprocating Relationships - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Automatically Reciprocating Relationships

Nilsen Septon

18 months ago

I have a 'ContactsT' table (contact can be a Business/Organization or an Individual) in which I am successfully showing a relationship between Bus/Ord to Ind or back the other way. In other words, when on 'XYZ Corp' record, a relationship subform shows 'John Doe' and his title. Then on 'John Doe's' record, a relationship subform shows John Doe's 'Title' and 'Bus/Org' (XYZ Corp).

Here's my question - if I'm on a record (doesn't matter if on Bus/Org or Ind record) and create the relationship, can I have that relationship automatically reciprocated/created on the other record? Using the example above, if I'm on 'XYZ Corp' record and add 'Tom Jones' as the 'Office Manager', can access automatically create on 'Tom Jones' record that he is the 'Office Manager' for 'XYZ Corp'.

Hope that makes sense...and sorry, it looks like I'm being needy today as this is my 2nd post

	Nilsen Septon OP @Reply 18 months ago
(oops....spelling mistake - *Bus/Org not Bus/Ord in first reference of such)

Michael Craytor @Reply

18 months ago

I have done this using a RelationshipT table and a reciprocal relationship key, so that the relationship type (RelationshipTypeID) for, say, parent, is listed as the reciprocal relationship type for child, or the type for Employer is linked to Employee. Then if I add a record for Person A as the parent of Person B, a second record is added for Person B as the child or Person A. I've set up some VBA so that when a new record is inserted into RelationshipT it inserts the reciprocal record, too, so that it is all done in one step.

	Nilsen Septon OP @Reply 18 months ago
That's what I was guessing would need to happen is to have some VBA code doing the work....what does that code look like though. That's where my brain is currently cutting out on me.

Michael Craytor @Reply

18 months ago

Here's what I have at the moment, a little complicated, and in need of refinement - it's gone through a few iterations as other parts of the database have changed. But you can see how I use a "Select Case" to get the ID number for the reciprocal relationship, then I check to make sure the relationship doesn't already exist, and then I add the new record. Looks like I used a recordset here to add the new record, but an append query might be simpler and could be set up in the query designer.

Details

Public Sub SaveRelationship(Person1 As Long, Person2 As Long, Relationship As Long)

    Dim ReciprocalRelationship As Long

    ' Get the reciprocal relationship number

    Select Case Relationship

        ' FAMILY

        Case 67                         ' Caregiver

            ReciprocalRelationship = 68 ' dependent

        Case 68

            ReciprocalRelationship = 67

        Case 69                         ' Family Member

            ReciprocalRelationship = 69

        Case 70

            ReciprocalRelationship = 70

            

        'HEALTH CARE

        Case 72                         ' Health Care

            ReciprocalRelationship = 74 ' Patient

        Case 74

            ReciprocalRelationship = 72

        Case 75                         ' Other

            ReciprocalRelationship = 75

            

        'EMPLOYMENT

        Case 76                         ' Employer

            ReciprocalRelationship = 77 ' Employee

        Case 77

            ReciprocalRelationship = 76

        Case 78                         ' Other

            ReciprocalRelationship = 78



        Case Else

            MsgBox "I don't know the reciprocal relationship for that yet - sincerely, computer"

            Exit Sub

    End Select

    

    DoCmd.SetWarnings False

    InsertRelationship Person2, Person1, ReciprocalRelationship

    DoCmd.SetWarnings True

    MsgBox "Like peas and carrots"

End Sub



Public Sub InsertRelationship(Person1 As Long, Person2 As Long, Relationship As Long)

    Dim db As Database

    Dim rs As Recordset

    Dim strCriteria As String

    Dim mySQL As String

    

    Set db = CurrentDb

    

    ' Check for existing relationship

        strCriteria = "PersonID = " & Person1 & " AND OtherPersonID = " & Person2

        mySQL = "SELECT * FROM RelationshipT WHERE " & strCriteria

        Set rs = db.OpenRecordset(mySQL, dbOpenDynaset, dbSeeChanges)

        

        If rs.EOF Then

            ' No existing relationship found, insert new relationship

            rs.AddNew

            rs!PersonID = Person1

            rs!OtherPersonID = Person2

            rs!RelationshipTypeID = Relationship

            rs.Update

        End If

        

    rs.Close

    Set rs = Nothing

    Set db = Nothing

End Sub

	John Davy @Reply 18 months ago
Take a look at Richard's Access Genealogy Seminar. John

	Sami Shamma @Reply 18 months ago
What John Davy said, as well as show screenshots of your tables in design view

	Nilsen Septon OP @Reply 18 months ago
Sorry for my delayed response. I do want to get back to this but for now, have moved on to other things in the db. I think that @Michael Cryator response would work for me. I did look at the Genealogy vids but will look a little closer when I have the chance. I do appreciate all of the input though and hope to get back to this soon. Thanks!!

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen