Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    kelann is offline Learning ... thank you!!
    Windows XP Access 2010 32bit
    Join Date
    Aug 2012
    Posts
    132

    Edit field value linked to another table's field

    Hi, all!

    I have a table (OrganizationsT) with a primary key field (OrgName).
    I have another table (ContactsT) with a field (ContactOrgName) that is pulled from the OrgName field of OrganizationsT.

    These fields are linked on a one-to-many basis -- each organization can have more than one contact, but a contact can only be linked to a single organization.

    Once a contact has been created, and an organization selected for that contact (ContactOrgName), if I try to edit the organization's name (OrgName) in the OrganizationsT, it won't let me, because it is linked.

    I was hoping editing the OrgName would, instead, not only accept changes, but "flow through" to any contacts it is linked to.

    Is there any way to achieve this?

    With thanks!

    --ak

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Would you per chance be using a Lookup Field? http://access.mvps.org/access/lookupfields.htm

  3. #3
    kelann is offline Learning ... thank you!!
    Windows XP Access 2010 32bit
    Join Date
    Aug 2012
    Posts
    132
    Thanks for your response, Rural Guy! I am, indeed, using a lookup field.

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    I would suggest removing the Lookup at the table level and you will see why you are having trouble. http://www.btabdevelopment.com/ts/removelookups

  5. #5
    kelann is offline Learning ... thank you!!
    Windows XP Access 2010 32bit
    Join Date
    Aug 2012
    Posts
    132
    Thanks, Rural Guy!

    I tried this, but I'm now getting a message that says "the record cannot be deleted or or changed because 'ContactsT' includes related records."

    Any thoughts on how this field value can be edited and flow through the related records?

    Thanks so much!

    --ak

  6. #6
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    The value you are trying to change in not in the table in which you are trying to change it. Changt it in the Lookup table.

  7. #7
    kelann is offline Learning ... thank you!!
    Windows XP Access 2010 32bit
    Join Date
    Aug 2012
    Posts
    132
    Thanks, Rural Guy! I know (at least I think) that I can change it -- but I want the USER to be able to edit the organization's name (if necessary) via a form -- they won't have "automatic" table access.

    It seems rather normal that a value should, at some time, change in a field that is linked, and I feel there must be something to plug in somewhere so the user can do it with a typical Edit-type form (this one is OrgEditF), but I can't imagine how!

    Any thoughts??

    Thanks!

    --ak

  8. #8
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    You should be able to create a form that has the Lookup table (a select query of same) as the RecordSource of the form and allows for the change of all of the fields but the PrimaryKey.

  9. #9
    kelann is offline Learning ... thank you!!
    Windows XP Access 2010 32bit
    Join Date
    Aug 2012
    Posts
    132
    Rats! This field IS the primary key because I didn't want any duplicates!!!!! I thought it would still be changeable, as long as it is unique.

    Am I lost, or is this salvageable?

    Thanks!

  10. #10
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Hmm...I'll go look at your structure again. What is the name of that table?

  11. #11
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Oops...mixing up two threads. Forget my last post. Let me get another cup of coffee. 8^)

  12. #12
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    You could be in a pickle here. *Any* field in a table can be held to unique values and need not be the PrimaryKey field. I don't think turning on Cascading Updates can help here.

  13. #13
    kelann is offline Learning ... thank you!!
    Windows XP Access 2010 32bit
    Join Date
    Aug 2012
    Posts
    132
    If I switch the primary key to something else -- the ID field, perhaps -- would that likely crab any SQL statements? In other words, as I am not exactly an expert, I'm not sure if any of my code depended on that field as the primary key. Would such a thing matter -- that is, if I changed the primary key to a different field?

  14. #14
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    You will find Access will not let you do that since the PK value is scattered throughout your system.

  15. #15
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Had you started with the ID field as the PK then you could easily change this name field and it would magically change wherever it was used because only the PK is stored in the other tables.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Change a field to Primary key in linked table
    By MrFormula in forum Programming
    Replies: 4
    Last Post: 11-25-2011, 01:42 PM
  2. Replies: 3
    Last Post: 03-19-2011, 11:04 AM
  3. Replies: 1
    Last Post: 06-01-2009, 01:09 PM
  4. Field Types for a Linked Table
    By Jeff_J in forum Access
    Replies: 9
    Last Post: 05-05-2009, 07:12 AM
  5. Linked Table Field Validation
    By yuriyl in forum Access
    Replies: 4
    Last Post: 04-20-2009, 01:31 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