Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2020
    Posts
    19

    Update another recordset with a matching field if a note is added to another record matching field

    Hi there,

    I have an activity table where a user can add information pertaining to client, example add an appointment, a note regarding a call, etc.



    In some cases, a client has a spouse and they each have there own record in the parent table. The unique identifier is a client id. I have added another field to this table to identify members who are from the same family. Is there a way, when a user add a record to the child table, an activity, that the this record is also added to the other family remembers with the same family id? The goal here is to allow the user to enter one activity for the family without having to go into each family member and manually add the same activity.

    Or alternative, is there a way I can create a query that will show me all activities belonging to a family id and then have them appear on a tabbed form for each client. In other words, if a note is added to one client, the querry would pull all family members through the family id with activities that have been entered so they will all show up regardless of the client I select. I am using a tabbed form to display each clients related information. I need to be able to link this information to my client tabbed form by the unique client id which is the primary key.

    I would appreciate any assistance.

    Thank you kindly,
    Tess

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Don't duplicate the records. If you have tblActivity, the activity gets recorded once per family. If you can have many activities related to many families then you may need a junction table where the FamilyIDfk and ActivityIDfk fields are located as foreign keys (the primary key fields from tblFamily and tblActivity). However, I think you'd need a family id field in tblClient with a value in each record whether or not the "family" is one person or more. I suspect you are listing each member in the same client table because you are treating each family member as a separate client. The fact that not all clients might reside at the same address, or have the same contact numbers, ages, etc. justifies a record for each member, even if that family member is a count of one.

    I might have misunderstood some of what you have, as I'm not sure what you mean by child and parent. In Access terms, it refers to something like a many table vs a one table. You might mean actual parents and their children, so I'm not sure if you actually have a table for clients as well as one for those who are parents. Further complicating that understanding is the fact that you could have 2 members of the same family, neither of which are parents. Maybe post a pic of your table relationships if you have built them.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    In a form/subform layout change the master/child linking between the forms to use the FamillyID from the main form and ActivityFamilyID (might be a new field that you'd have to create in tblActivity). For the query scenario you can simply create one where you join the to tables and add the ClientID,FamilyID (from tblClient), ActivityName,ActivityDate (from tblActivity) WHERE FamilyID =Forms!frmClient!FamilyID, create a datasheet form based on it and add it to a new page on your tab control linked by ClientID.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  4. #4
    Join Date
    Jun 2020
    Posts
    19
    Hi Micron, You are correct in your analysis. There are many family members and each is a seperate client. By child parent i did mean the one to many relationships between the client and the activities.

    Thank you for your response.

    Tess

  5. #5
    Join Date
    Jun 2020
    Posts
    19
    Hi Gicu,

    Thank you, that makes sense! How can I get the ActivityFamilyID to populate from the activity table every time a new activity is added please?

    Regards,
    Tess

  6. #6
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    In a form/subform scenario linked by FamilyId(Master)/ActivityFamilyId(Child) the later will be populated automatically when adding new records (with the FamilyID value from the main form).

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

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

Similar Threads

  1. Replies: 6
    Last Post: 01-03-2021, 05:06 AM
  2. Replies: 43
    Last Post: 01-15-2019, 10:49 PM
  3. Replies: 8
    Last Post: 02-27-2018, 09:52 AM
  4. Replies: 1
    Last Post: 10-24-2011, 08:01 AM
  5. Replies: 1
    Last Post: 08-11-2011, 11:33 AM

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