Results 1 to 3 of 3
  1. #1
    OTOTO is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Jul 2014
    Location
    Massachusetts
    Posts
    37

    Form to update tables = Brick wall! How to apply updates and changes to tables/foreign keys etc.

    I've used the following query builder to build the following form. Getting the records to update has been a brick wall. The fields that are located in the table:Record Class Codes update just fine, they are standalone bits of information. It's when the form is pulling the information from another table that I am having difficulty with the update. I would need the table:Record Class Codes to update the references (foreign Keys) for any change that took place. I don't see that Access has this dummy-proof capability. am I wrong? How should I approach this pretty obvious issue?



    Click image for larger version. 

Name:	7-15-2014 10-45-05 AM.jpg 
Views:	16 
Size:	101.5 KB 
ID:	17261
    Click image for larger version. 

Name:	7-15-2014 8-22-47 AM.jpg 
Views:	15 
Size:	205.1 KB 
ID:	17262

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Suggest visiting your query. Make a copy of your query for testing purposes. Remove two tables, Record Class Name 1 and 2. Adjust the remaining JOIN to "Include all Records from Table Business Functions and only those....

    Save your query and open your query in data sheet view, see if you can add, update, and delete the desired records.

    Also, you need to consider using a naming convention for your objects within your DB. Advise no spaces and or special characters. Be sure to avoid words reserved by Access, eg Key, Now, Field....

  3. #3
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Just looking at your diagram, I don't think you WANT to have changes to the FK's in [Record Class Codes] reflected in the other tables. The other three are lookup tables (the 'one' side of a one-to-many relationship). Changes made to the 'many' side should never be propagated to the 'one' side - that is almost guaranteed to violate referential integrity, or mess up the data in the 'many' side.

    If you want to make changes to the ID values in the lookup tables, set up relationships between them and [Record Class Codes], and select the "Enforce Referential Integrity" option. with this setup, changes made to the ID's in the lookup tables will automatically be made in the [Record Class Codes] table.

    HTH

    John

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

Similar Threads

  1. Query with Foreign Keys
    By BawdyB in forum Queries
    Replies: 5
    Last Post: 05-15-2013, 06:47 PM
  2. primary keys in four different tables
    By greatlhanderz_15 in forum Access
    Replies: 5
    Last Post: 01-30-2013, 10:04 PM
  3. primary/foreign keys
    By st1300 in forum Database Design
    Replies: 38
    Last Post: 12-04-2011, 01:27 PM
  4. Need help with foreign keys
    By acdougla17 in forum Access
    Replies: 1
    Last Post: 09-26-2011, 03:50 PM
  5. Design dilemma; multiple tables with same foreign keys?
    By squirrly in forum Database Design
    Replies: 9
    Last Post: 08-16-2011, 10:43 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