Results 1 to 9 of 9
  1. #1
    msmithtlh is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    151

    Stuck creating an add/update form - underlying query not updatable. Need suggestion.

    I started out with a multi-table query, which I have simplified down to 2 tables. The form team members works great to select the team leader and display the team, but I can't add team members or edit team members or delete team members.

    I realize I need a totally different approach, but don't know where to start, since these two tables are necessary. If you could tell me what I need to do, I would greatly appreciate it.

    Relevant tables are
    Congregations
    Individuals
    Teams
    Ind_roles

    Teams are made up of individuals in a congregation. They have different roles, statuses, begin and end dates.



    Thank you for your help! Database attached.test.zip

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    The query is not linking on key fields. Individuals table has FullName designated as PK yet you link to its Individual_ID_PK field. Either save FullName value as FK (bad idea) or designate Individual_ID_PK as primary key. Now the query is editable and can enter data on form but should you? What is purpose of this form - to enter new Individual records or new Ind_Roles records?

    Really looks like FullName field should be renamed to ReferredBy.

    Normally a form does data entry to only one table. Really should not be necessary to include multiple tables in form RecordSource. Possibly should use a form/subform arrangement.

    Why does Relationships builder include Congregations table twice? There is a relationship somewhere in table or form design that is forcing this. Remove the second instance and save Relationships, reopen and the table copy is back.

    Should the TeamLeader combobox list be dependent on the Congregation combobox?

    Do you want to require selection in both comboboxes for filter? I don't use dynamic parameterized queries. I prefer VBA to build filter criteria and apply to form or report. Review http://allenbrowne.com/ser-62.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    msmithtlh is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    151
    Thanks so much for your help.

    It is a lot better, thanks to your suggestions. Still am having trouble adding individuals to the team.

    I changed the primary key on Individuals (the numeric id field was what I meant to make the primary key). This does make the query updatable.

    The form is good now for editing data, but I still cannot add records - although you can add records in the query.

    Should the TeamLeader combobox list be dependent on the Congregation combobox? YES. Teams are a part of a congregation.

    I tried using the subform and that works for editing current members of the team. However, the subform doesn't work for adding individuals to the team. I tried adding a 2nd subform for new team members, but that doesn't work either.

    Hoping you can help again. Updated database attached.
    Attached Files Attached Files

  4. #4
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi

    Which Form is it that you are having the trouble with ?
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  5. #5
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi

    The relationships make no sense to me at all.

    Can you give me an example of the following

    1. Name of a Congregation
    2. Name of the Team this Congregation belongs to


    I would have thought that:-

    A Congregation is made up of Individuals who are Members of a Team and Each Team Member has a specific Role.
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  6. #6
    msmithtlh is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    151
    Congregations are made up of Individuals, each congregation has multiple teams of individuals. The form I'm having trouble with is "Team Members." The subform allows me to edit team members, but not add team members.
    Relevant tables are
    Congregations
    Individuals
    Teams
    Ind_roles

    Example: congregation = Trinity UMC, team 1 with leader Robert Gibbs

    Teams are made up of individuals in a congregation. They have different roles, statuses, begin and end dates.



  7. #7
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Where your relationships are failing is because you are trying to use the Congregation as a Name of a Team

    I used to go to Church a few days a week and not once did anyone refer to a Team within a Congregation.
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  8. #8
    msmithtlh is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    151
    You are missing the point. This is a database for a multi-congregation ministry. Each congregation can have multiple teams.

  9. #9
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi

    I beg to differ, see the attached which allows you to :-

    1. Select a Congregation
    2. Add Multiple addresses for the selected Congregation
    3. Add 1 or More Teams
    4. Select Individuals who belong to the Team selected
    5. Add a Role for the Individual

    Please study the Relationship diagram as there are other areas that need work.
    Attached Files Attached Files
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

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

Similar Threads

  1. Replies: 3
    Last Post: 05-08-2017, 04:53 AM
  2. Update Table With Form - Stuck :(
    By mromberg in forum Forms
    Replies: 4
    Last Post: 02-08-2017, 02:49 PM
  3. Replies: 1
    Last Post: 10-27-2016, 12:14 PM
  4. Creating an updatable query recordset using Access 2010
    By Bill McCoy in forum Programming
    Replies: 1
    Last Post: 04-17-2012, 09:36 AM
  5. Replies: 0
    Last Post: 03-13-2012, 12:57 PM

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